January 11, 2007 at 8:23 am
Is it possible to run a query with a CASE statement inside of another CASE statement?
,
CASE
when (DateDiff(mm,sxsd_svc_startdttm,@RevEnd)+1) < 0 then 0
else (DateDiff(mm,sxsd_svc_startdttm,@RevExpStart)+1)
End as "MonthsElapsed"
For example, replacing the above with:
,CASE
when ( CASE xxxx END < 0 then 0
else (DateDiff(mm,sxsd_svc_startdttm,@RoyaltyEnd)+1)
End as "MonthsElapsed"
Thanks
January 11, 2007 at 9:01 am
Why not use AND?
CASE
when (DateDiff(mm,sxsd_svc_startdttm, @RevEnd)+1) < 0 AND something_else = xyz then 0
else (DateDiff(mm,sxsd_svc_startdttm,@RevExpStart)+1)
End as "MonthsElapsed"
January 18, 2007 at 7:45 am
Sometimes it can be a little tricky to get all bits just right, but in answer to your question:
Yes, it's quite possible to nest CASE statements.
/Kenneth
January 18, 2007 at 9:12 am
Yes, CASE statements can be nested without any restriction.
Prasad Bhogadi
www.inforaise.com
January 18, 2007 at 11:03 am
Thanks for the response, i used a subquery to complete the task.
I did the first set of computations in the subquery, then reused those fields within the query...i'm still new to SQL Server 2005, but getting better:
declare
@Start as smalldatetime
declare
@End as smalldatetime
Set
@Start = '02/01/2007'
set
@End = '02/28/2007'
Select
TotalAmt
,RoyaltyAmt
,RoyaltyPeriod
,MonthsElapsed
,MonthsElapsed/RoyaltyPeriod as test1
,(Cast(Monthselapsed as float)/(cast (RoyaltyPeriod as float))) as test3
FROM
(
SELECT
sxsd_curr_accum_trxns*sxsd_chrg_rate AS TotalAmt
,(sxsd_accum*sxsd_rate*2) AS RoyaltyAmt
,(DateDiff(mm,startdttm, enddttm)+1) AS RoyaltyPeriod
,CASE
when ... < then ...
when ... > then ...
else ...
End as "MonthsElapsed"
FROM
xxxxx
)
AS
Exp_main
order
by cus_name
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply