Filling in gaps in data - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Filling in gaps in data

Author

Topic

knox203
Starting Member

31 Posts

Posted - 2009-06-01 : 13:58:28

Hey everyone, first let me start out by saying that I've searched the forum quite extensively and have found solutions similar to what I need... but not enough to solve my issue. So I figured I'd start a new thread in hopes of finding a better solution... here we go.

I've got a simple query that pulls a few columns of data. I'm pulling the amount of orders placed by service type on a per hour basis. Here's the query, followed by the result set:


SELECT	COUNT(O.OrderNumber) AS OrderCount, datepart(year, O.CreateDate) AS 'Year', datepart(month, O.CreateDate) AS 'Month',
		datepart(day, O.CreateDate) AS 'Day', datepart(hour, O.CreateDate) AS 'Hour', O.Service
FROM	dbo.OrderMain AS O
WHERE	(O.Service IN ('PR1H', 'PR2H', 'PR45m', 'PR4H', 'Rush_C', '1_Hour_C', '3Hour_C')) AND (O.CreateDate >= '03/29/09') AND (O.CreateDate <= '03/30/09')
GROUP BY	O.Service, datepart(year, O.CreateDate), datepart(month, O.CreateDate), datepart(day, O.CreateDate), datepart(hour, O.CreateDate)
ORDER BY	O.Service, datepart(year, O.CreateDate), datepart(month, O.CreateDate), datepart(day, O.CreateDate), datepart(hour, O.CreateDate)

Order Count - Year - Month - Day - Hour - Service Type

2	2009	3	30	7	1_Hour_C  
3	2009	3	30	9	1_Hour_C  
3	2009	3	30	10	1_Hour_C  
2	2009	3	30	11	1_Hour_C  
1	2009	3	30	13	1_Hour_C  
1	2009	3	30	18	1_Hour_C  
1	2009	3	30	23	1_Hour_C  
6	2009	3	30	15	3Hour_C   
10	2009	3	30	16	3Hour_C   
2	2009	3	30	17	3Hour_C   
1	2009	3	30	18	3Hour_C   
1	2009	3	29	10	PR1H      
1	2009	3	30	15	PR1H      
3	2009	3	30	16	PR1H      
20	2009	3	30	15	PR2H      
26	2009	3	30	16	PR2H      
2	2009	3	30	17	PR2H      
2	2009	3	30	12	PR4H      
1	2009	3	30	13	PR4H      
1	2009	3	30	16	PR4H      
1	2009	3	29	10	Rush_C    
3	2009	3	29	12	Rush_C    
1	2009	3	29	14	Rush_C    
1	2009	3	29	17	Rush_C

As you can see, there are gaps between the hours for each day. I would like to fill this result with the missing hours with a '0' order count. I've spent some time getting this solved using PHP, but it's been taking me longer than I had hoped, so I figured there's got to be a way using just SQL.

Thanks for your time,
- Adam

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-01 : 14:12:28

[code]
;With Hour_CTE(HourVal)
AS
(SELECT 1
UNION ALL
SELECT HourVal + 1
FROM Hour_CTE
WHERE HourVal + 1<=24
)

SELECT DISTINCT datepart(year, O.CreateDate) AS 'Year', datepart(month, O.CreateDate) AS 'Month',
datepart(day, O.CreateDate) AS 'Day',O.Service,h.HourVal INTO #Calendar
FROM dbo.OrderMain AS O
CROSS JOIN Hour_CTE h
WHERE (O.Service IN ('PR1H', 'PR2H', 'PR45m', 'PR4H', 'Rush_C', '1_Hour_C', '3Hour_C')) AND (O.CreateDate >= '03/29/09') AND (O.CreateDate <= '03/30/09')

