Filling missing values with SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Filling missing values with SQL

Author

Topic

dan1982
Starting Member

2 Posts

Posted - 2014-06-23 : 17:09:02

Hello,

I have a three columns table that looks like this.

Data
ID Date Amt
1 1/1/2014 5
1 6/1/2014 10
2 1/1/2014 5
2 3/1/2014 10
2 5/1/2014 15

I need SQL to fill-in the missing data in between dates until max date is reach and carryover the amount. The result should look like this.

Result
ID Date Amt
1 1/1/2014 5
1 2/1/2014 5
1 3/1/2014 5
1 4/1/2014 5
1 5/1/2014 5
1 6/1/2014 10
1 7/1/2014 10
2 1/1/2014 5
2 2/1/2014 5
2 3/1/2014 10
2 4/1/2014 10
2 5/1/2014 15
2 6/1/2014 15
2 7/1/2014 15

Thank in advance.

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-06-24 : 03:32:08

[code]
;with cteData
AS
(select 1 as ID,CAST('1/1/2014' as Date) as [Date], 5 as Amt UNION ALL
select 1, CAST('6/1/2014' AS DATE) , 10 union all
select 2, CAST('1/1/2014' AS DATE) , 5 union all
select 2, CAST('3/1/2014' AS DATE), 10 union all
select 2, CAST('5/1/2014' AS DATE), 15 )
,
cteCalendar
AS
(
SELECT 1 as lvl,CAST('1/1/2014' AS DATE) as dDate

UNION ALL

SELECT lvl + 1 , DATEADD(m,1,dDate)
FROM
cteCalendar
WHERE
dDate<'2014/07/01')
,
cteDataNext
AS
(
SELECT T1.ID,T1.[Date],T1.Amt,
ISNULL(A.[Date],T1.[Date]) as NextDate
FROM
cteData as T1
OUTER APPLY --this give next day from interval
(
SELECT top (1) T2.[date]
FROM cteData as T2
WHERE T1.ID=T2.ID
AND T1.[date]<T2.[date]
ORDER by T2.[date] asc
)A
)

SELECT
A.ID
,B.dDate as [DATE]
,A.Amt
FROM
cteDataNext as A
FULL JOIN cteCalendar as B
ON (A.[Date] <= B.dDate AND A.NextDate > B.dDate)
OR (A.NextDate = A.[Date] and A.nextDate <= B.dDate)
ORDER BY A.ID, A.Date,B.dDate
[/code]

output:
[code]
ID DATE Amt
1 2014-01-01 5
1 2014-02-01 5
1 2014-03-01 5
1 2014-04-01 5
1 2014-05-01 5
1 2014-06-01 10
1 2014-07-01 10
2 2014-01-01 5
2 2014-02-01 5
2 2014-03-01 10
2 2014-04-01 10
2 2014-05-01 15
2 2014-06-01 15
2 2014-07-01 15
[/code]

sabinWeb MCP

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-24 : 04:35:49

[code]DECLARE @Sample TABLE
(
ID TINYINT NOT NULL,
Dt DATETIME NOT NULL,
Amt INT NOT NULL
);

INSERT @Sample
(
ID,
Dt,
Amt
)
VALUES (1, '20140101', 5),
(1, '20140601', 10),
(2, '20140101', 5),
(2, '20140301', 10),
(2, '20140601', 15);

-- SwePeso
DECLARE @minDt DATETIME,
@maxDt DATETIME;

SELECT @minDt = MIN(Dt),
@maxDt = MAX(Dt)
FROM @Sample;

SELECT a.ID,
b.Dt,
f.Amt
FROM (
SELECT DISTINCT ID
FROM @Sample
) AS a
CROSS JOIN (
SELECT DATEADD(MONTH, Number, @minDt) AS Dt
FROM master.dbo.spt_values
WHERE Type = 'P'
AND Number BETWEEN 0 AND DATEDIFF(MONTH, @minDt, @maxDt)
) AS b
OUTER APPLY (
SELECT TOP(1) s.Amt
FROM @Sample AS s
WHERE s.ID = a.ID
AND s.Dt <= b.Dt
ORDER BY s.Dt DESC
) AS f
ORDER BY a.ID,
b.Dt;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

dan1982
Starting Member

2 Posts

Posted - 2014-06-25 : 09:12:56

Thanks all for the quick response. These worked beautifully.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-27 : 09:25:15

They are not producing the same result, so you probably should be a little worried.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources