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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help with Query

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_2
a | 2014-07-08| 00:00:00.000 |1.00 | 0
a | 2014-07-09| 00:00:00.000 |4.00 | 2
a | 2014-07-10| 00:00:00.000 |9.00 | 8
a | 2014-07-11| 00:00:00.000 |15.00 | 14
a | 2014-07-12| 00:00:00.000 |10.00 | 17
a | 2014-07-13| 00:00:00.000 |12.00 | 22
a | 2014-07-14| 00:00:00.000 |16.00 | 29
a | 2014-07-15| 00:00:00.000 |20.00 | 35


Can I do this?

Many thanks,
Peter


DECLARE @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 acumulado
FROM @sample
WHERE 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 acumulado
FROM @sample
WHERE fecha > DATEADD(month, -3, GETDATE())


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

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,
Peter


DECLARE @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 acumulado
into #prueba
FROM @sample
WHERE 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_2

FROM #prueba
WHERE codigo IN ('a', 'b')
GROUP BY fecha
ORDER BY fecha
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-17 : 14:18:13
[code]WITH cteSource
AS (
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_2
FROM cteSource
GROUP BY fecha
ORDER BY fecha;[/code]


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

Peter01
Starting Member

16 Posts

Posted - 2014-08-18 : 03:52:26
Impressive.
Many thanks Swepeso.
Best,
Peter
Go to Top of Page
   

- Advertisement -