SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Fill in gaps between 2 dates


Fill in gaps between 2 dates

Author
Message
adhikari707
adhikari707
Old Hand
Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)

Group: General Forum Members
Points: 341 Visits: 117
Hi All,
Need some help in filling up gaps between 2 dates.
Scenario : I have Product Location balance snapshot for specific dates as shown below and want to produce query to show carry forward the balance between 2 dates
Example : Product A Location 2 balance on 2018-03-01 was 25 and there is not activity between 2018-03-01 - 2018-03-06 so the balance between these 2 dates should remain 25 and on 2018-03-06 it will change to 50 and then again after that it carry forward balance .

--- Current balance snapshot
Product Location TransDate TransBal
A 1 2018-02-01 50
A 1 2018-02-15 75
A 2 2018-03-01 25
A 2 2018-03-06 50
B 1 2018-03-01 25
----- Output for Product A Location 2 -------
Product Location TransDate TransBal
A 2 2018-03-01 25
A 2 2018-03-02 25
A 2 2018-03-03 25
A 2 2018-03-04 25
A 2 2018-03-05 25
A 2 2018-03-06 50
....


---------------- Script to load test date--------------------
DECLARE @StartDate DATE='01-01-2018'
DECLARE @EndDate DATE=CONVERT(DATE,GETDATE())

IF OBJECT_ID('tempdb..#Dates', 'U') IS NOT NULL
DROP TABLE #Dates;

CREATE TABLE #Dates (
FullDate DATE
);

WHILE @StartDate <= @EndDate
BEGIN
INSERT #Dates (FullDate) VALUES (@StartDate)

SET @StartDate = DATEADD(day,1,@StartDate)
END

IF OBJECT_ID('tempdb..#Product', 'U') IS NOT NULL
DROP TABLE #Product;

CREATE TABLE #Product (
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);

Thanks
toddbellamy74
toddbellamy74
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 14
--I've used something like this to generate date lists...
--First, you need a way to generate rows:
create function [dbo].[GenerateRows](@MaxRows int) returns table
as
return (
with N1( C) as (select 0 union all select 0) -- 2 rows
,N2( C) as (select 0 from N1 as T1 cross join N1 as T2) -- 4 rows
,N3( C) as (select 0 from N2 as T1 cross join N2 as T2) -- 16 rows
,N4( C) as (select 0 from N3 as T1 cross join N3 as T2) -- 256 rows
,N5( C) as (select 0 from N4 as T1 cross join N4 as T2) -- 65,536
,N6( C) as (select 0 from N3 as T1 cross join N5 as T2) -- 1,048,576
,N7( C) as (select 0 from N3 as T1 cross join N6 as T2) -- 16,777,216
,Rowz(RowNum) as (select row_number() over (order by (select null)) from N7)
select RowNum from Rowz
where RowNum <= @MaxRows);
GO

--Then you can use that to generate dates...

declare @firstDate date = '2018-01-01', @secondDate date= getdate();

select dateadd(dd, r.RowNum-1, @firstDate) as FillDate
from
GenerateRows(datediff(dd, @firstDate, @secondDate)) as r;

Joe Celko
Joe Celko
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6265 Visits: 3485
adhikari707 - Monday, March 5, 2018 11:34 AM
Hi All,
Need some help in filling up gaps between 2 dates.
Scenario : I have Product Location balance snapshot for specific dates as shown below and want to produce query to show carry forward the balance between 2 dates
Example : Product A Location 2 balance on 2018-03-01 was 25 and there is not activity between 2018-03-01 - 2018-03-06 so the balance between these 2 dates should remain 25 and on 2018-03-06 it will change to 50 and then again after that it carry forward balance .

--- Current balance snapshot
Product Location TransDate TransBal
A 1 2018-02-01 50
A 1 2018-02-15 75
A 2 2018-03-01 25
A 2 2018-03-06 50
B 1 2018-03-01 25
----- Output for Product A Location 2 -------
Product Location TransDate TransBal
A 2 2018-03-01 25
A 2 2018-03-02 25
A 2 2018-03-03 25
A 2 2018-03-04 25
A 2 2018-03-05 25
A 2 2018-03-06 50
....


---------------- Script to load test date--------------------
DECLARE @StartDate DATE='01-01-2018'
DECLARE @EndDate DATE=CONVERT(DATE,GETDATE())

IF OBJECT_ID('tempdb..#Dates', 'U') IS NOT NULL
DROP TABLE #Dates;

CREATE TABLE #Dates (
FullDate DATE
);

WHILE @StartDate <= @EndDate
BEGIN
INSERT #Dates (FullDate) VALUES (@StartDate)

