dndaughtery (10/28/2013)
I have the following update statement where based on the value for the column in the case statement check per record to update the columns acoringly. I think you can understand from looking at the code to se what Im trying to do...an ex: if [SellThroughNow] = 1 then I also want [SellThrough1AndHalfHour], [SellThrough1Hour], and [SellThrough3hour] an don and on... to be equal to 1.
Update tblEvent
Set [SellThroughNow] =
Case When e.QTY_Available = 0
then 1
Else
0
End
,[SellThrough1Hour] =
Case When e.ProjectedTimeTill100PercentSellThrough <= 60
then 1
Else
0
End
,[SellThrough1AndHalfHour] =
Case When e.ProjectedTimeTill100PercentSellThrough <= 90
then 1
Else
0
End
,[SellThrough2Hours] =
Case When e.ProjectedTimeTill100PercentSellThrough <= 120
then 1
Else
0
End
,[SellThrough2AndHalfHours] =
Case When e.ProjectedTimeTill100PercentSellThrough <= 150
then 1
Else
0
End
,[SellThrough3Hours] =
Case When e.ProjectedTimeTill100PercentSellThrough <= 180
then 1
Else
0
End
,[SellThrough4Hours] =
Case When e.ProjectedTimeTill100PercentSellThrough <= 240
then 1
Else
0
End
,[SellThrough5Hours] =
Case When e.ProjectedTimeTill100PercentSellThrough <= 300
then 1
Else
0
End
,[SellThrough6Hours] =
Case When e.ProjectedTimeTill100PercentSellThrough <= 360
then 1
Else
0
End
,[SellThrough7Hours] =
Case When e.ProjectedTimeTill100PercentSellThrough <= 420
then 1
Else
0
End
,[SellThrough8Hours] =
Case When e.ProjectedTimeTill100PercentSellThrough <= 480
then 1
Else
0
End
,[SellThrough9Hours] =
Case When e.ProjectedTimeTill100PercentSellThrough <= 540
then 1
Else
0
End
,[SellThrough10Hours] =
Case When e.ProjectedTimeTill100PercentSellThrough <= 600
then 1
Else
0
End
,[SellThrough11Hours] =
Case When e.ProjectedTimeTill100PercentSellThrough <= 660
then 1
Else
0
End
,[SellThrough12Hours] =
Case When e.ProjectedTimeTill100PercentSellThrough <= 720
then 1
Else
0
End
,[SellThroughGreaterOrEqual13] =
Case When e.ProjectedTimeTill100PercentSellThrough > 720 OR e.ProjectedTimeTill100PercentSellThrough Is Null
then 1
Else
0
End
from tblEvent e
where ((((getdate() >= promostartdate) And (getdate() < PromoEndDate)) And RecordType = 'Promo')
OR (RecordType = 'Event' AND EventEndDate > getdate()))
So what is the question here?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/