Home Forums SQL Server 2016 SQL Server 2016 - Development and T-SQL Fill in gaps between 2 dates RE: Fill in gaps between 2 dates
March 8, 2018 at 7:47 am
Jeffrey Williams 3188 - Tuesday, March 6, 2018 4:13 PMI use something like this to generate dates:
Declare @minDate date = '2018-03-01'
, @maxDate date = '2018-03-06';With dates
As (
Select n.id
, DateValue = dateadd(day, n.id, @minDate)
From (Select row_number() over(Order By ac.object_id) - 1 As id
From sys.all_columns ac) As n
)
Select *
From dates d
Where d.DateValue <= @maxDate;Now - you just need to get the row that applies for that date - and to do that we can use a CROSS APPLY:
Declare @product Table (
Product char(1)
, Location tinyint
, TransDate DATE
, TransBal int
);Insert @product (
Product
, Location
, TransDate
, TransBal
)
Values ('A', 1, '02/01/2018', 50)
, ('A', 1, '02/15/2018', 75)
, ('A', 2, '03/01/2018', 25)
, ('A', 2, '03/06/2018', 50)
, ('B', 1, '03/01/2018', 25);Select * From @product;
Declare @minDate date = (Select min(p.TransDate) From @product p)
, @maxDate date = (Select max(p.TransDate) From @product p);With dates
As (
Select n.id
, DateValue = dateadd(day, n.id, @minDate)
From (Select row_number() over(Order By ac.object_id) - 1 As id
From sys.all_columns ac) As n
)
Select pd.Product
, pd.Location
, d.DateValue As TransDate
, pd.TransBal
From dates d
Cross Apply (Select Top 1
*
From @product p
Where p.TransDate <= d.DateValue
Order By
p.TransDate desc) As pd
Where d.DateValue <= @maxDate;If you already have a number or tally table - you can replace the derived table with the row_number function and use your number or tally table directly.
Edit: I missed part of the relationship in the cross apply - if multiple products can have the same transdate then you need to include that. To do that you would change the query to:
With dates
As (
Select n.id
, DateValue = dateadd(day, n.id, @minDate)
From (Select row_number() over(Order By ac.object_id) - 1 As id
From sys.all_columns ac) As n
)
Select Distinct pd.Product
, pd.Location
, d.DateValue As TransDate
, pd.TransBal
From dates d
Cross Join @product p
Cross Apply (Select Top 1
*
From @product p1
Where p1.Product = p.Product And p1.TransDate <= d.DateValue
Order By
p1.TransDate desc) As pd
Where d.DateValue <= @maxDate;The CROSS JOIN creates a row for each date - the WHERE in the CROSS APPLY then limits the value to the preceding product value - and the DISTINCT eliminates any duplicates generated by the cross join.
Hi thanks for giving the idea about the logic to use , will try this approach and update you the results that i'll get.