SET @StartDate = DATEADD(day,1,@StartDate)
END

IF OBJECT_ID('tempdb..#Product', 'U') IS NOT NULL
DROP TABLE #Product;

CREATE TABLE #Product (
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);

Thanks


>> Need some help in filling up gaps between 2 dates.
Scenario: I have (product_name, location_code, balance) snapshot for specific dates as shown below and want to produce query to show carry forward the balance between two dates. <<

Your attempted DDL had a lot of problems. By definition, a table must have a key. You also don’t know that there’s only one standard display format allowed in ANSI/ISO standard SQL. So first thing let’s go ahead and correct what you did post:

CREATE TABLE Product_History
(product_name CHAR(1) NOT NULL CHECK (product_name LIKE ‘[A-Z]’),
location_code CHAR(1) NOT NULL CHECK (location_code LIKE ‘[0-9]’)
trans_date DATE,
trans_amt DECIMAL (5,2) NOT NULL,
PRIMARY KEY (product_name, location_code, trans_date));

you might want to take some time to read the ISO 11179 naming rules.


Example: product_name, a location_code and a balance on 2018-03-01 was 25 and there is not activity between 2018-03-01 and 2018-03-06 so the balance between these 2 dates should remain 25 and on 2018-03-06 it will change to 50 and then again after that it carry forward balance.

But more than that, you don’t seem to appreciate the fact that, SQL is a declarative language, and you not supposed to be writing procedural code in it. We hate loops. We hate if-then-else statements. We hate local variables. We treat virtual tables (views, derived tables, etc.) as equal to materialized base tables. In fact, they have an advantage in that since they are recomputed each time there materialized, were sure that there’d information is current.

I’m also curious; why did you have an integer for a balance?

CREATE TABLE Product_History
(product_name CHAR(1) NOT NULL CHECK (product_name LIKE ‘[A-Z’]),
location_code CHAR(1) NOT NULL CHECK (location_code LIKE ‘[0-9]’)
trans_date DATE,
trans_balance_amt DECIMAL (5,2) NOT NULL,
PRIMARY KEY (product_name, location_code, trans_date));

INSERT INTO Product_History
VALUES
('A', ‘1’, '2018-02-01', 50.00),
('A', ‘1’, '2018-02-15', 75.00),
('A', ‘2’, '2018-03-01', 25.00),
('A', ‘2’, '2018-03-06', 50.00),
('B', ‘1’, '2018-03-01', 25.00);

I’d like you to stop and take some time to Google what a “calendar table” is and how to use it. It’s basically just what the name says; a list of dates, that uses a calendar date as its primary key and might include other information (we don’t care about the other stuff for now).

What we want to do is get all of the dates in the calendar table in the range that comes in his parameters. From this, if a particular calendar date has a balance in the history, then replicate the amount forward within partitions.

Here is a deliberately bad answer. Look at the results, make some corrections and get it to work (this is really a good exercise and will get you away from thinking in terms of loops).

CREATE PROCEDURE Daily_Product_Balances
(@in_start_date DATE, @in_end_date DATE)
AS
SELECT H.product_name, H.location_code, C.cal_date,
FIRST_VALUE(trans_balance_amt)
OVER (PARTITION BY H.product_name, H.location_code
ORDER BY C.cal_date)
FROM Calendar AS C
LEFT OUTER JOIN
Product_History AS H
ON C.cal_date BETWEEN @in_start_date AND @in_end_date
AND H.trans_date BETWEEN @in_start_date AND @in_end_date;

Please post DDL and follow ANSI/ISO standards when asking for help.
Jeffrey Williams 3188
Jeffrey Williams 3188
SSC Guru
SSC Guru (76K reputation)SSC Guru (76K reputation)SSC Guru (76K reputation)SSC Guru (76K reputation)SSC Guru (76K reputation)SSC Guru (76K reputation)SSC Guru (76K reputation)SSC Guru (76K reputation)

Group: General Forum Members
Points: 76576 Visits: 11010
I 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.


Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Jason A. Long
Jason A. Long
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18844 Visits: 7343
Just for the fun of it...

IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL 
BEGIN DROP TABLE #TestData; END;

