Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need an update to cascade among columns but its setting all columns Expand / Collapse
Author
Message
Posted Monday, October 28, 2013 11:35 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, February 26, 2014 1:28 PM
Points: 280, Visits: 622
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()))


Post #1509060
Posted Monday, October 28, 2013 12:12 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:44 PM
Points: 11,970, Visits: 10,995
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1509073
Posted Monday, October 28, 2013 1:31 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, February 26, 2014 1:28 PM
Points: 280, Visits: 622
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

Post #1509095
Posted Monday, October 28, 2013 1:57 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:44 PM
Points: 11,970, Visits: 10,995
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1509104
Posted Monday, October 28, 2013 7:20 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:03 PM
Points: 3,590, Visits: 5,098
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1509167
Posted Tuesday, October 29, 2013 7:55 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, February 26, 2014 1:28 PM
Points: 280, Visits: 622
I removed the alias from my original code and everything worked fine
Post #1509360
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse