Top value
-
I am trying to query for the top event_date by Consumer. How can I get one record per Consumer? I get the top record but if there were multiple entries on that day I get them all. How can I check for the Max clrec_sysid along with the Max event_date?
It seems it would be easy but I am having no luck. Or should I use Top N?
Here is my code and an example of the result:
SELECT DISTINCT l.clrec_sysid, l.cons_entity_id,MAX(l.event_date)
FROM tblclinical_record_master l
INNER JOIN tblcase c ON l.case_sysid = c.case_sysid
WHERE ((c.case_end_date) Is Null)
GROUP BY l.cons_entity_id, clrec_sysid
sys_id cons_id event_date
22003242 0000011999-06-30 00:00:00
22003243 0000011999-06-30 00:00:00
22036491 0000011999-06-30 00:00:00
22036496 0000042000-12-11 15:15:00
22057781 0000052003-03-11 10:30:00
22057782 0000052003-03-11 10:30:00
22057783 0000052003-04-03 14:36:00
22057784 0000052003-03-11 10:30:00
22057785 0000052003-03-11 10:30:00
22030932 0000072001-12-01 00:00:00
22030933 0000072002-01-01 00:00:00
22030934 000007 2002-02-01 00:00:00
TIA
-
tblclinical_record_master.clrec_sysid is the problem. It is unique, yet you are using it in the GROUP BY. Think in terms of using your current SELECT as a sub-select and get the TOP 1 clrec_sysid for each cons_entity_id.
I wasn't born stupid - I had to study.
-
That sounds like what I am looking for.
Can you give me an example?
I'm a rook and I am having trouble coding this.
-
SELECT Distinct m.clrec_sysid, m.cons_entity_id, m.event_date
FROM tblclinical_reord_master m
WHERE m.clrec_sysid =
(SELECT Top 1 m2.clrec_sysid
FROM tblclinical_record_master m2
JOIN tblcase c on m2.case_sysid = c.case_sysid
WHERE m2.cons_entity_id = m.cons_entity_id
AND c.case_end_date Is Null
ORDER BY m2.event_date desc, m2.clrec_sysid desc )
-
SELECT MAX(l.clrec_sysid), l.cons_entity_id, lm.Max_event_date
FROM tblclinical_record_master l
INNER JOIN tblcase c ON l.case_sysid = c.case_sysid
INNER JOIN (SELECT l1.cons_entity_id,MAX(l1.event_date) as Max_event_date
FROM tblclinical_record_master l1
GROUP BY l.cons_entity_id) lm on l.cons_entity_id = lm.cons_entity_id
and l.event_date = Max_event_date
WHERE ((c.case_end_date) Is Null)
GROUP BY l.cons_entity_id, lm.Max_event_date
This will return list of all customers with max event_date and max sysid for that date for avery customer.
_____________
Code for TallyGenerator -
Thank you all very much for your insight!
-
Ron_k, I tried this using a #TempTable (and dropping tblCase since I did not know the data and it looked to be a simple join with a where clause) and found your code only returned one record.
Sergiy, your code looks to work. (I have my settings such that Integers have comma's, hence the conversion to varchar - we have dollars with no cents to portray...) Nice job.
(I must admit, I thought about this a bit last night and did not come up with such an elegant solution. 
CREATE TABLE #tblclinical_record_master( clrec_sysid int,
cons_entity_id varchar(6),
event_date datetime)
INSERT INTO #tblclinical_record_master VALUES( 22003242, '000001', '1999-06-30 00:00:00')
INSERT INTO #tblclinical_record_master VALUES( 22003243, '000001', '1999-06-30 00:00:00')
INSERT INTO #tblclinical_record_master VALUES( 22036491, '000001', '1999-06-30 00:00:00')
INSERT INTO #tblclinical_record_master VALUES( 22036496, '000004', '2000-12-11 15:15:00')
INSERT INTO #tblclinical_record_master VALUES( 22057781, '000005', '2003-03-11 10:30:00')
INSERT INTO #tblclinical_record_master VALUES( 22057782, '000005', '2003-03-11 10:30:00')
INSERT INTO #tblclinical_record_master VALUES( 22057783, '000005', '2003-04-03 14:36:00')
INSERT INTO #tblclinical_record_master VALUES( 22057784, '000005', '2003-03-11 10:30:00')
INSERT INTO #tblclinical_record_master VALUES( 22057785, '000005', '2003-03-11 10:30:00')
INSERT INTO #tblclinical_record_master VALUES( 22030932, '000007', '2001-12-01 00:00:00')
INSERT INTO #tblclinical_record_master VALUES( 22030933, '000007', '2002-01-01 00:00:00')
INSERT INTO #tblclinical_record_master VALUES( 22030934, '000007', '2002-02-01 00:00:00')
/*
SELECT CONVERT( varchar(10), l.clrec_sysid) AS clrec_sysid,
l.cons_entity_id,
CONVERT( varchar, MAX( l.event_date), 101) AS event_date
FROM #tblclinical_record_master l
GROUP BY l.cons_entity_id, clrec_sysid
*/
SELECT CONVERT( varchar(10), MAX( l.clrec_sysid)) AS clrec_sysid,
CONVERT( varchar(10), l.cons_entity_id) AS cons_entity_id,
CONVERT( varchar, lm.Max_event_date, 101) AS Max_event_date
FROM #tblclinical_record_master l
INNER JOIN (SELECT l1.cons_entity_id, MAX( l1.event_date) AS Max_event_date
FROM #tblclinical_record_master l1
GROUP BY l1.cons_entity_id) lm ON l.cons_entity_id = lm.cons_entity_id
AND l.event_date = Max_event_date
GROUP BY l.cons_entity_id, lm.Max_event_date
DROP TABLE #tblclinical_record_master
I wasn't born stupid - I had to study.
-
I get the following error on Sergiy's code:
Server: Msg 107, Level 16, State 2, Line 11
The column prefix 'l' does not match with a table name or alias name used in the query.
Ron's does work (my desired result) but it takes a while to run (16 Min's on 32k records).
I can live with it.
-
I was able to use your code Farrell.
I added the check for no Case end date.
It runs very fast too. Thanks
SELECT MAX(l.clrec_sysid) AS clrec_sysid,
l.cons_entity_id AS cons_entity_id,
lm.Max_event_date AS Max_event_date
FROM tblclinical_record_master l
INNER Join tblCase C on l.case_sysid = c.case_sysid
INNER JOIN (SELECT l1.cons_entity_id, MAX( l1.event_date) AS Max_event_date
FROM tblclinical_record_master l1
GROUP BY l1.cons_entity_id) lm ON l.cons_entity_id = lm.cons_entity_id
AND l.event_date = Max_event_date
WHERE c.case_end_date IS null
GROUP BY l.cons_entity_id, lm.Max_event_date
-
I forgot to change alias in GROUP BY for nested query.
This must work:
SELECT MAX(l.clrec_sysid), l.cons_entity_id, lm.Max_event_date
FROM tblclinical_record_master l
INNER JOIN tblcase c ON l.case_sysid = c.case_sysid
INNER JOIN (SELECT l1.cons_entity_id,MAX(l1.event_date) as Max_event_date
FROM tblclinical_record_master l1
GROUP BY l1.cons_entity_id) lm on l.cons_entity_id = lm.cons_entity_id
and l.event_date = Max_event_date
WHERE ((c.case_end_date) Is Null)
GROUP BY l.cons_entity_id, lm.Max_event_date
_____________
Code for TallyGenerator -
Ok, I appologize Sergiy, I see now Farrell used your code.
Yes, it works for me now.
Thanks a bunch!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply