CODEWITHSUNDEEP
sqlsql-serversql-server-2012case
EJF
EJF

Reputation: 461

SQL query with multiple CASE statements

I'm currently working with some data that shows all patients who have undergone a certain type of procedure at a clinic. This procedure category is made up of several codes, each of which represents a more specific procedure that falls under that category. Quality initiatives at the clinic state that patients should have one of these procedures within a certain timeframe; the problem is that each them have different criteria regarding when they could have happened in order to count for quality purposes.

Let me break it down and hopefully explain it a little better. Say we have 3 codes, each representing a type of procedure.

CODE    DESCRIPTION
--------------------
1234    Basic procedure
5678    Intermediate procedure
9012    Thorough procedure

Now, each of these types of procedure have their own timeframe. The basic version has to have been performed within the past year in order to count. The intermediate one can be from any time in the past four years, and the thorough one is good for ten years. So a patient who had the intermediate procedure in 2014 would still count for quality purposes, and one who had the thorough procedure in 2009 would still count.

I've got my basic query:

SELECT DISTINCT
    PatientID,
    PatientAge,
    ProcedureCode,
    CodeDescription,
    ServiceDate,
    RenderingProvider,
    VisitType

FROM ServiceDetail

WHERE ProcedureCode IN ('1234','5678','9012')

(and yes, the procedure codes are stored as varchars because certain ones in the actual database are alphanumeric)

Now, I wanted to be able to do something using IF/THEN/ELSE logic, which would be a CASE statement in SQL unless I'm mistaken), that can look at the type of code, the service date that the procedure happened, and determine whether or not that procedure counts for quality purposes.

Example in pseudocode:

IF ProcedureCode = 5678
AND ServiceDate is between [GETDATE() minus 4 years] and GETDATE()
THEN Yes

There'd be identical statements for the two other procedure types with their respective timeframes. I would want the query to only display results when these cases returned true.

My problem is that I know what I need to do, but my SQL is rusty and I'm not sure how to do it. Basically I'm looking for tips on syntax.

Upvotes: 0

Views: 2651

Answers (3)

S3S
S3S

Reputation: 25112

This should work for your where clause

WHERE 
    ProcedureCode IN ('1234','5678','9012')
    AND VisitID IN
    (select VisitID 
    from ServiceDetail 
    where ServiceDate >=
        case 
        when ProcedureCode = 1234 then dateadd(year,-1,getdate())
        when ProcedureCode = 5678 then dateadd(year,-4,getdate())
        when ProcedureCode = 9012 then dateadd(year,-10,getdate())
        end)
    and ServiceDate <= getdate()

Or you can explicitly use an OR operator for each case of your where clause. I gave CASE expression since you explicitly asked for that.

And to fix the syntax errors of others if you choose a direct approach...

WHERE 
   (ProcedureCode = '1234' and ServiceDate Between DATEADD(year, -1, getdate()) and getdate()) 
OR
   (ProcedureCode = '5678' and ServiceDate Between DATEADD(year, -2, getdate()) and getdate())
etc...

Upvotes: 1

Phil
Phil

Reputation: 42991

What about

where
   (procedureCode = '1234' AND ServiceDate 
      Between DATEADD(year, -1, getdate()) and getdate())
or
   (procedureCode = '5678' AND ServiceDate 
       Between DATEADD(year, -4, getdate()) and getdate())
or
  .. etc

Upvotes: 1

gaganshera
gaganshera

Reputation: 2639

You don't really need cases for this, it would only make your query complicated. You can simply do it like using AND/OR like

SELECT DISTINCT
    PatientID,
    PatientAge,
    ProcedureCode,
    CodeDescription,
    ServiceDate,
    RenderingProvider,
    VisitType

FROM ServiceDetail

WHERE (ProcedureCode  = '1234' and ServiceDate Between getdate() AND DATEADD(year, -1, getdate()) OR
(ProcedureCode  = '5678' and ServiceDate Between getdate() AND DATEADD(year, -2, getdate()) ...

Upvotes: 1

Related Questions