Search code examples
sql-serverdynamic-sql

Dynamically NULL last then 2 last rows based on row label in SQL SERVER

I am busy creating analytical triangles in R however i need to create the base data in SQL first to do this, basically it expects the data to have null data as it moves, so if 201912 is your current YearMonth then all rows should be null except the 1st one, if the YearMonth is 201911 then all rows except the first two should be null and so forth.

I need to do this dynamically through a stored procedure and nulling the final rows is posing a bit difficult.

I tested it out with the following:

    DECLARE @Values as VARCHAR(MAX)
SELECT @Values =
COALESCE(@Values + ', ','')+'(' + QUOTENAME(B.COLUMN_NAME, '''')+','+QUOTENAME(B.column_name)+ ')'
FROM
   (select CAST(column_name as int) as column_name from INFORMATION_SCHEMA.COLUMNS where column_name != 'origin' and table_name = 'PIVOT'
   ) AS B
   order by b.COLUMN_NAME

DECLARE @SQL as VARCHAR(MAX)
SET @SQL = '
SELECT
a.origin,
a.dev,
CASE
WHEN a.origin = 201901
and a.dev = 16
then null
WHEN a.origin = 201902
and a.dev in (16, 15)
then null
WHEN a.origin = 201903
and a.dev in (16, 15, 14)
then null
WHEN a.origin = 201904
and a.dev in (16, 15, 14, 13)
then null
WHEN a.origin = 201905
and a.dev in (16, 15, 14, 13, 12)
then null
WHEN a.origin = 201906
and a.dev in (16, 15, 14, 13, 12, 11)
then null
WHEN a.origin = 201907
and a.dev in (16, 15, 14, 13, 12, 11, 10)
then null
WHEN a.origin = 201908
and a.dev in (16, 15, 14, 13, 12, 11, 10, 9)
then null
WHEN a.origin = 201909
and a.dev in (16, 15, 14, 13, 12, 11, 10, 9, 8)
then null
WHEN a.origin = 201910
and a.dev in (16, 15, 14, 13, 12, 11, 10, 9, 8, 7)
then null
WHEN a.origin = 201911
and a.dev in (16, 15, 14, 13, 12, 11, 10, 9, 8, 7, 6)
then null
WHEN a.origin = 201912
and a.dev in (16, 15, 14, 13, 12, 11, 10, 9, 8, 7, 6, 5)
then null
WHEN a.origin = 202001
and a.dev in (16, 15, 14, 13, 12, 11, 10, 9, 8, 7, 6, 5, 4)
then null
WHEN a.origin = 202002
and a.dev in (16, 15, 14, 13, 12, 11, 10, 9, 8, 7, 6, 5, 4, 3)
then null
WHEN a.origin = 202003
and a.dev in (16, 15, 14, 13, 12, 11, 10, 9, 8, 7, 6, 5, 4, 3, 2)
then null
WHEN a.origin = 202004
and a.dev in (16, 15, 14, 13, 12, 11, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1)
then null
else a.values
end "values"
from
(select 
origin,
CAST(dev as int) "dev",
SUM(x.values) OVER(PARTITION BY origin ORDER BY CAST(dev as int)
   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) "values"
from PIVOT
CROSS APPLY
(
VALUES ' + @Values + '
) x(dev, values)
--order by origin, dev
)a
'
EXEC(@SQL)

The Case statements should give you an idea of what it is supposed to do, What I need to do is dynamically set the a.origin and the a.dev in and count down based on @values variable

I should be seeing something similar to this: Image

This would start to form a triangle effect once moved into R

Any ideas would be extremely helpful

Solution

  • Got it working, replacing the case with the following case will work:

    CASE
WHEN DATEDIFF(month,CAST(CONCAT(CAST(round(a.origin,0) as INT),''01'')as date), GETDATE()) >= a.dev
THEN a.values
end "values"