CREATE TABLE #TestData (
SomeDate DATE NOT NULL PRIMARY KEY CLUSTERED, --<= this will function as a "POC" index for the query.
SomeValue INT NOT NULL
);
INSERT #TestData (SomeDate, SomeValue) VALUES
('2018-01-01', 25),
('2018-01-05', 22),
('2018-01-22', 13),
('2018-02-02', 20),
('2018-02-05', 30),
('2018-02-15', 34),
('2018-02-24', 42),
('2018-02-27', 9),
('2018-03-01', 15),
('2018-03-11', 4),
('2018-03-15', 25),
('2018-03-20', 60),
('2018-04-09', 12),
('2018-04-12', 32),
('2018-04-21', 34),
('2018-04-24', 61),
('2018-05-15', 4),
('2018-05-23', 33);

--====================================================

WITH
cte_get_lead_date AS ( --<= the CTE is just an easy option for making the "lead_date" available to the main query.
SELECT
td.SomeDate,
td.SomeValue,
lead_date = LEAD(td.SomeDate, 1, td.SomeDate) OVER (ORDER BY td.SomeDate) --<= the use of a "POC" index prevents the windowing function from causing a sort operation in the execution plan.
FROM
#TestData td
)
SELECT
SomeDate = DATEADD(DAY, atd.add_to_date, gld.SomeDate),
gld.SomeValue
FROM
cte_get_lead_date gld
CROSS APPLY (
SELECT TOP (ISNULL(NULLIF(DATEDIFF(DAY, gld.SomeDate, gld.lead_date), 0), 1))
ROW_NUMBER() OVER (ORDER BY n1.n) - 1
FROM --<= just showing an alternate means of creating an inline tally table.
( VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1) ) n1 (n) --<= 10 rows
CROSS APPLY ( VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1) ) n2 (n) --<= 100 rows
CROSS APPLY ( VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1) ) n3 (n) --<= 1000 rows (seems like 3 years should be enough "head room").
) atd (add_to_date);


The results...
SomeDate SomeValue
---------- -----------
2018-01-01 25
2018-01-02 25
2018-01-03 25
2018-01-04 25
2018-01-05 22
2018-01-06 22
2018-01-07 22
2018-01-08 22
2018-01-09 22
2018-01-10 22
2018-01-11 22
2018-01-12 22
2018-01-13 22
2018-01-14 22
2018-01-15 22
2018-01-16 22
2018-01-17 22
2018-01-18 22
2018-01-19 22
2018-01-20 22
2018-01-21 22
2018-01-22 13
2018-01-23 13
2018-01-24 13
2018-01-25 13
2018-01-26 13
2018-01-27 13
2018-01-28 13
2018-01-29 13
2018-01-30 13
2018-01-31 13
2018-02-01 13
2018-02-02 20
2018-02-03 20
2018-02-04 20
2018-02-05 30
2018-02-06 30
2018-02-07 30
2018-02-08 30
2018-02-09 30
2018-02-10 30
2018-02-11 30
2018-02-12 30
2018-02-13 30
2018-02-14 30
2018-02-15 34
2018-02-16 34
2018-02-17 34
2018-02-18 34
2018-02-19 34
2018-02-20 34
2018-02-21 34
2018-02-22 34
2018-02-23 34
2018-02-24 42
2018-02-25 42
2018-02-26 42
2018-02-27 9
2018-02-28 9
2018-03-01 15
2018-03-02 15
2018-03-03 15
2018-03-04 15
2018-03-05 15
2018-03-06 15
2018-03-07 15
2018-03-08 15
2018-03-09 15
2018-03-10 15
2018-03-11 4
2018-03-12 4
2018-03-13 4
2018-03-14 4
2018-03-15 25
2018-03-16 25
2018-03-17 25
2018-03-18 25
2018-03-19 25
2018-03-20 60
2018-03-21 60
2018-03-22 60
2018-03-23 60
2018-03-24 60
2018-03-25 60
2018-03-26 60
2018-03-27 60
2018-03-28 60
2018-03-29 60
2018-03-30 60
2018-03-31 60
2018-04-01 60
2018-04-02 60
2018-04-03 60
2018-04-04 60
2018-04-05 60
2018-04-06 60
2018-04-07 60
2018-04-08 60
2018-04-09 12
2018-04-10 12
2018-04-11 12
2018-04-12 32
2018-04-13 32
2018-04-14 32
2018-04-15 32
2018-04-16 32
2018-04-17 32
2018-04-18 32
2018-04-19 32
2018-04-20 32
2018-04-21 34
2018-04-22 34
2018-04-23 34
2018-04-24 61
2018-04-25 61
2018-04-26 61
2018-04-27 61
2018-04-28 61
2018-04-29 61
2018-04-30 61
2018-05-01 61
2018-05-02 61
2018-05-03 61
2018-05-04 61
2018-05-05 61
2018-05-06 61
2018-05-07 61
2018-05-08 61
2018-05-09 61
2018-05-10 61
2018-05-11 61
2018-05-12 61
2018-05-13 61
2018-05-14 61
2018-05-15 4
2018-05-16 4
2018-05-17 4
2018-05-18 4
2018-05-19 4
2018-05-20 4
2018-05-21 4
2018-05-22 4
2018-05-23 33

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)

