Get last date/time - 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)

Get last date/time

Author

Topic

kbeana
Starting Member

1 Post

Posted - 2013-05-28 : 13:01:14

I am trying to pull a fairly complex report, and on that report they want to view the date the patient last had a blood pressure, and what the blood pressure result was. Each portion of the blood pressure (Systolic and Diastolic) are stored separately. And for each one, there's a separate date field and a time field. I have yet to figure out how the time is stored but I do have a way to convert it if needed. I'm not even sure how I stumbled across this anymore.

RIGHT('0'+CAST(MRVL99.T_VITAL/3600 AS VARCHAR),2)+':' + RIGHT('0'+CAST(((MRVL99.T_VITAL/60)%60) AS VARCHAR),2) AS VITAL_TIME

So my query works fairly well, except if a patient has more than one blood pressure recorded on the same date. I can NOT figure out how to make it return the last record only. This would work if the date and time were stored in one date/time field but I just can't get it to work the way it is. Any help would be greatly appreciated.

SELECT DISTINCT    MRPA99.ACCT_ID, 
			rtrim(MRPA99.PAT_LAST_NAME) + ', '+ rtrim(MRPA99.PAT_FIRST_NAME) AS PT_NAME,
			rtrim(LAST_BPS.VITAL_RESULT) + '/' + rtrim(LAST_BPD.VITAL_RESULT) AS LAST_BP,
			CONVERT(VARCHAR(10),LAST_BPS.D_VITAL,101) AS LAST_BP_DATE
FROM         MRCI99 INNER JOIN
                      MRPA99 ON MRCI99.PTID = MRPA99.PTID 
				LEFT OUTER JOIN
					(SELECT DISTINCT MRPA99.PTID,
							MRVL99.VITAL_RESULT AS VITAL_RESULT,
							MRVL99.D_VITAL AS D_VITAL,
							MRVL99.T_VITAL AS T_VITAL
					FROM MRVL99 INNER JOIN
							MRPA99 ON MRVL99.PTID = MRPA99.PTID 
							INNER JOIN
								(SELECT DISTINCT    MRPA99.PTID AS PTID, 
										MAX(MRVL99.D_VITAL) AS LAST_D_DIASTOLIC,
										MAX(MRVL99.T_VITAL) AS LAST_T_DIASTOLIC
								FROM         MRPA99 INNER JOIN
												MRVL99 ON MRPA99.PTID = MRVL99.PTID
								WHERE MRVL99.VITAL_NAME = 'Diastolic'
								GROUP BY MRPA99.PTID)
								AS LAST_DIASTOLIC ON MRPA99.PTID = LAST_DIASTOLIC.PTID 
									AND LAST_DIASTOLIC.LAST_D_DIASTOLIC = MRVL99.D_VITAL
					WHERE MRVL99.VITAL_NAME = 'Diastolic')
					AS LAST_BPD ON MRPA99.PTID = LAST_BPD.PTID
				
				LEFT OUTER JOIN
					(SELECT DISTINCT MRPA99.PTID,
							MRVL99.VITAL_RESULT AS VITAL_RESULT,
							MRVL99.D_VITAL AS D_VITAL,
							MRVL99.T_VITAL AS T_VITAL
					FROM MRVL99 INNER JOIN
							MRPA99 ON MRVL99.PTID = MRPA99.PTID 
							INNER JOIN
								(SELECT DISTINCT    MRPA99.PTID AS PTID, 
										MRVL99.D_VITAL,
										MAX(MRVL99.D_VITAL) AS LAST_D_SYSTOLIC,
										MAX(MRVL99.T_VITAL) AS LAST_T_SYSTOLIC
								FROM         MRPA99 INNER JOIN
												MRVL99 ON MRPA99.PTID = MRVL99.PTID
								WHERE MRVL99.VITAL_NAME = 'Systolic'
								GROUP BY MRPA99.PTID, MRVL99.D_VITAL)
								AS LAST_SYSTOLIC ON MRPA99.PTID = LAST_SYSTOLIC.PTID 
									AND LAST_SYSTOLIC.LAST_D_SYSTOLIC = MRVL99.D_VITAL
					WHERE MRVL99.VITAL_NAME = 'Systolic')
					AS LAST_BPS ON MRPA99.PTID = LAST_BPS.PTID
WHERE MRCI99.CLIN_LIST_CODE1 >= '250.00'   
			AND MRCI99.CLIN_LIST_CODE1 <= '250.9' 
			AND MRPA99.PAT_STATUS <> 'D'
			AND MRPA99.PAT_LAST_NAME NOT LIKE '%ZTEST%'
			AND MRPA99.ACCT_ID = '14875'
			AND LAST_BPD.T_VITAL = LAST_BPS.T_VITAL
ORDER BY PT_NAME

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-05-28 : 13:40:03

--> "how to make it return the last record only"

Having no idea of how structures are, and, the data placed in the tables, so can only suggest an idea. You might need to use the Row_Number function in the Left Join data sets as appropriate. Please refer to BOL for more details of Row_Number function. In your case I believe PatiendID would be the candidate column for "Partition By" and Date/time field(s) would be for "Order By" in descending order. And then restriction the dataset only to the most recent one e.g. where row_number =1, take a look at the following example.

declare @tab table (ID int, TestDate date)
insert into @tab values (1,'2013-05-01'),(1,'2013-05-02'),(1,'2013-05-03'),(2,'2013-05-02'),(2,'2013-05-01'),(2,'2013-05-03')
SELECT *
FROM (select ID,TestDate,ROW_NUMBER() Over (partition by ID Order by TestDate Desc) as MostRecentTest
from @tab
)A
WHERE MostRecentTest=1

Cheers
MIK

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-28 : 14:23:45

Instead of posting queries, it would be much helpful for someone trying to help if you post some sample data from your tables and then explain what you want as output from it.

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

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-05-28 : 14:28:30

Have you tried using a cte to calculate the time per patient with a row_number partitioned by patient then us the max row?

djj

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources