Groups > comp.databases.ms-sqlserver > #1559 > unrolled thread

select most recent event

Started by	migurus <migurus@yahoo.com>
First post	2013-08-29 19:04 -0700
Last post	2013-08-30 08:37 -0400
Articles	2 — 2 participants

Back to article view | Back to comp.databases.ms-sqlserver

  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

#1559 — select most recent event

From	migurus <migurus@yahoo.com>
Date	2013-08-29 19:04 -0700
Subject	select most recent event
Message-ID	<7d2c843d-1926-47cf-9ae2-3c9b30a8f1f4@googlegroups.com>

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.

[toc] | [next] | [standalone]

#1560

From	"Bob Barrows" <reb01501@NOSPAMyahoo.com>
Date	2013-08-30 08:37 -0400
Message-ID	<kvq3m0$bha$1@dont-email.me>
In reply to	#1559

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;

[toc] | [prev] | [standalone]

csiph-web

select most recent event

Contents

#1559 — select most recent event

#1560