SELECT c.[Year],c.[Month],c.[Day],c.HourVal,COALESCE(d.OrderCount,0) AS OrderCount
FROM #Calendar c
LEFT JOIN
(SELECT COUNT(O.OrderNumber) AS OrderCount, datepart(year, O.CreateDate) AS 'Year', datepart(month, O.CreateDate) AS 'Month',
datepart(day, O.CreateDate) AS 'Day', datepart(hour, O.CreateDate) AS 'Hour', O.Service
FROM dbo.OrderMain AS O
WHERE (O.Service IN ('PR1H', 'PR2H', 'PR45m', 'PR4H', 'Rush_C', '1_Hour_C', '3Hour_C')) AND (O.CreateDate >= '03/29/09') AND (O.CreateDate <= '03/30/09')
GROUP BY O.Service, datepart(year, O.CreateDate), datepart(month, O.CreateDate), datepart(day, O.CreateDate), datepart(hour, O.CreateDate)
)d
ON d.Service=c.Service
AND d.[Year]=c.[Year]
AND d.[Month]=c.[Month]
AND d.[Day]=c.[Day]
AND d.Hour=c.HourVal
ORDER BY c.Service, c.[year], c.[month], c.[day], c.HourVal
[/code]

knox203
Starting Member

31 Posts

Posted - 2009-06-01 : 15:50:04

Thanks very much visakh for your help, though I am a little confused with your query, maybe you can help me understand it better?

What is "Hour_CTE" and how does it relate to my original query? Also, it looks like you posted 3 separate queries... do I run these separately, or is it one big query? I guess I just don't see how they would be ran together.

Again, thanks very much for your assistance, I hope to hear back from you!

- Adam

knox203
Starting Member

31 Posts

Posted - 2009-06-01 : 16:08:06

You know what, I should have just copy/pasted it before I spoke! It seems like it ran okay! There is one issue now... I tried running it again and I got an error that says: "There is already an object named '#Calendar' in the database."

Will this be creating a table in the database? If so, this is a database that is crucial that we do not manually create any data into. It is a database created by one of our software vendors and they specifically state to not input and data manually.

Thanks for your time!

kira
Starting Member

17 Posts

Posted - 2009-06-01 : 17:33:26

#Calendar is a temporary table and the table will only exist in current scope. so nothing serious. And you can always drop table #calendar at the end of your stored procedure.

knox203
Starting Member

31 Posts

Posted - 2009-06-01 : 17:49:10

Thanks for the clarification, Kira!

knox203
Starting Member

31 Posts

Posted - 2009-06-01 : 17:55:43

Oh my goodness visakh, your query is wonderful!! I am sorry about all the questions... I am just learning SQL so please bear with me. I have one more question... with your query, it's returning the hours as 1-24. How can I change it so it returns the hours in a 0-23 format?

Thank you!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-02 : 12:39:45

quote:
Originally posted by knox203

Oh my goodness visakh, your query is wonderful!! I am sorry about all the questions... I am just learning SQL so please bear with me. I have one more question... with your query, it's returning the hours as 1-24. How can I change it so it returns the hours in a 0-23 format?

Thank you!

welcome

change query like this to get 0-23


;With Hour_CTE(HourVal)
AS
(SELECT 0
UNION ALL
SELECT HourVal + 1
FROM Hour_CTE
WHERE HourVal + 1<=23
)

SELECT DISTINCT datepart(year, O.CreateDate) AS 'Year', datepart(month, O.CreateDate) AS 'Month',
		datepart(day, O.CreateDate) AS 'Day',O.Service,h.HourVal INTO #Calendar
FROM dbo.OrderMain AS O
CROSS JOIN Hour_CTE h
WHERE	(O.Service IN ('PR1H', 'PR2H', 'PR45m', 'PR4H', 'Rush_C', '1_Hour_C', '3Hour_C')) AND (O.CreateDate >= '03/29/09') AND (O.CreateDate <= '03/30/09')

