Selecting specific data from a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Selecting specific data from a table

Author

Topic

stamford
Starting Member

47 Posts

Posted - 2014-05-08 : 06:46:13

I need a script that will return CARE_ID, EVENT_DATE, EVENT_ID, EVENT_TYPE from this table.
For each CARE_ID I need the last CP before the first appearance of 'BR', 'CH', 'SU' or 'TE'
The tie breaker for 'CP' is MAX(EVENT_ID) and for 'BR', 'CH', 'SU' or 'TE') it is MIN(EVENT_ID)

For each CARE_ID that isn't included above return the most recent 'CP'
The tie breaker for 'CP' is MAX(EVENT_ID).

So I'm thinking two scripts with a UNION?

Two examples are as below.
* The returned row for CARE_ID 3 has a 'CP' before the first 'BR', 'CH', 'SU' or 'TE'
* The returned row for CARE_ID 78 does not have a 'CP' before the first 'BR', 'CH', 'SU' or 'TE' so the most recent CP has been returned.


CARE_ID	EVENT_DATE	        EVENT_ID   EVENT_TYPE
3	09/04/2010 00:00	117	   CP
78	09/12/2010 00:00	707	   CP

The script has to be fairly old school


CARE_ID	EVENT_DATE              EVENT_ID   EVENT_TYPE
3	09/04/2010 00:00	117	   CP
3	11/04/2010 00:00	104	   CH
3	16/04/2010 00:00	190	   SU
3	12/07/2010 00:00	16	   BR
3	13/07/2010 00:00	17	   BR
3	13/07/2010 00:00	18	   BR
78	27/07/2009 00:00	11	   CH
78	28/07/2009 00:00	9	   TE
78	08/12/2010 00:00	706	   CP
78	09/12/2010 00:00	707	   CP
107	23/02/2010 00:00	93	   CP
107	21/09/2012 00:00	1474	   SU
364	26/01/2011 00:00	1122	   CP
364	18/02/2011 00:00	1136	   CP
364	19/02/2011 00:00	569	   SU
364	23/08/2012 00:00	774	   CH
367	21/06/2010 00:00	151	   CP
367	01/07/2010 00:00	247	   SU
653	30/09/2010 00:00	360	   SU
653	15/10/2010 00:00	295	   CP
653	15/10/2010 00:00	298	   CP
655	06/06/2011 00:00	1570	   CP
689	06/10/2010 00:00	372	   SU
689	26/10/2010 00:00	307	   CP
689	02/11/2010 00:00	306	   CP
689	02/11/2010 00:00	370	   CP
689	26/08/2011 00:00	1944	   CP
689	27/08/2011 00:00	1947	   CP
689	22/09/2011 00:00	2125	   CP
689	24/09/2012 00:00	2654	   CP
689	24/09/2012 00:00	2677	   CP
689	06/11/2012 00:00	2720	   CP
689	11/11/2012 00:00	2736	   CP
689	13/11/2012 00:00	2752	   CP
689	15/11/2012 00:00	2765	   CP
1066	11/11/2010 00:00	472	   SU
1066	15/11/2010 00:00	91	   TE
1066	23/11/2010 00:00	408	   CP
3907	05/01/2014 00:00	3591	   CP
3907	09/01/2014 00:00	945	   CH
3907	13/01/2014 00:00	1821	   SU
3907	14/01/2014 00:00	3592	   CP
3907	14/01/2014 00:00	3593	   CP

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-05-08 : 07:52:40

[code]
;with aCTE
AS
(
select 3 CARE_ID,'09/04/2010 00:00' EVENT_DATE ,117 EVENT_ID,'CP' EVENT_TYPE UNION ALL
select 3,'11/04/2010 00:00',104,'CH' union all
select 3,'16/04/2010 00:00',190,'SU' union all
select 3,'12/07/2010 00:00',16,'BR' union all
select 3,'13/07/2010 00:00',17,'BR' union all
select 3,'13/07/2010 00:00',18,'BR' union all
select 78,'27/07/2009 00:00',11,'CH' union all
select 78,'28/07/2009 00:00',9,'TE' union all
select 78,'08/12/2010 00:00',706,'CP' union all
select 78,'09/12/2010 00:00',707,'CP')

select
CARE_ID
,EVENT_DATE
,EVENT_ID
,EVENT_TYPE
from
(

select
A.*

,Row_Number() OVER(Partition By A.CARE_ID Order by A.EVENT_DATE desc) as rn
from
aCTE A
outer apply
(

select Top 1
EVENT_DATE
from aCTE B
where
EVENT_TYPE in ('BR', 'CH', 'SU' , 'TE' )
AND A.CARE_ID=B.CARE_ID
Order by EVENT_DATE ASC
) C
Where
A.EVENT_DATE<C.EVENT_DATE
AND A.EVENT_TYPE='CP'
)X
Where X.rn=1
[/code]

output
[code]
CARE_ID EVENT_DATE EVENT_ID EVENT_TYPE
3 09/04/2010 00:00 117 CP
78 09/12/2010 00:00 707 CP
[/code]

sabinWeb MCP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources