Nested Case statements syntax 1

[waubain]

I am trying my first nested case statements and have put together the following from more than one example. RxStatus can have 2 values, 'ACTIVE' has 5 conditions and 'SUSPENDED' has 2. I am calculating a new future date based on another date and the future date has to be at least 14 days into the future. My query works, but doesn't quite look right.

Is there a better way?

SELECT 
	,CASE WHEN RxStatus = 'ACTIVE' THEN 
		CASE WHEN Ratio >= 1.67 THEN GETDATE()
		ELSE CASE WHEN Ratio >=0.83 AND Ratio < 1.67 THEN DATEADD(day, 14, GETDATE())
		ELSE CASE WHEN Ratio < 0.83 AND Ratio > 0 THEN DATEADD(day, [DaysSupply], FillDateTime)
		ELSE CASE WHEN Ratio <= 0 AND FillDateTime < DATEADD(day, 14, GETDATE()) THEN DATEADD(day, 14, GETDATE())
		ELSE FillDateTime	  
		END 
		END 
		END 
		END
	 ELSE
	   CASE WHEN RxStatus = 'SUSPENDED' AND FillDateTime < DATEADD(day, 14, GETDATE()) THEN DATEADD(day, 14, GETDATE())
	   ELSE FillDateTime
	   END 
	  END AS [New Fill Date]

Thank you in advance.

You don't know what you don't know...

 

[fredericofonseca]

no need for that many case ... end

SELECT CASE
       WHEN RxStatus = 'ACTIVE'
         THEN CASE 
              WHEN Ratio >= 1.67
                   THEN GETDATE()
              WHEN Ratio >=0.83 AND Ratio < 1.67
                   THEN DATEADD(day, 14, GETDATE())
              WHEN Ratio < 0.83 AND Ratio > 0
                   THEN DATEADD(day, [DaysSupply], FillDateTime)
              WHEN Ratio <= 0 AND FillDateTime < DATEADD(day, 14, GETDATE())
                   THEN DATEADD(day, 14, GETDATE())
              ELSE FillDateTime
              END
      ELSE
        CASE
        WHEN RxStatus = 'SUSPENDED' AND FillDateTime < DATEADD(day, 14, GETDATE())
             THEN DATEADD(day, 14, GETDATE())
        ELSE FillDateTime
        END 
      END AS [New Fill Date]

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[waubain]

Works perfectly. Thank you very much.



You don't know what you don't know...