SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 comparing dates
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Mike1957
Starting Member

USA
4 Posts

Posted - 04/14/2014 :  11:38:14  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3649 Posts

Posted - 04/14/2014 :  11:59:04  Show Profile  Reply with Quote
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;
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 04/14/2014 :  12:14:32  Show Profile  Reply with Quote
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.
Go to Top of Page

Mike1957
Starting Member

USA
4 Posts

Posted - 04/14/2014 :  13:25:29  Show Profile  Reply with Quote
Thank you very much. It is in there as both date field and integer field. Appreciate your help. :)
Go to Top of Page

Mike1957
Starting Member

USA
4 Posts

Posted - 04/14/2014 :  13:42:14  Show Profile  Reply with Quote
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'
Go to Top of Page

Mike1957
Starting Member

USA
4 Posts

Posted - 04/14/2014 :  13:54:17  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3649 Posts

Posted - 04/14/2014 :  16:05:48  Show Profile  Reply with Quote
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;
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000