We help IT Professionals succeed at work.

Case Statement Help TSQL 2005

Jeff S
Jeff S asked
on
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

Comment
Watch Question

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

Open in new window

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

Author

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'.
Rahul Goel ITILSenior Consultant - Deloitte
CERTIFIED EXPERT

Commented:
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')))
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

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

Author

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.
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.  
Rahul Goel ITILSenior Consultant - Deloitte
CERTIFIED EXPERT

Commented:
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.
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."

Author

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.
My bad...

change the dateadd to datediff on both lines.

Author

Commented:
Brandon -

Your correct - not looking to filter.

Author

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"
Ok... I think it's evident that I have been up for 18+ hours.  The datediff(y should be yy.  Y is days.

Author

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!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.