CODEWITHSUNDEEP
sqlsql-serverdatabaset-sql
user3314492
user3314492

Reputation: 241

Get previous month date values from data stored within SQL Server table

My table structure in SQL Server looks as below.

id     startdate     enddate     value
---------------------------------------
1     2019-02-06    2019-02-07    11
1     2019-01-22    2019-02-05    10
1     2019-01-15    2019-01-21    14
1     2018-12-13    2018-01-14    15
1     2018-12-09    2018-12-12    14
1     2018-08-13    2018-12-08    17
1     2018-07-19    2018-08-12    19
1     2018-06-13    2018-07-18    20

Now my query needs to display value from highest start date for that month. Which is fine and I know what needs to be done but Not start just highest date value for that month, if no value is there for that start date, we carry forward value from last month. So basically if you notice on above data, after December 2018 values, there are no values for November, October, September etc but I want to return MM/YYYY values for that month in result but value for those months should be what we found on earlier month which is August values which in this example is 17. Please note that enddate will always be as of one day before new start date begins. Probably that can be used for back filling and carry forwarding missing month values?

So my result should look like below.

id     date         value
----------------------------
1     2019-02        11
1     2019-01        10
1     2018-12        15
1     2018-11        17
1     2018-10        17
1     2018-09        17
1     2018-08        17
1     2018-07        19
1     2018-06        20

Do you think this can be done without using cursor here?

Upvotes: 3

Views: 299

Answers (2)

Xedni
Xedni

Reputation: 4715

Alexander Volok's answer is solid, so I won't go into too much extra code. But I thought I'd explain the reasoning. In essence, what you need to do is create a skeleton date table containing all the dates and primary keys you want returned. I'm guessing you have more than one id value in your real data, so probably something like this (whether you choose to persist it or not is up to you)

create table #skelly
(
    id int,
    _year int,
    _month int

    primary key (id, _year, _month)
)

You can get much more precise if you need to be, by only including dates which fall between the min and max StartDate per id, but that's an exercise I leave up to you.

From there, it's then just a matter of filling in the values you care about against that skeleton table. You can do this in a number of ways; by joining, cross applying or a correlated subquery (as Alexander Volok used).

Upvotes: 2

Alexander Volok
Alexander Volok

Reputation: 5940

DECLARE @start DATE, @end DATE;
SELECT @start = '20180601', @end = GETDATE();

;WITH Months AS 
(

  SELECT EOMONTH(DATEADD(month, n-1, @start)) AS DateValue FROM (
  SELECT TOP (DATEDIFF(MONTH, @start, @end) + 1)   
  n = ROW_NUMBER() OVER (ORDER BY [object_id])
  FROM sys.all_objects
  ) D
)


, InputData AS 
(

SELECT 1  AS id,    '2019-02-06' startdate,    '2019-02-07' as enddate,   11 AS [value] UNION ALL
SELECT 1,     '2019-01-22',    '2019-01-25',    10 UNION ALL
SELECT 1,     '2019-01-15',    '2019-01-17',    14 UNION ALL
SELECT 1,     '2018-12-13',    '2018-12-19',    15 UNION ALL
SELECT 1,     '2018-12-09',    '2018-12-10',    14 UNION ALL
SELECT 1,     '2018-08-13',    '2018-12-08',    17 UNION ALL
SELECT 1,     '2018-07-19',    '2018-07-25',    19 UNION ALL
SELECT 1,     '2018-06-13',    '2018-07-18',    20 
)

SELECT  FORMAT(m.DateValue, 'yyyy-MM') AS [Month]
, (SELECT TOP 1 I.value FROM InputData I WHERE I.startdate < M.DateValue ORDER BY I.startdate DESC ) [Value]
FROM months m
ORDER BY M.DateValue DESC

Results to:

Month   Value
2019-02 11
2019-01 10
2018-12 15
2018-11 17
2018-10 17
2018-09 17
2018-08 17
2018-07 19
2018-06 20

Upvotes: 1

Related Questions