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 2008 Forums
 Transact-SQL (2008)
 Text to date conversions and filters
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

KJinTN
Starting Member

2 Posts

Posted - 10/17/2012 :  10:24:03  Show Profile  Reply with Quote
I'm writing a query that's going to be used to populate a report. The report needs to identify patients who were hospitalized during the date range the users select (for now, just trying to get the query right, then I'll worry about adding prompts).

The problem is that the hospitalization dates are stored A) vertically - the admit date is in one row and the discharge date in another; and B) they're stored as text.

I've been able to convert the text to a date-formatted display, and flatten the records, by doing this:

MAX(CASE WHEN FindingAbbr = 'AR_HospAdmDate' then SUBSTRING(CONVERT(VARCHAR, dbo.HObservation.Value, 12), 5, 2) + '/' + SUBSTRING(CONVERT(VARCHAR, dbo.HObservation.Value, 12), 7, 2) + '/' + LEFT(CONVERT(VARCHAR, dbo.HObservation.Value, 102), 4) else null end) as HospitalAdmitDate


I imagine I'll need to use a HAVING clause, since I'm using MAX(CASE) to flatten the records, but how do I write the clause? I've tried:

MAX(CASE WHEN FindingAbbr = 'AR_HospAdmDate' then SUBSTRING(CONVERT(VARCHAR, dbo.HObservation.Value, 12), 5, 2) + '/' + SUBSTRING(CONVERT(VARCHAR, dbo.HObservation.Value, 12), 7, 2) + '/' + LEFT(CONVERT(VARCHAR, dbo.HObservation.Value, 102), 4) else null end) < '07/01/2012'

but it's not filtering the records at all. I'm well and truly stumped.

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/17/2012 :  11:02:55  Show Profile  Reply with Quote
Make your current query a subquery like shown below

DECLARE @dateRangeStart DATETIME, @dateRangeEnd DATETIME;
SET @dateRangeStart = '20120101';
SET @dateRangeEnd = '20120131'; 
    
SELECT * FROM
( 
	-- Your current query in green
	SELECT PatientId,
		   HospitalAdmitDate,
		   HosptialDischargeDate
	FROM   YourTable     
)s
WHERE
	HospitalAdmitDate BETWEEN @dateRangeStart AND @dateRangeStart
	OR HosptialDischargeDate BETWEEN @dateRangeStart AND @dateRangeStart

Edited by - sunitabeck on 10/17/2012 11:03:18
Go to Top of Page

KJinTN
Starting Member

2 Posts

Posted - 10/17/2012 :  12:22:56  Show Profile  Reply with Quote
quote:
Originally posted by sunitabeck

Make your current query a subquery like shown below



Why didn't I think of that? ::sigh:: Thanks a bunch!
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.05 seconds. Powered By: Snitz Forums 2000