• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 293
  • Last Modified:

SQL Case statement within a case statement syntax problem

Im wondering if someone can look at this sql statement and tell me where my syntax is incorrect.  

---This statement works

select

MonthTotal_Bucket1 = case when Date_ACTUALDATE = @PM_Comparison_StartDate then Amount1 else 0 end,
MonthTotal_Bucket1 = case when Date_ACTUALDATE >= @PM_Comparison_StartDate then Amount2 else 0 end


From Sales

blah blah


---This statement does not work.  The error message is: "Incorrect syntax near '='."

Select

case when @productflag =1 then

MonthTotal_Bucket1 = case when Date_ACTUALDATE = @PM_Comparison_StartDate then Amount1 else 0 end,

else

MonthTotal_Bucket1 = case when Date_ACTUALDATE > @PM_Comparison_StartDate then Amount1 else 0 end

end


From Sales

blah blah


0
BostonMA
Asked:
BostonMA
2 Solutions
 
Nathan RileyFounder/CTOCommented:
Like this?
Select 
 
case when @productflag =1 then
 
set MonthTotal_Bucket1 = case when Date_ACTUALDATE = @PM_Comparison_StartDate then Amount1 else 0 end,
 
else
 
set MonthTotal_Bucket1 = case when Date_ACTUALDATE > @PM_Comparison_StartDate then Amount1 else 0 end
 
end
 
 
From Sales

Open in new window

0
 
Patrick MatthewsCommented:
Select MonthTotal_Bucket1 =
      case when @productflag =1 then
            case when Date_ACTUALDATE = @PM_Comparison_StartDate then Amount1 else 0 end
      else
            case when Date_ACTUALDATE > @PM_Comparison_StartDate then Amount1 else 0 end
      end

From Sales
0
 
BostonMAAuthor Commented:
Thanks guys, but neither of those worked.

The first one by gallitin,  i got the message: "Incorrect syntax near the keyword 'set"

The second one by  matthew, isnt what I was aiming for.  I need  the MonthTotal_Bucket1 to be calculated differently depending on what the value of this flag is.  Let me write this isn psudeo code:

Select
Case when ProductFlag = 1 Then

MonthTotal_Bucket1 = case (criteria 1 current year date range)
MonthTotal_Bucket2 = case  (criteria 1 current prior year date range)
MonthTotal_Bucket3 = case (criteria 1 current prior year-1 date range)
MonthTotal_Bucket4 = case (criteria 1 current prior year-2 date range)
MonthTotal_Bucket5 = case (criteria 1 current prior year-3 date range)

Else
MonthTotal_Bucket1 = case (criteria 2 current year date range)
MonthTotal_Bucket2 = case  (criteria 2 current prior year date range)
MonthTotal_Bucket3 = case (criteria 2 current prior year-1 date range)
MonthTotal_Bucket4 = case (criteria 2 current prior year-2 date range)
MonthTotal_Bucket5 = case (criteria 2 current prior year-3 date range)
End


SO the output would look like the following everytime, except the values change in each column:

MonthTotal_Bucket1      MonthTotal_Bucket2      MonthTotal_Bucket3      MonthTotal_Bucket4      MonthTotal_Bucket5
100      48      4201      4524      1475
500      64      2451      1897      4517
75      487      748      1154      4156

Notice how the column name is the same, but the criteria used in calculating it changes based on the case statement...Does that make sense?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you cannot assign inside a CASE statment. the approach given by matthewspatrick is correct.


Select MonthTotal_Bucket1 = CASE WHEN ProductFlag = 1 THEN (criteria 1 current year date range) ELSE (criteria 2 current year date range)  END
, MonthTotal_Bucket2 = CASE WHEN ProductFlag = 1 THEN (criteria 1 current year -1 date range) ELSE (criteria 2 current year -1 date range)  END
, MonthTotal_Bucket3 = CASE WHEN ProductFlag = 1 THEN (criteria 1 current year -2 date range) ELSE (criteria 2 current year -2 date range)  END
, MonthTotal_Bucket4 = CASE WHEN ProductFlag = 1 THEN (criteria 1 current year -3 date range) ELSE (criteria 2 current year -3 date range)  END
, MonthTotal_Bucket5 = CASE WHEN ProductFlag = 1 THEN (criteria 1 current year -4 date range) ELSE (criteria 2 current year -4 date range)  END

Open in new window

0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now