Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

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

Re: select most recent event

Show all headers | 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