Group: General Forum Members
Points: 859475 Visits: 47085
jcelko212 32090 - Monday, March 5, 2018 6:34 PM
You also don’t know that there’s only one standard display format allowed in ANSI/ISO standard SQL. So first thing let’s go ahead and correct what you did post:

Apparently, you've never actually read the standards, which allow for two formats. YYYYMMDD as the primary/preferred format and YYYY-MM-DD as a secondary format, which silently screws you in SQL Server depending on the default language. For example, if the French language is the default, SQL Server will actually use or consume what you think is YYYY-MM-DD as YYYY-DD-MM and will only report an error if you violate the allowed range for any of the parts.


--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Joe Celko
Joe Celko
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6265 Visits: 3485
Jeff Moden - Wednesday, March 7, 2018 2:12 PM
jcelko212 32090 - Monday, March 5, 2018 6:34 PM
You also don’t know that there’s only one standard display format allowed in ANSI/ISO standard SQL. So first thing let’s go ahead and correct what you did post:

Apparently, you've never actually read the standards, which allow for two formats. YYYYMMDD as the primary/preferred format and YYYY-MM-DD as a secondary format, which silently screws you in SQL Server depending on the default language. For example, if the French language is the default, SQL Server will actually use or consume what you think is YYYY-MM-DD as YYYY-DD-MM and will only report an error if you violate the allowed range for any of the parts.

No, Jeff, ISO-8601 has several display formats, but we voted to allow only the "yyyy-mm-dd" in ANSI/ISO Standard SQL. I would have like the version with a "T" between the date and time instead of a space. A solid string prevents "white space" problems.


Please post DDL and follow ANSI/ISO standards when asking for help.
adhikari707
adhikari707
Old Hand
Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)

Group: General Forum Members
Points: 341 Visits: 117
Jeffrey Williams 3188 - Tuesday, March 6, 2018 4:13 PM
I 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.

adhikari707
adhikari707
Old Hand
Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)

Group: General Forum Members
Points: 341 Visits: 117
Jason A. Long - Wednesday, March 7, 2018 12:45 PM
Just for the fun of it...

IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL 
BEGIN DROP TABLE #TestData; END;

CREATE TABLE #TestData (
SomeDate DATE NOT NULL PRIMARY KEY CLUSTERED, --<= this will function as a "POC" index for the query.
SomeValue INT NOT NULL
);
INSERT #TestData (SomeDate, SomeValue) VALUES
('2018-01-01', 25),
('2018-01-05', 22),
('2018-01-22', 13),
('2018-02-02', 20),
('2018-02-05', 30),
('2018-02-15', 34),
('2018-02-24', 42),
('2018-02-27', 9),
('2018-03-01', 15),
('2018-03-11', 4),
('2018-03-15', 25),
('2018-03-20', 60),
('2018-04-09', 12),
('2018-04-12', 32),
('2018-04-21', 34),
('2018-04-24', 61),
('2018-05-15', 4),
('2018-05-23', 33);

--====================================================

WITH
cte_get_lead_date AS ( --<= the CTE is just an easy option for making the "lead_date" available to the main query.
SELECT
td.SomeDate,
td.SomeValue,
lead_date = LEAD(td.SomeDate, 1, td.SomeDate) OVER (ORDER BY td.SomeDate) --<= the use of a "POC" index prevents the windowing function from causing a sort operation in the execution plan.
FROM
#TestData td
)
SELECT
SomeDate = DATEADD(DAY, atd.add_to_date, gld.SomeDate),
gld.SomeValue
FROM
cte_get_lead_date gld
CROSS APPLY (
SELECT TOP (ISNULL(NULLIF(DATEDIFF(DAY, gld.SomeDate, gld.lead_date), 0), 1))
ROW_NUMBER() OVER (ORDER BY n1.n) - 1
FROM --<= just showing an alternate means of creating an inline tally table.
( VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1) ) n1 (n) --<= 10 rows
CROSS APPLY ( VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1) ) n2 (n) --<= 100 rows
CROSS APPLY ( VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1) ) n3 (n) --<= 1000 rows (seems like 3 years should be enough "head room").
) atd (add_to_date);


