Filling in the holes in a timeline

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 the holes in a timeline

Author

Topic

tatsky
Starting Member

1 Post

Posted - 2007-07-04 : 14:25:06

I have what seems like a fairly common and straight forward query.

My query returns a count of a particular statistic over a period of time, grouped by a time interval. So basically something like

select month(Date), Year(Date), count(*) from MyTable where Date between @StartDate and @EndDate group by Month(Date), Year(Date)

this will give me something like

Month,Year,Count
1,2007,45
2,2007,87
3,2007,345
5,2007,23
etc

My query also allows me to change the reported period. So instead of grouping by month i can group by week, or day over a period.

However, my problem is if there are no results for a particular day, week or month then nothing is shown in the results. As in the example above month 4, April, has returned no results. What I want is to show 0 rather than nothing at all.

I could solve this by creating a temporary table which contains a number to represent each day, week or month between the @startdate and @enddate and then do a join against this table, and do a coalesce(count(*), 0) or similar but I wondered if there was a more elegant solution to this?

Thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-04 : 14:30:42

you have to left join to a datetime table or function.
you can find it here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Kristen
Test

22859 Posts

Posted - 2007-07-05 : 06:09:19

Or you could perhaps just JOIN a Tally Table

If you don't have a physical table you can generate one on the fly by joining this:


SELECT COUNT(*)
FROM
(
	SELECT	v0.n + v1.n + v2.n + v3.n AS i
	FROM
	(
		SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 
			UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION SELECT 8 
			UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 
			UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15
	) AS v0,
	(
		SELECT 0 n UNION ALL SELECT 16 UNION ALL SELECT 32 UNION ALL SELECT 48 UNION ALL SELECT 64
			UNION ALL SELECT 80 UNION ALL SELECT 96 UNION ALL SELECT 112 UNION SELECT 128 
			UNION ALL SELECT 144 UNION ALL SELECT 160 UNION ALL SELECT 176 UNION ALL SELECT 192
			UNION ALL SELECT 208 UNION ALL SELECT 224 UNION ALL SELECT 240
	) AS v1,
	(
		SELECT 0 n UNION ALL SELECT 256 UNION ALL SELECT 512 UNION ALL SELECT 768 
			UNION ALL SELECT 1024 UNION ALL SELECT 1280 UNION ALL SELECT 1536 
			UNION ALL SELECT 1792 UNION ALL SELECT 2048 UNION ALL SELECT 2304 
			UNION ALL SELECT 2560 UNION ALL SELECT 2816 UNION ALL SELECT 3072 
			UNION ALL SELECT 3328 UNION ALL SELECT 3584 UNION ALL SELECT 3840
	) AS v2,
	(
		SELECT 0 n UNION ALL SELECT 4096
	) AS v3
) AS w

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources