Need an update to cascade among columns but its setting all columns

  • 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()))

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

  • what I want to do is based on the value from ProjectedTimeTill100PercentSellThrough, in each record I want to update the bit fields.

    For example if I have the following row:

    ProjectedTimeTill100PercentSellThrough SellThrough1Hour SellThrough2Hour SellThrough3Hour SellThrough4Hour

    65 0 0 0 0

    The ProjectedTimeTill100PercentSellThrough is in minutes. I want an update that will go through every column and set its SellThrough1Hour, SellThrough2Hour, SellThrough3Hour, and SellThrough4Hour to a 1 or 0 depending if the minutes falls in the range. So the above would result in the following:

    ProjectedTimeTill100PercentSellThrough SellThrough1Hour SellThrough2Hour SellThrough3Hour SellThrough4Hour

    65 0 1 1 1

  • dndaughtery (10/28/2013)


    what I want to do is based on the value from ProjectedTimeTill100PercentSellThrough, in each record I want to update the bit fields.

    For example if I have the following row:

    ProjectedTimeTill100PercentSellThrough SellThrough1Hour SellThrough2Hour SellThrough3Hour SellThrough4Hour

    65 0 0 0 0

    The ProjectedTimeTill100PercentSellThrough is in minutes. I want an update that will go through every column and set its SellThrough1Hour, SellThrough2Hour, SellThrough3Hour, and SellThrough4Hour to a 1 or 0 depending if the minutes falls in the range. So the above would result in the following:

    ProjectedTimeTill100PercentSellThrough SellThrough1Hour SellThrough2Hour SellThrough3Hour SellThrough4Hour

    65 0 1 1 1

    How about some ddl and sample data?

    I have to admit that I can't even begin to understand what the logic is supposed to be here. Some explanation of how the calculation should work would help greatly.

    _______________________________________________________________

    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/

  • Why not avoid the UPDATE entirely by making [SellThrough1Hour], [SellThrough2Hour], etc. computed columns?

    You already have the CASE statements you'd need in those column definitions.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • I removed the alias from my original code and everything worked fine

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply