MAX() - Help (AGAIN) - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

MAX() - Help (AGAIN)

Author

Topic

OON
Starting Member

22 Posts

Posted - 2010-09-24 : 13:18:26

Hello again,

i have the following query -

SELECT MAX(MR.date_time) AS MOST_RECENT,MR.notes from (

SELECT nmr2.note_datetime as date_time,hno2.note_summary as notes
FROM KPGA_ODS.CLARITY.NOTES_MC_RFL AS nmr2
INNER JOIN KPGA_ODS.CLARITY.HNO_ENC_INFO as hno2 on hno2.NOTE_ID = nmr2.NOTE_ID and hno2.Note_Type_C= 7
INNER JOIN KPGA_ODS.CLARITY.ZC_NOTE_TYPE as znt2 on znt2.NOTE_TYPE_C = hno2.NOTE_TYPE_C
where record_id= '200979155'--'200797535'

UNION

SELECT rnh1.notif_datetime as date_time, s.smarttext_name as notes
FROM KPGA_ODS.CLARITY.REFERRAL_NOTIF_HIS AS rnh1
inner join clarity.SMARTTEXT s on rnh1.ltr_hx_letter_id = s.SMARTTEXT_ID
WHERE rnh1.Note_Type_C= 7 and line=1 and rnh1.referral_id='200979155'--'200797535'

) AS MR

GROUP BY MR.notes

OUTPUT -

MOST_RECENT NOTES
2010-08-12 16:17:00.000 Auto: 14092-QRM MN TARGET REVIEW DENIAL COMM
2010-08-13 07:16:00.000 Auto: 42577-QRM SF MN TARGET REVIEW DENIAL C
2010-08-13 07:16:00.000 Auto: Notification Recipient List
2010-08-13 22:30:00.000 QRM SF MN TARGET REVIEW DENIAL COMM

What i really want is for this to return only the one "Note" based on the Most_Recent date and in this case it would be

MOST_RECENT NOTES
2010-08-13 22:30:00.000 QRM SF MN TARGET REVIEW DENIAL COMM

i just need to see one line with the most recent date and the corresponding note.

Please help.

Thanks again!!!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-09-24 : 13:35:52

Try making use of a ranking function like ROW_NUMBER():

SELECT
	MOST_RECENT,
	notes
FROM
	(
		SELECT 
			*, 
			ROW_NUMBER() OVER(ORDER BY MOST_RECENT DESC) AS RowNum
		FROM
		(
			SELECT MAX(MR.date_time) AS MOST_RECENT,MR.notes from (

			SELECT nmr2.note_datetime as date_time,hno2.note_summary as notes
			FROM KPGA_ODS.CLARITY.NOTES_MC_RFL AS nmr2 
			INNER JOIN KPGA_ODS.CLARITY.HNO_ENC_INFO as hno2 on hno2.NOTE_ID = nmr2.NOTE_ID and hno2.Note_Type_C= 7
			INNER JOIN KPGA_ODS.CLARITY.ZC_NOTE_TYPE as znt2 on znt2.NOTE_TYPE_C = hno2.NOTE_TYPE_C
			where record_id= '200979155'--'200797535'

			UNION

			SELECT rnh1.notif_datetime as date_time, s.smarttext_name as notes
			FROM KPGA_ODS.CLARITY.REFERRAL_NOTIF_HIS AS rnh1
			inner join clarity.SMARTTEXT s on rnh1.ltr_hx_letter_id = s.SMARTTEXT_ID
			WHERE rnh1.Note_Type_C= 7 and line=1 and rnh1.referral_id='200979155'--'200797535'

			) AS MR

			GROUP BY MR.notes
		) AS A
	) AS B
WHERE
	RowNum = 1

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-09-24 : 13:37:06

Or use a TOP clause with an ORDER BY:


SELECT TOP 1 MOST_RECENT, MR.notes
FROM
	(
		SELECT MAX(MR.date_time) AS MOST_RECENT,MR.notes from (

		SELECT nmr2.note_datetime as date_time,hno2.note_summary as notes
		FROM KPGA_ODS.CLARITY.NOTES_MC_RFL AS nmr2 
		INNER JOIN KPGA_ODS.CLARITY.HNO_ENC_INFO as hno2 on hno2.NOTE_ID = nmr2.NOTE_ID and hno2.Note_Type_C= 7
		INNER JOIN KPGA_ODS.CLARITY.ZC_NOTE_TYPE as znt2 on znt2.NOTE_TYPE_C = hno2.NOTE_TYPE_C
		where record_id= '200979155'--'200797535'

		UNION

		SELECT rnh1.notif_datetime as date_time, s.smarttext_name as notes
		FROM KPGA_ODS.CLARITY.REFERRAL_NOTIF_HIS AS rnh1
		inner join clarity.SMARTTEXT s on rnh1.ltr_hx_letter_id = s.SMARTTEXT_ID
		WHERE rnh1.Note_Type_C= 7 and line=1 and rnh1.referral_id='200979155'--'200797535'

		) AS MR

		GROUP BY MR.notes
	) AS T
