Distinct values based on MIN() and MAX() values

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)

Distinct values based on MIN() and MAX() values

Author

Topic

stamford
Starting Member

47 Posts

Posted - 2014-01-29 : 08:36:10

I have a table called EVENTS and I need to write 4 scripts to generate 4 separate tables of distinct EVENT_ID values. Below is the first script and the other 3 will be of a similar logic. Can anyone help with this first script please so I can then hopefully use it as a template for the other 3 scripts. I am writing these scripts in SQL2005 which must be backwards compatible with SQL2000. I have removed any duplicates so there shouldn't be a need to involve the rank of the EVENT_ID in the logic. Thank you.

For each CARE_ID select the value of EVENT_ID which has an EVENT_TYPE of CP and has a MAX(EVENT_DATE) which is <= the MIN(EVENT_DATE) where the EVENT_TYPE is in ('B','CH','S', 'T')


CARE_ID	EVENT_ID	EVENT_DATE	EVENT_TYPE
3	194	        01/10/2012	S
3	228	        07/07/2010	S
3	104	        12/05/2010	CH
3	16	        12/07/2010	B
3	17	        13/07/2010	B
3	43	        15/01/2010	P
3	189	        15/04/2010	S
39	45	        09/10/2009	T
39	4	        21/07/2009	P
39	6	        21/07/2009	CH
78	28	        08/07/2009	S
78	706	        08/12/2010	CP
78	707	        09/12/2010	CP
78	9	        28/07/2009	T
78	11	        28/07/2009	CH
95	21	        31/07/2009	CH
95	21	        31/07/2009	T
107	1474	        21/09/2012	S
107	93	        23/02/2010	CP
107	59	        29/10/2012	P
107	58	        29/12/2009	P
151	186	        19/03/2010	S
151	49	        21/03/2010	T
152	69	        26/08/2009	CH
206	85	        21/08/2009	CP
206	84	        28/07/2009	CP
217	158	        18/02/2010	S
217	102	        30/03/2010	CH
218	159	        12/03/2010	S
227	1378	        01/04/2011	CP
355	19	        13/07/2010	B
355	20	        13/07/2010	B
355	239	        13/07/2010	S
355	56	        16/07/2010	T
355	111	        16/07/2010	CH
364	1136	        18/02/2011	CP
364	569	        19/02/2011	S
364	774	        23/08/2012	CH
364	1122	        26/01/2011	CP
367	247	        01/07/2010	S
367	151	        21/06/2010	CP
369	108	        26/07/2010	P
369	152	        27/07/2010	CP
369	109	        28/07/2010	P
369	117	        28/07/2010	CH
369	248	        28/07/2010	S
380	277	        08/07/2011	T
396	1573	        06/06/2011	CP
481	63	        07/09/2010	T
481	116	        07/09/2010	P
481	194	        07/09/2010	CP
481	289	        07/09/2010	S
502	200	        13/08/2010	CP
530	220	        14/06/2010	CP
535	222	        05/07/2010	CP
535	303	        13/07/2010	S
535	223	        19/07/2010	CP
535	224	        26/07/2010	CP
536	135	        10/09/2010	CH
536	225	        23/08/2010	CP
568	155	        06/10/2010	P
568	315	        15/10/2010	S
631	148	        02/02/2010	CH
631	74	        15/01/2010	T
631	256	        15/12/2009	CP
631	345	        15/12/2009	S
631	147	        25/12/2009	CH
632	259	        18/09/2010	CP
653	189	        29/10/2010	P
653	360	        30/09/2010	S
655	1570	        06/06/2011	CP
680	569	        08/12/2010	CP
680	1191	        24/11/2011	S
680	530	        25/01/2011	S
680	151	        30/09/2010	P
680	281	        30/09/2010	CP
680	480	        30/11/2010	CP
689	306	        02/11/2010	CP
689	158	        06/10/2010	P
689	372	        06/10/2010	S
689	2720	        06/11/2012	CP
689	2736	        11/11/2012	CP
689	2752	        13/11/2012	CP
689	2765	        15/11/2012	CP
689	2125	        22/09/2011	CP
689	2654	        24/09/2012	CP
689	1944	        26/08/2011	CP
689	307	        26/10/2010	CP
689	1947	        27/08/2011	CP
729	299	        15/09/2010	CP
811	413	        27/10/2010	S
834	622	        01/01/2012	CH
834	1233	        06/01/2012	S
834	624	        15/01/2012	CH
834	625	        23/01/2012	CH
834	627	        23/01/2012	CH
838	629	        02/01/2012	CH
838	630	        20/01/2012	CH
838	632	        27/01/2012	CH
846	416	        05/10/2010	S
849	195	        03/11/2010	P
849	336	        21/02/2011	CP
923	441	        26/07/2010	S
963	371	        29/10/2010	CP
981	624	        23/03/2011	S
984	384	        13/11/2010	CP
984	392	        18/11/2010	CP

sqlsaga
Yak Posting Veteran

93 Posts

Posted - 2014-01-29 : 10:17:15

If you can give the same data in the format of a DML statement, your query can be solved much faster.

Visit www.sqlsaga.com for more t-sql snippets and BI related how to's.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-30 : 07:23:03

[code]
SELECT p.CARE_ID,p.EVENT_ID
FROM Table p
INNER JOIN
(
SELECT t.CARE_ID,
MAX(CASE WHEN EVENT_DATE < MinEvntDate THEN EVENT_DATE END) AS MaxEventDate
FROM Table t
INNER JOIN (SELECT CARE_ID,MIN(CASE WHEN EVENT_TYPE IN ('B','CH','S', 'T') THEN EVENT_DATE END) AS MinEvntDate
FROM Table
GROUP BY CARE_ID
)t1
ON t1.CARE_ID = t.CARE_ID
AND t.EVENT_DATE <= t1.MinEvntDate
AND t.EVENT_TYPE ='CP'
GROUP BY t.CARE_ID
)r
ON r.CARE_ID = p.CARE_ID
AND r.MaxEVentDate = p.EVENT_DATE
AND p.EVENT_TYPE='CP'
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-01-30 : 16:37:41

Visakh, I don't think your solution returns the correct result.
Why are you returning CareID 535 at all?

Try to simplify

-- SwePeso
SELECT	CareID,
	CAST(SUBSTRING(Data, 9, 12) AS SMALLINT) AS EventID
FROM	(
		SELECT		CareID,
				MAX(CASE WHEN EventType = 'CP' THEN CONVERT(CHAR(8), EventDate, 112) + STR(EventID, 12) ELSE NULL END) AS Data
		FROM		@Sample
		GROUP BY	CareID
		HAVING		MAX(CASE WHEN EventType = 'CP' THEN EventDate ELSE NULL END) <= MIN(CASE WHEN EventType IN ('B', 'CH', 'S', 'T') THEN EventDate ELSE NULL END)
	) AS d;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-01-30 : 16:41:30

For testing purposes

DECLARE	@Sample TABLE
	(
		CareID SMALLINT NOT NULL,
		EventID SMALLINT NOT NULL,
		EventDate DATETIME NOT NULL,
		EventType VARCHAR(2) NOT NULL
	);

INSERT	@Sample
	(
		CareID,
		EventID,
		EventDate,
		EventType
	)
