Case Statement Help TSQL 2005

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

LVL 7
Jeff SAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Open in new window

0
BrandonGalderisiCommented:
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
Jeff SAuthor Commented:
[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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Rahul Goel ITILSenior Consultant - DeloitteCommented:
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
BrandonGalderisiCommented:
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
BrandonGalderisiCommented:
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
Jeff SAuthor Commented:
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
BrandonGalderisiCommented:
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
Rahul Goel ITILSenior Consultant - DeloitteCommented:
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
BrandonGalderisiCommented:
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
Jeff SAuthor Commented:
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
BrandonGalderisiCommented:
My bad...

change the dateadd to datediff on both lines.
0
Jeff SAuthor Commented:
Brandon -

Your correct - not looking to filter.
0
Jeff SAuthor Commented:
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
BrandonGalderisiCommented:
Ok... I think it's evident that I have been up for 18+ hours.  The datediff(y should be yy.  Y is days.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeff SAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.