ORDER BY MOST_RECENT DESC

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-24 : 13:38:30

Thinsk about what you did

You grouped all the data by a Note...

Then took the MAX Date for that Note

But it sounds like you wan the MAX(Date) and that Notes Value

Is that correct?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

OON
Starting Member

22 Posts

Posted - 2010-09-27 : 10:30:11

@ Brett, you are correct. thats what i wanted. But i was able to get what i want with the TOP function as suggested above. Thanks ALL!

OON
Starting Member

22 Posts

Posted - 2010-09-27 : 12:24:56

HEY ALL, USING THE TOP FUNCTION RETURNS ONLY ONE RECORD BUT IF I HAVE 1000 RECORDS THE TOP FUNCTION ONLY RETURNS ONE RECORD. IF I HAVE 1000 PATIENTS, I WANT TO RETURN THE THE MAX(DATE_TIME) VALUE AND NOTES ASSOCIATED WITH THAT FOR EACH PATIENT. SO I COULD HAVE MULTIPLE LINES BUT EACH LINE SHOULD/WILL BE UNIQUE FOR EACH PATIENT.

PLEASE HELP

THANKS

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-27 : 12:27:55

Use ROW_NUMBER() function for that.

And please don't type in all caps.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

OON
Starting Member

22 Posts

Posted - 2010-09-27 : 12:35:12

Sorry fro typing in caps. Could you please show an example using my query?

thanks

OON
Starting Member

22 Posts

Posted - 2010-09-27 : 12:52:54

This is what i have -

SELECT
*

FROM
(
SELECT
*,
ROW_NUMBER() OVER(ORDER BY MOST_RECENT DESC) AS RowNum
FROM
(
SELECT MAX(MR.MOST_RECENT) AS MOST_RECENT,MR.LETTER from (

SELECT R.REFERRAL_ID, R.PAT_ID, P.PAT_MRN_ID, H.CONTACT_DATE, H.NOTE_SUMMARY AS LETTER, H.NOTE_TYPE_C,
Z.NAME, N.NOTE_DATE, MAX(N.NOTE_DATETIME) AS MOST_RECENT, H.NOTE_ID, H.EDIT_DTTM,N.RECORD_ID
FROM KPGA_ODS.CLARITY.REFERRAL R
INNER JOIN KPGA_ODS.CLARITY.PATIENT P ON R.PAT_ID = P.PAT_ID
INNER JOIN KPGA_ODS.CLARITY.NOTES_MC_RFL N ON R.REFERRAL_ID = N.RECORD_ID
INNER JOIN KPGA_ODS.CLARITY.HNO_ENC_INFO H ON N.NOTE_ID = H.NOTE_ID
INNER JOIN KPGA_ODS.CLARITY.ZC_NOTE_TYPE Z ON H.NOTE_TYPE_C = Z.NOTE_TYPE_C
WHERE EXTERNAL_ID_NUM = '201185093'
--AND H.NOTE_TYPE_C = '7'
--AND H.NOTE_SUMMARY <>'Auto: Notification Recipient List'
GROUP BY R.REFERRAL_ID, R.PAT_ID, P.PAT_MRN_ID, H.CONTACT_DATE,H.NOTE_SUMMARY, H.NOTE_TYPE_C,
Z.NAME, N.NOTE_DATE,H.NOTE_ID, H.EDIT_DTTM, N.RECORD_ID

) AS MR

GROUP BY MR.LETTER
) AS A
) AS B
WHERE
RowNum = 1

However, when WHERE EXTERNAL_ID_NUM = (multiple ID_NUM's) it still only returns one.

each patient gets a letter and i want the most recent letter and the date associated with that letter for each patient. i dont know that the ROW NUMBER() functions can do this or maybe im doing it wrong.

Thanks for you help.

OON
Starting Member

22 Posts

Posted - 2010-09-27 : 13:16:12

Please can anyone help me out with this?

Thanks

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-09-27 : 13:58:08

Hi OON,

I think the main reason people do not reply is that your problem is not clear for them.

Please, provide sample data and desired output.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-27 : 14:04:48

+1 to namman's post.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog