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 |
|
alx_ivanovic
Starting Member
2 Posts |
 Posted - 2009-11-13 : 18:58:15
|
| Hi everybody,I have a table that contains some data. it seems just like below;ID Salesman Customer Product Quantity--------------------------------------------1 | Frank | X Corp | Prod1 | 202 | Frank | X Corp | Prod2 | 353 | Joey | Y Corp | Prod1 | 154 | Joey | Y Corp | Prod1 | 325 | Sarah | Y Corp | Prod1 | 23my table just like above. i want to get a result set just like below;ID Salesman Customer Product Quantity--------------------------------------------1 | Frank | X Corp | Prod1 | 552 | Frank | X Corp | Prod2 | 03 | Joey | Y Corp | Prod1 | 474 | Joey | Y Corp | Prod1 | 05 | Sarah | Y Corp | Prod1 | 23please somebody help me for this query. is it possible to create a query which is generating that result set? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-11-14 : 02:57:03
|
| [code]UPDATE tSET t.Quantity = CASE WHEN t.Seq=1 THEN t.Total ELSE 0 ENDFROM (SELECT ROW_NUMBER() OVER(PARTITION BY ID, Salesman, Customer ORDER BY Product) AS Seq, SUM(Quantity) OVER (PARTITION BY ID, Salesman, Customer ORDER BY Product) AS Total,* FROM Table)t[/code] |
 |
|
|
alx_ivanovic
Starting Member
2 Posts |
Posted - 2009-11-14 : 10:17:43
|
| hi visahk i tried that code but it throws an error in level15 state 1 line 5 incorrect syntax near order error.thanks for your reply |
|
|
|
|
|
|
Please help me for a sql code