SELECT c.[Year],c.[Month],c.[Day],c.HourVal,COALESCE(d.OrderCount,0) AS OrderCount
FROM #Calendar c
LEFT JOIN 
(SELECT	COUNT(O.OrderNumber) AS OrderCount, datepart(year, O.CreateDate) AS 'Year', datepart(month, O.CreateDate) AS 'Month',
		datepart(day, O.CreateDate) AS 'Day', datepart(hour, O.CreateDate) AS 'Hour', O.Service
FROM	dbo.OrderMain AS O
WHERE	(O.Service IN ('PR1H', 'PR2H', 'PR45m', 'PR4H', 'Rush_C', '1_Hour_C', '3Hour_C')) AND (O.CreateDate >= '03/29/09') AND (O.CreateDate <= '03/30/09')
GROUP BY	O.Service, datepart(year, O.CreateDate), datepart(month, O.CreateDate), datepart(day, O.CreateDate), datepart(hour, O.CreateDate)   
)d
ON d.Service=c.Service
AND d.[Year]=c.[Year]
AND d.[Month]=c.[Month]
AND d.[Day]=c.[Day]
AND d.Hour=c.HourVal
ORDER BY	c.Service, c.[year], c.[month], c.[day], c.HourVal

knox203
Starting Member

31 Posts

Posted - 2009-06-02 : 20:43:53

That worked great! Can I bug you one more time? Should be the last time!

Is it possible to make that query fill in missing days as well? I hope that wouldn't be too difficult.
I'm fine with it either way, I just appreciate your help thus far.

Thanks!
- Adam

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-03 : 13:27:57

sorry didnt get that. do you mean days missing from dbo.OrderMain?

knox203
Starting Member

31 Posts

Posted - 2009-06-03 : 13:50:52

Sorry, that's correct. Just like how there's missing hours, in certain spots there will be a gap between days where nothing was recorded. I'd like to fill in those missing days if possible.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-03 : 14:14:56

do you have a calendar table present in your db?

knox203
Starting Member

31 Posts

Posted - 2009-06-03 : 14:31:05

visakh, I just created a calendar table called "DateLookup" with a year range of 1900-2100 (including all other necessary information). Does this help?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-03 : 14:48:19

[code]
;With Hour_CTE(HourVal)
AS
(SELECT 0
UNION ALL
SELECT HourVal + 1
FROM Hour_CTE
WHERE HourVal + 1<=23
)

SELECT DISTINCT O.Year, O.Month,
c.Day,O.Service,h.HourVal INTO #Temp
FROM
(
SELECT DISTINCT datepart(year, CreateDate) AS 'Year', datepart(month, CreateDate) AS 'Month',Service
FROM dbo.OrderMain
WHERE (Service IN ('PR1H', 'PR2H', 'PR45m', 'PR4H', 'Rush_C', '1_Hour_C', '3Hour_C')) AND (CreateDate >= '03/29/09') AND (CreateDate <= '03/30/09')
)O
INNER JOIN YourCalendarTable c
ON O.Year=c.Year
AND O.Month=c.Month
CROSS JOIN Hour_CTE h

SELECT c.[Year],c.[Month],c.[Day],c.HourVal,COALESCE(d.OrderCount,0) AS OrderCount
FROM #Temp c
LEFT JOIN
(SELECT COUNT(O.OrderNumber) AS OrderCount, datepart(year, O.CreateDate) AS 'Year', datepart(month, O.CreateDate) AS 'Month',
datepart(day, O.CreateDate) AS 'Day', datepart(hour, O.CreateDate) AS 'Hour', O.Service
FROM dbo.OrderMain AS O
WHERE (O.Service IN ('PR1H', 'PR2H', 'PR45m', 'PR4H', 'Rush_C', '1_Hour_C', '3Hour_C')) AND (O.CreateDate >= '03/29/09') AND (O.CreateDate <= '03/30/09')
GROUP BY O.Service, datepart(year, O.CreateDate), datepart(month, O.CreateDate), datepart(day, O.CreateDate), datepart(hour, O.CreateDate)
)d
ON d.Service=c.Service
AND d.[Year]=c.[Year]
AND d.[Month]=c.[Month]
AND d.[Day]=c.[Day]
AND d.Hour=c.HourVal
ORDER BY c.Service, c.[year], c.[month], c.[day], c.HourVal
[/code]

knox203
Starting Member

31 Posts

Posted - 2009-06-03 : 15:28:26

Once again visakh, you come to the rescue! That query works great, does everything I was hoping it would do. Also, I learned a great deal from this experience, I can't thank you enough :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-04 : 13:08:48

No problem...you're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources