SQL Case statement within a case statement syntax problem

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

Select allOpen in new window

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?