Build a table with previous months (cumulative)
I am a bit lost on the following problem that I need to solve with a SQL query and not plsql. The idea is to build a cumulative column to calculate all previous months. The input table looks like
Month
1
2
3
..
24
I need the following table:
Month Cum_Month
1 1
2 1
2 2
3 1
3 2
3 3
..
24 1
...
24 23
It's all in SQL Server 2008, thanks in advance
source to share
You can do it like this:
DECLARE @tbl TABLE ([Month] INT)
INSERT @tbl VALUES
(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),
(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24)
SELECT Month
, ROW_NUMBER() OVER (PARTITION BY Month ORDER BY Month) num
FROM @tbl a
JOIN
(
SELECT *
FROM master..spt_values
WHERE type = 'P'
)
b ON b.number < a.Month
master..spt_values
is used to generate numbers, after the numbers are generated, the result of the subquery is concatenated @tbl
to get the number of rows that match month
. It is then ROW_NUMBER
used to generate the corresponding sequence numbers for each month.
source to share
Here's a pretty cool trick that doesn't use any tables:
SELECT N.Number as Month, N2.Number as Cum_Month
FROM
(SELECT Number FROM master..spt_values WHERE Number BETWEEN 1 AND 24 AND Type = 'P') N
JOIN (SELECT Number FROM master..spt_values WHERE Number BETWEEN 1 AND 24 AND Type = 'P') N2 ON N.Number >= N2.Number
ORDER BY N.Number, N2.Number
And Fiddle .
And if you don't really want the last 24 24 (why not), just change the second query to be between 1 and 23).
source to share