comparing dates - 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 2012 Forums

Transact-SQL (2012)

comparing dates

Author

Topic

Mike1957
Starting Member

4 Posts

Posted - 2014-04-14 : 11:38:14

I need easy help, I'm sure

I am creating a table from lab services at a hospital and bringing in a date in int format 20140101 and need to determine if this lab service was performed during an inpatient visit.

I am creating table and brining in LabServiceDate. I need to then take this date and look in table inpatient at at admitdate and discharge date to determine if it is an inpatient lab or not. The admitdate would have to be less than the LabServiceDate and the discharge date would have to be null (still a patient) or would have to be greater than LabServiceDate. All three fields are in integer data type. Again, probably very simple, but I am novice.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-04-14 : 11:59:04

If you still have a choice in the matter, store the admit date, discharge date, and lab service date as data type DATE rather than datatype INT. If you must store them as INTs, then your query would be something like this:


SELECT
	CASE WHEN CAST(CAST(admitDate AS CHAR(8)) AS DATE) <= 
		CAST(CAST(LabServiceDate AS CHAR(8)) AS DATE) 
		AND
		CAST(COALESCE(CAST(dischargeDate AS CHAR(8)),'20991231') AS DATE) >=
		CAST(CAST(LabServiceDate AS CHAR(8)) AS DATE) 
	THEN 'InPatientTest'
	ELSE 'OutpatientTest' END AS TestStatus,
	othercolumns
FROM
	YourTable;

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-04-14 : 12:14:32

I agree with James about storing a date as a date. However, assuming YYYYMMDD format on your int-date column you shouldn't need to convert it to a date to do a comparison for a case expression.

Mike1957
Starting Member

4 Posts

Posted - 2014-04-14 : 13:25:29

Thank you very much. It is in there as both date field and integer field. Appreciate your help. :)

Mike1957
Starting Member

4 Posts

Posted - 2014-04-14 : 13:42:14

This is what I have so far. The patient has two lab tests but eight admissions. this is bringing back 16 result set when I only want to match the admitdate on the two lab results

CASE WHEN CAST(CAST(admitDate AS CHAR(8)) AS DATE) <=
CAST(CAST(LabServiceDate AS CHAR(8)) AS DATE)
AND
[DischargeDateTime] is null
then admitdate
wHEN CAST(CAST(admitDate AS CHAR(8)) AS DATE) <=
CAST(CAST(LabServiceDate AS CHAR(8)) AS DATE)
AND
[DischargeDateTime] >=
CAST(CAST(LabServiceDate AS CHAR(8)) AS DATE)
then admitdate
ELSE -1 END AS 'admitdate'

Mike1957
Starting Member

4 Posts

Posted - 2014-04-14 : 13:54:17

This is my result set. The admitdate is correct for the two lab visits, however, I do not need nor want those -1 which would indicate Outpatient.

Admitdate LabServiceDate
20100618 20140204
-1 20140204
-1 20140204
-1 20140204
-1 20140204
-1 20140204
-1 20140204
-1 20140204
20100618 20140204
-1 20140204
-1 20140204
-1 20140204
-1 20140204
-1 20140204
-1 20140204
-1 20140204

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-04-14 : 16:05:48

Use the expressions in a where clause. As Lamprey pointed out, if the dates are integers in the YYYYMMDD form, they can be directly compared without converting to date data type. So the query would be

SELECT *
FROM YourTable
WHERE admitdate <= labserviceDate
	AND ISNULL(dischargedate,20991231) >= labservicedate;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources