Groups > comp.databases.ms-sqlserver > #1560

Re: select most recent event

Path	csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!feeder.erje.net!eu.feeder.erje.net!eternal-september.org!feeder.eternal-september.org!news.eternal-september.org!.POSTED!not-for-mail
From	"Bob Barrows" <reb01501@NOSPAMyahoo.com>
Newsgroups	comp.databases.ms-sqlserver
Subject	Re: select most recent event
Date	Fri, 30 Aug 2013 08:37:34 -0400
Organization	A noiseless patient Spider
Lines	73
Message-ID	<kvq3m0$bha$1@dont-email.me> (permalink)
References	<7d2c843d-1926-47cf-9ae2-3c9b30a8f1f4@googlegroups.com>
Injection-Date	Fri, 30 Aug 2013 12:37:20 +0000 (UTC)
Injection-Info	mx05.eternal-september.org; posting-host="096837095dd208cce5bf4df79f1836c2"; logging-data="11818"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1/dRCjXi9FU+3j275PmWfH6IlKo+VlrMjc="
X-MimeOLE	Produced By Microsoft MimeOLE V6.00.2900.6157
X-RFC2646	Format=Flowed; Original
X-Newsreader	Microsoft Outlook Express 6.00.2900.5931
Cancel-Lock	sha1:RnpzGFP3ygt05hMhMzNnaJ6vb9M=
X-Priority	3
X-MSMail-Priority	Normal
Xref	csiph.com comp.databases.ms-sqlserver:1560

Show key headers only | View raw

migurus wrote:
> I am dealing with schema where customers have one-to-many events, so
> each customer has at least one event record. I need to show customer
> details and his most recent event.
>
> This is how I do it:
>
> declare @CUSTOMERS table (
> CUST_ID int NOT NULL PRIMARY KEY
> ,CUST_NAME varchar(64) NOT NULL
> ,CUST_PHONE varchar(24) NULL
> );
> declare @EVENTS table (
> SEQ_ID int NOT NULL IDENTITY PRIMARY KEY
> ,CUST_ID int NOT NULL
> ,EVENT_DATE date NOT NULL
> );
> insert into @CUSTOMERS(CUST_ID,CUST_NAME,CUST_PHONE) values
> (1000, 'DOLE,MARY', '212-409-4406'),
> (2000, 'DALES,KYE', '212-664-7055'),
> (3300, 'DOBBS,JOHN', '818-752-6028'),
> (4000, 'DUNES,RAY', '818-648-3890');
>
> insert into @EVENTS(CUST_ID, EVENT_DATE) values
> (1000, '2002-01-01'),
> (2000, '2002-01-01'),
> (3300, '2002-01-01'),
> (4000, '2002-01-01'),
> (3300, '2003-06-01'),
> (3300, '2008-10-15'),
> (1000, '2011-12-01');
>
> select distinct
> E.CUST_ID
> ,MAX(E.EVENT_DATE) over (partition by E.CUST_ID) [EVENT_DATE]
> ,C.CUST_NAME
> ,C.CUST_PHONE
> from   @EVENTS E
>  JOIN @CUSTOMERS C
>    ON E.CUST_ID = C.CUST_ID;
>
> Output:
> CUST_ID EVENT_DATE CUST_NAME CUST_PHONE
> 1000 2011-12-01 DOLE,MARY 212-409-4406
> 2000 2002-01-01 DALES,KYE 212-664-7055
> 3300 2008-10-15 DOBBS,JOHN 818-752-6028
> 4000 2002-01-01 DUNES,RAY 818-648-3890
>
> +++
>
> Does anyone have any better idea? any critique is welcome.

This query returns the most recent event per customer:

SELECT CUST_ID,MAX(EVENT_DATE) AS LatestEvent
FROM @EVENTS


Now you simply need to join these results to your @CUSTOMERS table. Here's 
one way to do that:

;WITH e AS (
SELECT CUST_ID,MAX(EVENT_DATE) AS LatestEvent
FROM @EVENTS)
SELECT E.CUST_ID, LatestEvent,
,C.CUST_NAME
,C.CUST_PHONE
from  E
  JOIN @CUSTOMERS C
    ON E.CUST_ID = C.CUST_ID;

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Find similar

Thread

select most recent event migurus <migurus@yahoo.com> - 2013-08-29 19:04 -0700
  Re: select most recent event "Bob Barrows" <reb01501@NOSPAMyahoo.com> - 2013-08-30 08:37 -0400

csiph-web