Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
servmgr2004
Starting Member
10 Posts |
Posted - 2012-12-20 : 13:24:12
|
I sell CDs. They are shipped to different locations. The query below says if a CD is made for 1 location(East), change the count to zero and add its CDs to the other location (West). UPDATE #ShipLog SET Media_Copies = Media_Copies + (SELECT Media_Copies FROM #ShipLog WHERE Region = 'East' AND Job_Type = 'CD') WHERE Region = 'West' AND Job_Type = 'CD' UPDATE #ShipLog SET Media_Copies = 0 WHERE Region = 'East' AND Job_Type = 'CD' Now, the problem is if I have 6 CDs that are created for one location (East) that I need evenly split between two locations (West & South).I want the query to say IF there are 6 CDs that have printed for East, THEN change the count to zero and SPLIT the CD count between West & South. |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-20 : 13:29:01
|
[code]UPDATE #ShipLogSET Media_Copies = CASE WHEN SUM(CASE WHEN Region ='East' AND Job_Type = 'CD' THEN 1 ELSE 0 END) OVER () = 6 AND Region = 'East' THEN 0 ELSE Media_Copies + 3 ENDWHERE Region in ('East','West','South') AND Job_Type = 'CD'[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
servmgr2004
Starting Member
10 Posts |
Posted - 2012-12-20 : 13:36:34
|
Great, but I didn't mention that it need to split whatever the CD count is between two locations, meaning the count will not always be 6. It has to divide the CD count by two then move thier totals (whatever it is) to the other two locations.Something like: A1 has X number of Cds, (X dividied by 2) = Y, then change A1 to zero and add Y to B1 and B2 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-20 : 13:48:21
|
[code]UPDATE sSET Media_Copies = CASE WHEN EastTotal >0 AND Region = 'East' THEN 0 ELSE Media_Copies + EastTotal/2 ENDFROM (SELECT *,SUM(CASE WHEN Region ='East' AND Job_Type = 'CD' THEN 1 ELSE 0 END) OVER () AS EastTotal FROM #ShipLog)sWHERE Region in ('East','West','South') AND Job_Type = 'CD'[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
servmgr2004
Starting Member
10 Posts |
Posted - 2012-12-21 : 07:16:36
|
It errors on the "s" at the end of the FROM statement. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-21 : 07:54:15
|
it wont if you close braces properly (its working fine for me)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-12-21 : 07:59:20
|
quote: Originally posted by servmgr2004 It errors on the "s" at the end of the FROM statement.
Can you post us the error message?--Chandu |
|
|
|
|
|
|
|