The results...
SomeDate SomeValue
---------- -----------
2018-01-01 25
2018-01-02 25
2018-01-03 25
2018-01-04 25
2018-01-05 22
2018-01-06 22
2018-01-07 22
2018-01-08 22
2018-01-09 22
2018-01-10 22
2018-01-11 22
2018-01-12 22
2018-01-13 22
2018-01-14 22
2018-01-15 22
2018-01-16 22
2018-01-17 22
2018-01-18 22
2018-01-19 22
2018-01-20 22
2018-01-21 22
2018-01-22 13
2018-01-23 13
2018-01-24 13
2018-01-25 13
2018-01-26 13
2018-01-27 13
2018-01-28 13
2018-01-29 13
2018-01-30 13
2018-01-31 13
2018-02-01 13
2018-02-02 20
2018-02-03 20
2018-02-04 20
2018-02-05 30
2018-02-06 30
2018-02-07 30
2018-02-08 30
2018-02-09 30
2018-02-10 30
2018-02-11 30
2018-02-12 30
2018-02-13 30
2018-02-14 30
2018-02-15 34
2018-02-16 34
2018-02-17 34
2018-02-18 34
2018-02-19 34
2018-02-20 34
2018-02-21 34
2018-02-22 34
2018-02-23 34
2018-02-24 42
2018-02-25 42
2018-02-26 42
2018-02-27 9
2018-02-28 9
2018-03-01 15
2018-03-02 15
2018-03-03 15
2018-03-04 15
2018-03-05 15
2018-03-06 15
2018-03-07 15
2018-03-08 15
2018-03-09 15
2018-03-10 15
2018-03-11 4
2018-03-12 4
2018-03-13 4
2018-03-14 4
2018-03-15 25
2018-03-16 25
2018-03-17 25
2018-03-18 25
2018-03-19 25
2018-03-20 60
2018-03-21 60
2018-03-22 60
2018-03-23 60
2018-03-24 60
2018-03-25 60
2018-03-26 60
2018-03-27 60
2018-03-28 60
2018-03-29 60
2018-03-30 60
2018-03-31 60
2018-04-01 60
2018-04-02 60
2018-04-03 60
2018-04-04 60
2018-04-05 60
2018-04-06 60
2018-04-07 60
2018-04-08 60
2018-04-09 12
2018-04-10 12
2018-04-11 12
2018-04-12 32
2018-04-13 32
2018-04-14 32
2018-04-15 32
2018-04-16 32
2018-04-17 32
2018-04-18 32
2018-04-19 32
2018-04-20 32
2018-04-21 34
2018-04-22 34
2018-04-23 34
2018-04-24 61
2018-04-25 61
2018-04-26 61
2018-04-27 61
2018-04-28 61
2018-04-29 61
2018-04-30 61
2018-05-01 61
2018-05-02 61
2018-05-03 61
2018-05-04 61
2018-05-05 61
2018-05-06 61
2018-05-07 61
2018-05-08 61
2018-05-09 61
2018-05-10 61
2018-05-11 61
2018-05-12 61
2018-05-13 61
2018-05-14 61
2018-05-15 4
2018-05-16 4
2018-05-17 4
2018-05-18 4
2018-05-19 4
2018-05-20 4
2018-05-21 4
2018-05-22 4
2018-05-23 33

Hi thanks for providing the logic and i am also using something like this where i created date range for transaction and then finally use that date range to populate the missing values , will update the results.
thanks for the help

Jeffrey Williams 3188
Jeffrey Williams 3188
SSC Guru
SSC Guru (76K reputation)SSC Guru (76K reputation)SSC Guru (76K reputation)SSC Guru (76K reputation)SSC Guru (76K reputation)SSC Guru (76K reputation)SSC Guru (76K reputation)SSC Guru (76K reputation)

Group: General Forum Members
Points: 76576 Visits: 11010
I realized there is a much simpler way to get the results:


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 = dateadd(day, 1, (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
)
, products
As (
Select *
, EndTransDate = lead(p.TransDate, 1, @maxDate) over(Partition By p.Product, p.Location Order By p.TransDate)
From @Product p
)
Select ps.Product
, ps.Location
, TransDate = dt.DateValue
, ps.TransBal
From products ps
Inner Join dates dt On dt.DateValue >= ps.TransDate
And dt.DateValue < ps.EndTransDate
Order By
Product
, Location
, TransDate;


With this one - it will generate rows through the max end date for any product/location that does not have a following row. If there is a next date for the product/location it will use the values for that row...

Increase the variable @maxDate to include rows to the end date you want...for example, if you change @maxDate to: dateadd(day, 1, getdate()) you will get rows for each product/location through current date.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search