October 28, 2013 at 11:35 am
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()))
October 28, 2013 at 12:12 pm
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/
October 28, 2013 at 1:31 pm
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
October 28, 2013 at 1:57 pm
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/
October 28, 2013 at 7:20 pm
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 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
October 29, 2013 at 7:55 am
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