VALUES	(3, 194, '20121001', 'S'),
	(3, 228, '20100707', 'S'),
	(3, 104, '20100512', 'CH'),
	(3, 16, '20100712', 'B'),
	(3, 17, '20100713', 'B'),
	(3, 43, '20100115', 'P'),
	(3, 189, '20100415', 'S'),
	(39, 45, '20091009', 'T'),
	(39, 4, '20090721', 'P'),
	(39, 6, '20090721', 'CH'),
	(78, 28, '20090708', 'S'),
	(78, 706, '20101208', 'CP'),
	(78, 707, '20101209', 'CP'),
	(78, 9, '20090728', 'T'),
	(78, 11, '20090728', 'CH'),
	(95, 21, '20090731', 'CH'),
	(95, 21, '20090731', 'T'),
	(107, 1474, '20120921', 'S'),
	(107, 93, '20100223', 'CP'),
	(107, 59, '20121029', 'P'),
	(107, 58, '20091229', 'P'),
	(151, 186, '20100319', 'S'),
	(151, 49, '20100321', 'T'),
	(152, 69, '20090826', 'CH'),
	(206, 85, '20090821', 'CP'),
	(206, 84, '20090728', 'CP'),
	(217, 158, '20100218', 'S'),
	(217, 102, '20100330', 'CH'),
	(218, 159, '20100312', 'S'),
	(227, 1378, '20110401', 'CP'),
	(355, 19, '20100713', 'B'),
	(355, 20, '20100713', 'B'),
	(355, 239, '20100713', 'S'),
	(355, 56, '20100716', 'T'),
	(355, 111, '20100716', 'CH'),
	(364, 1136, '20110218', 'CP'),
	(364, 569, '20110219', 'S'),
	(364, 774, '20120823', 'CH'),
	(364, 1122, '20110126', 'CP'),
	(367, 247, '20100701', 'S'),
	(367, 151, '20100621', 'CP'),
	(369, 108, '20100726', 'P'),
	(369, 152, '20100727', 'CP'),
	(369, 109, '20100728', 'P'),
	(369, 117, '20100728', 'CH'),
	(369, 248, '20100728', 'S'),
	(380, 277, '20110708', 'T'),
	(396, 1573, '20110606', 'CP'),
	(481, 63, '20100907', 'T'),
	(481, 116, '20100907', 'P'),
	(481, 194, '20100907', 'CP'),
	(481, 289, '20100907', 'S'),
	(502, 200, '20100813', 'CP'),
	(530, 220, '20100614', 'CP'),
	(535, 222, '20100705', 'CP'),
	(535, 303, '20100713', 'S'),
	(535, 223, '20100719', 'CP'),
	(535, 224, '20100726', 'CP'),
	(536, 135, '20100910', 'CH'),
	(536, 225, '20100823', 'CP'),
	(568, 155, '20101006', 'P'),
	(568, 315, '20101015', 'S'),
	(631, 148, '20100202', 'CH'),
	(631, 74, '20100115', 'T'),
	(631, 256, '20091215', 'CP'),
	(631, 345, '20091215', 'S'),
	(631, 147, '20091225', 'CH'),
	(632, 259, '20100918', 'CP'),
	(653, 189, '20101029', 'P'),
	(653, 360, '20100930', 'S'),
	(655, 1570, '20110606', 'CP'),
	(680, 569, '20101208', 'P'),
	(680, 1191, '20111124', 'S'),
	(680, 530, '20110125', 'S'),
	(680, 151, '20100930', 'P'),
	(680, 281, '20100930', 'CP'),
	(680, 480, '20101130', 'CP'),
	(689, 306, '20101102', 'CP'),
	(689, 158, '20101006', 'P'),
	(689, 372, '20101006', 'S'),
	(689, 2720, '20121106', 'CP'),
	(689, 2736, '20121111', 'CP'),
	(689, 2752, '20121113', 'CP'),
	(689, 2765, '20121115', 'CP'),
	(689, 2125, '20110922', 'CP'),
	(689, 2654, '20120924', 'CP'),
	(689, 1944, '20110826', 'CP'),
	(689, 307, '20101026', 'CP'),
	(689, 1947, '20110827', 'CP'),
	(729, 299, '20100915', 'CP'),
	(811, 413, '20101027', 'S'),
	(834, 622, '20120101', 'CH'),
	(834, 1233, '20120106', 'S'),
	(834, 624, '20120115', 'CH'),
	(834, 625, '20120123', 'CH'),
	(834, 627, '20120123', 'CH'),
	(838, 629, '20120102', 'CH'),
	(838, 630, '20120120', 'CH'),
	(838, 632, '20120127', 'CH'),
	(846, 416, '20101005', 'S'),
	(849, 195, '20101103', 'P'),
	(849, 336, '20110221', 'CP'),
	(923, 441, '20100726', 'S'),
	(963, 371, '20101029', 'CP'),
	(981, 624, '20110323', 'S'),
	(984, 384, '20101113', 'CP'),
	(984, 392, '20101118', 'CP');

-- SwePeso
SELECT	CareID,
	CAST(SUBSTRING(Data, 9, 12) AS SMALLINT) AS EventID
FROM	(
		SELECT		CareID,
				MAX(CASE WHEN EventType = 'CP' THEN CONVERT(CHAR(8), EventDate, 112) + STR(EventID, 12) ELSE NULL END) AS Data
		FROM		@Sample
		GROUP BY	CareID
		HAVING		MAX(CASE WHEN EventType = 'CP' THEN EventDate ELSE NULL END) <= MIN(CASE WHEN EventType IN ('B', 'CH', 'S', 'T') THEN EventDate ELSE NULL END)
	) AS d;

--Visakh
SELECT p.CAREID,p.EVENTID
FROM @Sample p
INNER JOIN 
(
SELECT t.CAREID,
MAX(CASE WHEN EVENTDATE < MinEvntDate THEN EVENTDATE END) AS MaxEventDate
FROM @Sample t
INNER JOIN (SELECT CAREID,MIN(CASE WHEN EVENTTYPE IN ('B','CH','S', 'T') THEN EVENTDATE END) AS MinEvntDate
            FROM @Sample
            GROUP BY CAREID
            )t1
ON t1.CAREID = t.CAREID
AND t.EVENTDATE <= t1.MinEvntDate
AND t.EVENTTYPE ='CP'
GROUP BY t.CAREID
)r
ON r.CAREID = p.CAREID
AND r.MaxEVentDate = p.EVENTDATE
AND p.EVENTTYPE='CP'

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources