• 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/