?
Solved

Case Statement Help TSQL 2005

Posted on 2008-01-28
16
Medium Priority
?
169 Views
Last Modified: 2012-05-05
In my Database, I do not have a [Last Visit Date]. I have had to pull it by doing the following:

(select top 1 visit from patientvisit pv
where visit >= ISNULL(NULL,'1/1/1900') and visit < dateadd(d, 1,ISNULL(NULL,'1/1/3000')) AND
pp.patientprofileid = pv.PatientProfileID and datediff(day, getDate(), visit) < 0 order by visit desc) as [Last Visit Date]

My client would like to have a listing of patients with a visit within the past 2 years and without a visit in the past 2 years. What I would like to do is have a case statement that evaluates like:

Case
When [LastVisitDate] within 2 yrs Then 'WithinYRs'
When [LastVisitDate] not in 2 yrs Then 'Outside2Yrs'
END ... some field name

So basically, either your in 2 yrs or your not.
/* Patient List*/
SET NOCOUNT ON
 
DECLARE @Zip varchar(40)
SELECT @Zip = LTRIM(RTRIM('NULL')) + '%';
WITH cteMedlitsPatientStatus AS
(
SELECT * FROM Medlists WHERE TableName = 'PatientProfileStatus'
)
 
SELECT 		
	PatientID, RespSameAsPatient=isnull(PatientSameAsGuarantor,0), 
	PatientName=CASE 
	WHEN RTRIM(pp.Last + ' ' + ISNULL(pp.Suffix,'')) <> '' THEN 
	RTRIM(RTRIM(pp.Last + ' ' + ISNULL(pp.Suffix,'')) + ', ' + ISNULL(pp.First,'') + ' ' + ISNULL(pp.Middle,'')) 
	ELSE RTRIM(ISNULL(pp.First,'') + ' ' + ISNULL(pp.Middle,'')) 
	END, 
	PatientAddr1=pp.Address1, PatientAddr2=pp.Address2, 
	PatientCity=pp.City, PatientState=pp.State, PatientZip=pp.Zip, 
	PatientRespName=CASE 
	WHEN RTRIM(pr.Last + ' ' + ISNULL(pr.Suffix,'')) <> '' THEN 
	RTRIM(RTRIM(pr.Last + ' ' + ISNULL(pr.Suffix,'')) + ', ' + ISNULL(pr.First,'') + ' ' + ISNULL(pr.Middle,'')) 
	ELSE RTRIM(ISNULL(pr.First,'') + ' ' + ISNULL(pr.Middle,'')) 
	END, 
	PatientRespAddr1=pr.Address1, PatientRespAddr2=pr.Address2, PatientRespCity=pr.City, 
	PatientRespState=pr.State, PatientRespZip=pr.Zip, FinancialClass=isnull(ml.Description,'none'), 
	Doctor=df.ListName,Facility=df1.OrgName,Balance=isnull(ppa.PatBalance,0)+isnull(ppa.InsBalance,0), pp.DeathDate, 
	Status = ml1.Description, 
	pp.BirthDate,
	(select top 1 visit 
	from patientvisit pv 
	where visit >= ISNULL(NULL,'1/1/1900') and 
	visit < dateadd(d, 1,ISNULL(NULL,'1/1/3000')) AND
	pp.patientprofileid = pv.PatientProfileID 
	and datediff(day, getDate(), visit) < 0 order by visit desc) as [Last Visit Date]
 
FROM 	PatientProfile pp
	LEFT JOIN PatientProfileAgg ppa ON pp.PatientProfileID = ppa.PatientProfileID
	LEFT JOIN Guarantor pr ON pp.GuarantorID = pr.GuarantorID
	LEFT JOIN MedLists ml ON pp.FinancialClassMID = ml.MedListsID
	LEFT JOIN DoctorFacility df ON pp.DoctorID = df.DoctorFacilityID
	LEFT JOIN DoctorFacility df1 ON pp.FacilityId = df1.DoctorFacilityID
	LEFT JOIN cteMedlitsPatientStatus ml1 ON pp.PatientStatusMId = ml1.MedlistsId
 
WHERE etc ......

Open in new window

0
Comment
Question by:Jeff S
  • 8
  • 6
  • 2
16 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 20765310
Here it is
Case dateadd(y,[LastVisitDate],getdate())
When <2 then 'Less Than 2'
else '2 or more'
END 

Open in new window

0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 20765318
Actually...


Case 
When dateadd(y,[LastVisitDate],getdate())<2  then 'Less Than 2'
When dateadd(y,[LastVisitDate],getdate())>=2 then '2 or more'
else 'No detected visit'
END 

Open in new window

0
 
LVL 7

Author Comment

by:Jeff S
ID: 20765326
[LastVisitDate] Is not a field in my Database, therefore it is throwing the following error:

Msg 207, Level 16, State 1, Line 37
Invalid column name 'LastVisitDate'.
Msg 207, Level 16, State 1, Line 38
Invalid column name 'LastVisitDate'.
0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
LVL 9

Expert Comment

by:Rahul Goel ITIL
ID: 20765347
I will not suggest you the case, you can go through with where clause:
(visit >= ISNULL(NULL,'1/1/1900') and visit < dateadd(d, 1,ISNULL(NULL,'1/1/3000'))) or
(visit >= ISNULL(NULL,'1/1/1900') and visit < dateadd(d, 1,ISNULL(NULL,'1/1/3000')))
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 20765348
What field represents the last visit date then?
-- I would change:
        pp.BirthDate,
        (select top 1 visit 
        from patientvisit pv 
        where visit >= ISNULL(NULL,'1/1/1900') and 
        visit < dateadd(d, 1,ISNULL(NULL,'1/1/3000')) AND
        pp.patientprofileid = pv.PatientProfileID 
        and datediff(day, getDate(), visit) < 0 order by visit desc) as [Last Visit Date]
 
FROM    PatientProfile pp
        LEFT JOIN PatientProfileAgg ppa ON pp.PatientProfileID = ppa.PatientProfileID
 
-- to:
        pp.BirthDate,
        lvd.[LastVisitDate]
FROM    PatientProfile pp
        LEFT JOIN PatientProfileAgg ppa ON pp.PatientProfileID = ppa.PatientProfileID
 
 
and add this join:
 
left outer join (select PatientProfileId,max(Visit) as LastVisitDate from patientvisit group by patientProfileId) lpv
on pp.patientProfileId  = lpv.patientProfileId 

Open in new window

0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 20765368
You can then use the previously provided case to return the status of less than or older than 2 years.

The end result should be:
SET NOCOUNT ON
 
DECLARE @Zip varchar(40)
SELECT @Zip = LTRIM(RTRIM('NULL')) + '%';
WITH cteMedlitsPatientStatus AS
(
SELECT * FROM Medlists WHERE TableName = 'PatientProfileStatus'
)
 
SELECT          
        PatientID, RespSameAsPatient=isnull(PatientSameAsGuarantor,0), 
        PatientName=CASE 
        WHEN RTRIM(pp.Last + ' ' + ISNULL(pp.Suffix,'')) <> '' THEN 
        RTRIM(RTRIM(pp.Last + ' ' + ISNULL(pp.Suffix,'')) + ', ' + ISNULL(pp.First,'') + ' ' + ISNULL(pp.Middle,'')) 
        ELSE RTRIM(ISNULL(pp.First,'') + ' ' + ISNULL(pp.Middle,'')) 
        END, 
        PatientAddr1=pp.Address1, PatientAddr2=pp.Address2, 
        PatientCity=pp.City, PatientState=pp.State, PatientZip=pp.Zip, 
        PatientRespName=CASE 
        WHEN RTRIM(pr.Last + ' ' + ISNULL(pr.Suffix,'')) <> '' THEN 
        RTRIM(RTRIM(pr.Last + ' ' + ISNULL(pr.Suffix,'')) + ', ' + ISNULL(pr.First,'') + ' ' + ISNULL(pr.Middle,'')) 
        ELSE RTRIM(ISNULL(pr.First,'') + ' ' + ISNULL(pr.Middle,'')) 
        END, 
        PatientRespAddr1=pr.Address1, PatientRespAddr2=pr.Address2, PatientRespCity=pr.City, 
        PatientRespState=pr.State, PatientRespZip=pr.Zip, FinancialClass=isnull(ml.Description,'none'), 
        Doctor=df.ListName,Facility=df1.OrgName,Balance=isnull(ppa.PatBalance,0)+isnull(ppa.InsBalance,0), pp.DeathDate, 
