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.
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -