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

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

select most recent event

Show all headers | View raw

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.

Back to comp.databases.ms-sqlserver | Previous | Next — Next 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