Nested Case Statement
-
Hi, This is the first time I have asked a question here so bear with me.
I have a case statement that needs to compile in one field. When I run this statement in Query Analyzer, I get the message 'Case expressions may only be nested to a level of 10'. I did not know there was a limit. Does anyone have any other ideas how to accomplish this task?
This statement deals with 3 different dates to compare, but the user wants only one column for a total.
Thanks for any help.
SUM(CASE WHEN '#Date1#' > '#Date2#' then
Case When '#Date1#' = '#r.dtDomain#' then
isnull(b.camt1,0)
WHEN dateadd(Month,+01,'#Date1#') > '#Date2#' then
Case When '#Date1#' = dateadd(Month,-01,'#r.dtDomain#') then isnull(b.camt1,0)
When '#Date1#' = '#r.dtDomain#' then isnull(b.camt2,0)
WHEN dateadd(Month,+02,'#Date1#') > '#Date2#' then
Case When '#Date1#' = dateadd(Month,-02,'#r.dtDomain#') then isnull(b.camt1,0)
When '#Date1#' = dateadd(Month,-01,'#r.dtDomain#') then isnull(b.camt2,0)
When '#Date1#' = '#r.dtDomain#' then isnull(b.camt3,0)
WHEN dateadd(Month,+03,'#Date1#') > '#Date2#' then
Case When '#Date1#' = dateadd(Month,-03,'#r.dtDomain#') then isnull(b.camt1,0)
When '#Date1#' = dateadd(Month,-02,'#r.dtDomain#') then isnull(b.camt2,0)
When '#Date1#' = dateadd(Month,-01,'#r.dtDomain#') then isnull(b.camt3,0)
When '#Date1#' = '#r.dtDomain#' then isnull(b.camt4,0)
WHEN dateadd(Month,+04,'#Date1#') > '#Date2#' then
Case When '#Date1#' = dateadd(Month,-04,'#r.dtDomain#') then isnull(b.camt1,0)
When '#Date1#' = dateadd(Month,-03,'#r.dtDomain#') then isnull(b.camt2,0)
When '#Date1#' = dateadd(Month,-02,'#r.dtDomain#') then isnull(b.camt3,0)
When '#Date1#' = dateadd(Month,-01,'#r.dtDomain#') then isnull(b.camt4,0)
When '#Date1#' = '#r.dtDomain#' then isnull(b.camt5,0)
WHEN dateadd(Month,+05,'#Date1#') > '#Date2#' then
Case When '#Date1#' = dateadd(Month,-05,'#r.dtDomain#') then isnull(b.camt1,0)
When '#Date1#' = dateadd(Month,-04,'#r.dtDomain#') then isnull(b.camt2,0)
When '#Date1#' = dateadd(Month,-03,'#r.dtDomain#') then isnull(b.camt3,0)
When '#Date1#' = dateadd(Month,-02,'#r.dtDomain#') then isnull(b.camt4,0)
When '#Date1#' = dateadd(Month,-01,'#r.dtDomain#') then isnull(b.camt5,0)
When '#Date1#' = '#r.dtDomain#' then isnull(b.camt6,0)
WHEN dateadd(Month,+06,'#Date1#') > '#Date2#' then
Case When '#Date1#' = dateadd(Month,-06,'#r.dtDomain#') then isnull(b.camt1,0)
When '#Date1#' = dateadd(Month,-05,'#r.dtDomain#') then isnull(b.camt2,0)
When '#Date1#' = dateadd(Month,-04,'#r.dtDomain#') then isnull(b.camt3,0)
When '#Date1#' = dateadd(Month,-03,'#r.dtDomain#') then isnull(b.camt4,0)
When '#Date1#' = dateadd(Month,-02,'#r.dtDomain#') then isnull(b.camt5,0)
When '#Date1#' = dateadd(Month,-01,'#r.dtDomain#') then isnull(b.camt6,0)
When '#Date1#' = '#r.dtDomain#' then isnull(b.camt7,0)
WHEN dateadd(Month,+07,'#Date1#') > '#Date2#' then
Case When '#Date1#' = dateadd(Month,-07,'#r.dtDomain#') then isnull(b.camt1,0)
When '#Date1#' = dateadd(Month,-06,'#r.dtDomain#') then isnull(b.camt2,0)
When '#Date1#' = dateadd(Month,-05,'#r.dtDomain#') then isnull(b.camt3,0)
When '#Date1#' = dateadd(Month,-04,'#r.dtDomain#') then isnull(b.camt4,0)
When '#Date1#' = dateadd(Month,-03,'#r.dtDomain#') then isnull(b.camt5,0)
When '#Date1#' = dateadd(Month,-02,'#r.dtDomain#') then isnull(b.camt6,0)
When '#Date1#' = dateadd(Month,-01,'#r.dtDomain#') then isnull(b.camt7,0)
When '#Date1#' = '#r.dtDomain#' then isnull(b.camt8,0)
WHEN dateadd(Month,+08,'#Date1#') > '#Date2#' then
Case When '#Date1#' = dateadd(Month,-08,'#r.dtDomain#') then isnull(b.camt1,0)
When '#Date1#' = dateadd(Month,-07,'#r.dtDomain#') then isnull(b.camt2,0)
When '#Date1#' = dateadd(Month,-06,'#r.dtDomain#') then isnull(b.camt3,0)
When '#Date1#' = dateadd(Month,-05,'#r.dtDomain#') then isnull(b.camt4,0)
When '#Date1#' = dateadd(Month,-04,'#r.dtDomain#') then isnull(b.camt5,0)
When '#Date1#' = dateadd(Month,-03,'#r.dtDomain#') then isnull(b.camt6,0)
When '#Date1#' = dateadd(Month,-02,'#r.dtDomain#') then isnull(b.camt7,0)
When '#Date1#' = dateadd(Month,-01,'#r.dtDomain#') then isnull(b.camt8,0)
When '#Date1#' = '#r.dtDomain#' then isnull(b.camt9,0)
WHEN dateadd(Month,+09,'#Date1#') > '#Date2#' then
Case When '#Date1#' = dateadd(Month,-09,'#r.dtDomain#') then isnull(b.camt1,0)
When '#Date1#' = dateadd(Month,-08,'#r.dtDomain#') then isnull(b.camt2,0)
When '#Date1#' = dateadd(Month,-07,'#r.dtDomain#') then isnull(b.camt3,0)
When '#Date1#' = dateadd(Month,-06,'#r.dtDomain#') then isnull(b.camt4,0)
When '#Date1#' = dateadd(Month,-05,'#r.dtDomain#') then isnull(b.camt5,0)
When '#Date1#' = dateadd(Month,-04,'#r.dtDomain#') then isnull(b.camt6,0)
When '#Date1#' = dateadd(Month,-03,'#r.dtDomain#') then isnull(b.camt7,0)
When '#Date1#' = dateadd(Month,-02,'#r.dtDomain#') then isnull(b.camt8,0)
When '#Date1#' = dateadd(Month,-01,'#r.dtDomain#') then isnull(b.camt9,0)
When '#Date1#' = '#r.dtDomain#' then isnull(b.camt10,0)
WHEN dateadd(Month,+10,'#Date1#') > '#Date2#' then
Case When '#Date1#' = dateadd(Month,-10,'#r.dtDomain#') then isnull(b.camt1,0)
When '#Date1#' = dateadd(Month,-09,'#r.dtDomain#') then isnull(b.camt2,0)
When '#Date1#' = dateadd(Month,-08,'#r.dtDomain#') then isnull(b.camt3,0)
When '#Date1#' = dateadd(Month,-07,'#r.dtDomain#') then isnull(b.camt4,0)
When '#Date1#' = dateadd(Month,-06,'#r.dtDomain#') then isnull(b.camt5,0)
When '#Date1#' = dateadd(Month,-05,'#r.dtDomain#') then isnull(b.camt6,0)
When '#Date1#' = dateadd(Month,-04,'#r.dtDomain#') then isnull(b.camt7,0)
When '#Date1#' = dateadd(Month,-03,'#r.dtDomain#') then isnull(b.camt8,0)
When '#Date1#' = dateadd(Month,-02,'#r.dtDomain#') then isnull(b.camt9,0)
When '#Date1#' = dateadd(Month,-01,'#r.dtDomain#') then isnull(b.camt10,0)
When '#Date1#' = '#r.dtDomain#' then isnull(b.camt11,0)
WHEN dateadd(Month,+11,'#Date1#') > '#Date2#' then
Case When '#Date1#' = dateadd(Month,-11,'#r.dtDomain#') then isnull(b.camt1,0)
When '#Date1#' = dateadd(Month,-10,'#r.dtDomain#') then isnull(b.camt2,0)
When '#Date1#' = dateadd(Month,-09,'#r.dtDomain#') then isnull(b.camt3,0)
When '#Date1#' = dateadd(Month,-08,'#r.dtDomain#') then isnull(b.camt4,0)
When '#Date1#' = dateadd(Month,-07,'#r.dtDomain#') then isnull(b.camt5,0)
When '#Date1#' = dateadd(Month,-06,'#r.dtDomain#') then isnull(b.camt6,0)
When '#Date1#' = dateadd(Month,-05,'#r.dtDomain#') then isnull(b.camt7,0)
When '#Date1#' = dateadd(Month,-04,'#r.dtDomain#') then isnull(b.camt8,0)
When '#Date1#' = dateadd(Month,-03,'#r.dtDomain#') then isnull(b.camt9,0)
When '#Date1#' = dateadd(Month,-02,'#r.dtDomain#') then isnull(b.camt10,0)
When '#Date1#' = dateadd(Month,-01,'#r.dtDomain#') then isnull(b.camt11,0)
When '#Date1#' = '#r.dtDomain#' then isnull(b.camt12,0) else 0 End) as AnnFcst
-
Can you post the table definition, sample data and the desired output?? I'm sure there's a better way to do this.
-
Sure,
Table 'b' contains a primary key, a revision handle, an account handle, and 20 fields with budget amounts stored for expenses and revenue. There is no date stored in this table, therefore I have to calculate the date based on the filter items put in ('#date1#' and '#date2#') and the budget revision date (r.dtDomain) which comes from the a table (bgrevision) that holds the revision name and date.
For Example:
Bgrevision may hold the following:
Hmy = 1919, sdesc = 'Feb Forecast', r.dtDomain=2005-02-01
This is linked to bgBudget by hrevision which may contain the following:
hrevision = 1919, hAcct = 304 (pointer to acct table), cAmt1 = 50000, cAmt2 = 45000, cAmt3 = 65000, cAmt4 = 75000, cAmt5 = 95000, cAmt6 = 25000 etc
Say for example the user wants to run the report for April's budget on. This would look something like this: (cAmt2 + cAmt3+cAmt4+cAmt5+cAmt6+cAmt7+cAmt8+cAmt9)
Account Forecast
Rental Income 305000
Thanks for your help.
Hope this makes sense.
-
Don't have much time to deeply analyze this but if you want to report something based on a date or time period, you need a date column somewhere. While I don't understand the way you calculate it, here's an exemple of a query with a date column that does something like you want :
Select sum(Expenses) from dbo.Budget where DateCol between Date1 and Date2
---group by compagny...
You're done just like that. This query will work for any daterange the users can imagine.
-
syntax-wize you miss a couple of end-(case) statements ??
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I wonder if you could you simplify the query by using CASE DateDiff(Month,#Date1#,#Date2#)
WHEN 0 THEN
WHEN 1 THEN
WHEN 2 THEN
...etc
END
rather than all those dateadds
If it ain't broke, don't fix it...
-
Thank you. Remi - there is no date field to use in the table in which I need to pull the info from. This table is set up by a 3rd party vendor and I cannot change it.
David, thanks for the tip. I will try that.
-
Tammy,
From a cursory glance at your SQL statement I can guess that the root of the problem is that you have to go to different columns based on certain conditions. If this were true, a quick solution would be to write a dynamic SQL to get the necessary result set in a #temp table (or @Table) and then make a join. On detailed analysis, I am sure we could figure out other and better ways to do this. Just my 2 cents.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply