Author |
Topic |
Peter01
Starting Member
16 Posts |
Posted - 2014-08-16 : 18:53:29
|
Hello SQLTeam,Please, help me with the following:With the query below I accumulate the 'a' code sorted by date. And I would need an extra column with the same process where I accumulate the `b`column. So the result would be:codigo | fecha | acumulado_1 | acumulado_2a | 2014-07-08| 00:00:00.000 |1.00 | 0a | 2014-07-09| 00:00:00.000 |4.00 | 2a | 2014-07-10| 00:00:00.000 |9.00 | 8 a | 2014-07-11| 00:00:00.000 |15.00 | 14a | 2014-07-12| 00:00:00.000 |10.00 | 17a | 2014-07-13| 00:00:00.000 |12.00 | 22a | 2014-07-14| 00:00:00.000 |16.00 | 29a | 2014-07-15| 00:00:00.000 |20.00 | 35Can I do this?Many thanks,PeterDECLARE @Sample TABLE ( codigo VARCHAR(10) NOT NULL, fecha DATETIME NOT NULL, diferencia NUMERIC(10, 2) NOT NULL );INSERT @Sample ( Codigo, Fecha, Diferencia )VALUES ('a', '20140708', 1), ('a', '20140709', 3), ('a', '20140710', 5), ('a', '20140711', 6), ('a', '20140712', -5), ('a', '20140713', 2), ('a', '20140714', 4), ('a', '20140715', 4), ('b', '20140709', 2), ('b', '20140710', 6), ('b', '20140711', 6), ('b', '20140712', 3), ('b', '20140713', 5), ('b', '20140714', 7), ('c', '20140712', 7), ('c', '20140714', 7), ('b', '20140715', 6);SELECT codigo, fecha, SUM(diferencia) OVER (ORDER BY fecha)AS acumuladoFROM @sampleWHERE codigo= 'a' and fecha >DATEADD(month, -3, GETDATE()) |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2014-08-17 : 02:18:12
|
SELECT codigo, fecha, SUM(diferencia) OVER (PARTITION BY codigo ORDER BY fecha ROWS UNBOUNDED PRECEDING AND CURRENT ROW) AS acumuladoFROM @sampleWHERE fecha > DATEADD(month, -3, GETDATE()) Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
Peter01
Starting Member
16 Posts |
Posted - 2014-08-17 : 12:16:22
|
Thank you Swepeso.I have added some more code to create two columns, one per codigo.However, the way I do it might not be the most efficient way. Any suggestion from your side would be great.Best,PeterDECLARE @Sample TABLE(codigo VARCHAR(10) NOT NULL,fecha DATETIME NOT NULL,diferencia NUMERIC(10, 2) NOT NULL);INSERT @Sample(Codigo,Fecha,Diferencia)VALUES ('a', '20140708', 1),('a', '20140709', 3),('a', '20140710', 5),('a', '20140711', 6),('a', '20140712', -5),('a', '20140713', 2),('a', '20140714', 4),('a', '20140715', 4),('b', '20140709', 2),('b', '20140710', 6),('b', '20140711', 6),('b', '20140712', 3),('b', '20140713', 5),('b', '20140714', 7),('c', '20140712', 7),('c', '20140714', 7),('b', '20140715', 6);SELECT codigo, fecha, SUM(diferencia) OVER (PARTITION BY codigo ORDER BY fecha ROWS between UNBOUNDED PRECEDING AND CURRENT ROW) AS acumuladointo #pruebaFROM @sampleWHERE fecha > DATEADD(month, -3, GETDATE()) SELEct fecha,CAST(SUM(Case codigo when 'a' then acumulado ELSE 0 END)as numeric(12,3)) as columna_1,CAST(SUM(Case codigo when 'b' then acumulado ELSE 0 END)as numeric(12,3)) as columna_2FROM #pruebaWHERE codigo IN ('a', 'b')GROUP BY fechaORDER BY fecha |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2014-08-17 : 14:18:13
|
[code]WITH cteSourceAS ( SELECT codigo, fecha, SUM(diferencia) OVER (PARTITION BY codigo ORDER BY fecha ROWS between UNBOUNDED PRECEDING AND CURRENT ROW) AS acumulado FROM @sample WHERE fecha > DATEADD(month, -3, GETDATE()) AND codigo IN ('a', 'b'))SELECT fecha, CAST(SUM(Case codigo when 'a' then acumulado ELSE 0 END) AS numeric(12, 3)) as columna_1, CAST(SUM(Case codigo when 'b' then acumulado ELSE 0 END) AS numeric(12, 3)) as columna_2FROM cteSourceGROUP BY fechaORDER BY fecha;[/code] Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
Peter01
Starting Member
16 Posts |
Posted - 2014-08-18 : 03:52:26
|
Impressive.Many thanks Swepeso.Best,Peter |
|
|
|
|
|