lpv.[LastVisitDate],
Case 
When dateadd(y,[LastVisitDate],getdate())<2  then 'Less Than 2'
When dateadd(y,[LastVisitDate],getdate())>=2 then '2 or more'
else 'No detected visit'
END '2YearStatus'
 
FROM    PatientProfile pp
        LEFT JOIN PatientProfileAgg ppa ON pp.PatientProfileID = ppa.PatientProfileID
        LEFT JOIN Guarantor pr ON pp.GuarantorID = pr.GuarantorID
        LEFT JOIN MedLists ml ON pp.FinancialClassMID = ml.MedListsID
        LEFT JOIN DoctorFacility df ON pp.DoctorID = df.DoctorFacilityID
        LEFT JOIN DoctorFacility df1 ON pp.FacilityId = df1.DoctorFacilityID
        LEFT JOIN cteMedlitsPatientStatus ml1 ON pp.PatientStatusMId = ml1.MedlistsId
        left outer join (select PatientProfileId,max(Visit) as LastVisitDate from patientvisit group by patientProfileId) lpv
                on pp.patientProfileId  = lpv.patientProfileId 

Open in new window

0
 
LVL 7

Author Comment

by:Jeff S
ID: 20765371
rahu ketu patal -

I have to have a field name with this. I could easily add it in the Where clause, however I am needing it as a field.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 20765378
Adding it to the where clause would only allow you to filter based on it's state, it wouldn't really help you report what it was.  
0
 
LVL 9

Expert Comment

by:Rahul Goel ITIL
ID: 20765402
I agree to you, but visit is the datefield, which is storing the date of visit.

And you can put condition on that and filter the data.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 20765415
My impression is that he is not attempting to filter, but to:

"What I would like to do is have a case statement that evaluates like:

Case
When [LastVisitDate] within 2 yrs Then 'WithinYRs'
When [LastVisitDate] not in 2 yrs Then 'Outside2Yrs'
END ... some field name

So basically, either your in 2 yrs or your not."
0
 
LVL 7

Author Comment

by:Jeff S
ID: 20765416
Brandon -

Getting this now:

Msg 8116, Level 16, State 1, Line 5
Argument data type datetime is invalid for argument 2 of dateadd function.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 20765422
My bad...

change the dateadd to datediff on both lines.
0
 
LVL 7

Author Comment

by:Jeff S
ID: 20765423
Brandon -

Your correct - not looking to filter.
0
 
LVL 7

Author Comment

by:Jeff S
ID: 20765455
Brandon -

I am getting back results, however they dont appear correct.

Example - I got a Last Visit Date of 2007-10-25 08:30:00.000 and it tells me the 2YrStatus = "2 or more" - With this date, it should have evaluated to "Less than 2"
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 2000 total points
ID: 20765467
Ok... I think it's evident that I have been up for 18+ hours.  The datediff(y should be yy.  Y is days.
0
 
LVL 7

Author Comment

by:Jeff S
ID: 20765487
LOL - I cant believe I missed that too ... Im in same boat and alittle slow here. Thanks for all the help and understanding the issue clearly. MANY THANKS!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question