Fill in missing rows/gap in data

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)

Fill in missing rows/gap in data

Author

Topic

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2010-07-28 : 18:54:12

Hi all -

I have a situation where some of the data received has some gaps. I am trying to figure out a way to insert the "missing" data back into my table.

I have been playing around with CTEs, which seems like the best wa to go, but I have been unable to figure it out so far.

First, some sample data:


 CustomerCode   Active   CSR_Request     ActivityDate
 C42000           1          0               2010-05-07
 C42000           0          1               2010-06-04

The activity date is a week ending date, and I need to fill in the missing data between the dates and use the previous values for the Active and CSR_Request.

So, for the above example, I would need to create 3 rows to insert for 2010-05-14, 2010-05-21 and 2010-05-28. There are also some cases where only one week is missing.

If anyone has any suggestions or pointers to other posts/articles, that would be most welcome.

thanks - will

parody
Posting Yak Master

111 Posts

Posted - 2010-07-29 : 05:22:26

use a CTE to generate your date list then join onto it to fill in the gaps.

You could do it all in the CTE too though.

CREATE TABLE #ATable
(
CustomerCode char(6)
,Active bit
,CSR_Request bit
,ActivityDate date
)

INSERT INTO #ATable
SELECT 'C42000','1','0','20100507'
UNION
SELECT 'C42000','0','1','20100604'
UNION
SELECT 'C42001','1','0','20100507'
UNION
SELECT 'C42001','0','1','20100604'

WITH CTE AS
(
SELECT
MIN(ActivityDate) AS ActivityDate
,MAX(ActivityDate) AS MaxActivityDate
FROM #ATable
UNION ALL
SELECT
dateadd(dd,7,ActivityDate)
,MaxActivityDate
FROM CTE
WHERE dateadd(dd,7,ActivityDate) < MaxActivityDate
)

SELECT
a.CustomerCode
,a.Active
,a.CSR_Request
,ISNULL(b.ActivityDate,a.ActivityDate)
FROM #ATable a
LEFT JOIN CTE b
ON a.ActivityDate <= b.ActivityDate
ORDER BY a.CustomerCode,a.ActivityDate

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2010-07-29 : 11:17:26

Thanks. I had the Min and Max stuff, but had multiple CTEs to get the missing dates and it was too complicated and not working.

Your solution works great and is very straightforward.

Thanks so much!

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2010-07-29 : 14:24:48

Hi again -
Actually, this doesn't quite work for me. I'll continue to work on it and post my results when I can it to work.

Thanks - will

parody
Posting Yak Master

111 Posts

Posted - 2010-07-30 : 04:06:23

whats it doing that you dont want, or not doing that you do want?

PavanKK
Starting Member

32 Posts

Posted - 2010-07-30 : 04:36:21

Hi dhw,

try this. let me know if anything is missing

-------------
DECLARE @Tab TABLE
(
CustomerCode VARCHAR(10),
Active BIT,
CSR_Request BIT,
ActivityDate DATETIME
)

INSERT INTO @Tab VALUES
('C42000',1,0,'2010-05-07'),
('C42000',1,1,'2010-05-21'),
('C42000',0,1,'2010-06-04'),
('C43000',1,0,'2010-04-30'),
('C43000',1,1,'2010-05-07'),
('C43000',0,1,'2010-06-04')

SELECT * FROM @Tab ORDER BY CustomerCode,ActivityDate

;WITH cteAll AS
(
SELECT CustomerCode,MIN(ActivityDate) ActivityDate,MAX(ActivityDate) MaxActivityDate
FROM @Tab
GROUP BY CustomerCode

UNION ALL

SELECT C.CustomerCode,DATEADD(DD,7,C.ActivityDate),C.MaxActivityDate
FROM cteAll C
WHERE DATEADD(DD,7,C.ActivityDate) < C.MaxActivityDate
),

CteMissing AS
(
SELECT C.CustomerCode,T.Active,T.CSR_Request,C.ActivityDate,DATEDIFF(WW,T.ActivityDate,C.ActivityDate) Diff
FROM cteAll C
CROSS JOIN @Tab T
WHERE C.CustomerCode = T.CustomerCode
AND DATEDIFF(WW,T.ActivityDate,C.ActivityDate) > 0
)

INSERT INTO @Tab
SELECT C.CustomerCode,C.Active,C.CSR_Request,C.ActivityDate
FROM CteMissing C
WHERE NOT EXISTS ( SELECT 1 FROM @Tab WHERE CustomerCode = C.CustomerCode AND ActivityDate = C.ActivityDate )
AND C.Diff = ( SELECT MIN(I.Diff) FROM CteMissing I WHERE C.CustomerCode = I.CustomerCode AND C.ActivityDate = I.ActivityDate )
ORDER BY CustomerCode

SELECT * FROM @Tab ORDER BY CustomerCode,ActivityDate
-----------

KK :)

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2010-07-30 : 13:06:44

KK -

Yes, your solution does what I want. I tested it with several scenarios and, so far, it provides the missing data the way that I needed. I ended up creating something more complex (about 4 or 5 CTEs) that used a NOT EXISTS check like you have demonstrated. Otherwise, there were times when I was creating rows that already existed. I think that when I have some time today, I'll refactor my code, using yours as a model.

Thanks for the help.
- will

Celko
Starting Member

23 Posts

Posted - 2010-07-30 : 19:32:54

quote:
Originally posted by dhw

Hi all -

I have a situation where some of the data received has some gaps. I am trying to figure out a way to insert the "missing" data back into my table.

I hope you have a calendar table in your schema. This will let you find the missing dates and pick the most previous values for insertion. Do you want to see details or is that enough?

Author of
SQL for Smarties
Thinking in Sets
SQL Puzzles and Answers
Analytics and OLAP in SQL
SQL Programming Style
Standards in SQL
Data & Databases
Trees & Hierarchies in SQL

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2010-07-31 : 00:16:36

quote:
Originally posted by Celko
I hope you have a calendar table in your schema. This will let you find the missing dates and pick the most previous values for insertion. Do you want to see details or is that enough?

Yes, I do use a calendar table. Thanks for checking.

parody
Posting Yak Master

111 Posts

Posted - 2010-08-02 : 09:25:35

OK I see where mine was wrong. Simple to fix, either a second CTE to remove the dates from the CTE where they already exist in the source, or manage a flag in the first CTE, or add an additional join (shortest and simplest):

SELECT
a.CustomerCode
,a.Active
,a.CSR_Request
,ISNULL(b.ActivityDate,a.ActivityDate)
FROM #ATable a
LEFT JOIN CTE b
ON a.ActivityDate <= b.ActivityDate
AND NOT EXISTS (SELECT 1 FROM #ATable c WHERE c.ActivityDate = b.ActivityDate)
ORDER BY a.CustomerCode,a.ActivityDate

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2010-08-02 : 14:57:39

quote:
Originally posted by parody

OK I see where mine was wrong. Simple to fix, either a second CTE to remove the dates from the CTE where they already exist in the source, or manage a flag in the first CTE, or add an additional join (shortest and simplest):

Yeah, that is it. I had to remove the rows/dates that exist.

Thanks for the continued follow-up and help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources