Entering dates in blank rows - 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

General SQL Server Forums

New to SQL Server Programming

Entering dates in blank rows

Author

Topic

mzrax
Starting Member

2 Posts

Posted - 2007-05-09 : 09:56:42

I'm hoping that someone can help. This is my first time posting and fortunately I can normally find what I need but this time I am stumped.

I have a query that produces a date range. The problem is I want to insert or at lease display the dates between even if they don't show up in table. Here's the problem.

My intial query is
select date_of_call
from call_data
where date_of_call >= '2001-09-01' and date_of_call <= '2001-09-15'
order by date_of_call

Results:
2001-09-03 00:00:00.000
2001-09-07 00:00:00.000
2001-09-10 00:00:00.000
2001-09-13 00:00:00.000
2001-09-13 00:00:00.000
2001-09-13 00:00:00.000
2001-09-13 00:00:00.000
2001-09-13 00:00:00.000
2001-09-14 00:00:00.000

When I do a group by and count it looks like this:
select date_of_call, count(date_of_call) as Count_Date_of_Call
from call_data
where date_of_call >= '2001-09-01' and date_of_call <= '2001-09-15'
group by date_of_call
order by date_of_call

Date_Of_Call, Count_Date_of_Call
2001-09-03 00:00:00.000 1
2001-09-07 00:00:00.000 1
2001-09-10 00:00:00.000 1
2001-09-13 00:00:00.000 5
2001-09-14 00:00:00.000 1

If you notice out of 15 days it only shows 5 days. I am wondering how I can insert the days missing and insert either null or 0 values in the count column so it would look something like this:

Date_Of_Call, Count_Date_of_Call
2001-09-01 00:00:00.000 0
2001-09-02 00:00:00.000 0
2001-09-03 00:00:00.000 1
2001-09-04 00:00:00.000 0
2001-09-05 00:00:00.000 0
2001-09-06 00:00:00.000 0
2001-09-07 00:00:00.000 1
2001-09-08 00:00:00.000 0
2001-09-09 00:00:00.000 0
2001-09-10 00:00:00.000 1
2001-09-11 00:00:00.000 0
2001-09-12 00:00:00.000 0
2001-09-13 00:00:00.000 5
2001-09-14 00:00:00.000 1
2001-09-15 00:00:00.000 0

Any help would be much appreciates.

X002548
Not Just a Number

15586 Posts

Posted - 2007-05-09 : 10:45:48

You need an additional table that contains all dates, then left join from that to the other table...let's see, I have something like that somewhere...or maybe just a numbers table

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-09 : 10:55:06

Find the f_Table_Date function here:[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519&SearchTerms=f_table_date[/url]

Select d.[Date], count(d.[Date]) as count_date_of_call
From f_Table_Date('20010901', '20010915') d LEFT JOIN call_data c
on d.[Date] = c.date_of_call
Group by d.[Date]
order by 1

Edit: Missed Group By.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-05-09 : 10:55:56

If you are talking about small date ranges you could build a temp table and use that in your queries, but it would probably be agood idea to just create a Date table for use anywhere. For example:

-- Setup
DECLARE @Date TABLE (Date DATETIME PRIMARY KEY)
DECLARE @CurrentDate DATETIME
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

SET @StartDate = '2001-09-01'
SET @EndDate = '2001-09-15'

-- Do Validation (not shown)

SET @CurrentDate = @StartDate
-- Load Date table
WHILE @CurrentDate <= @EndDate
BEGIN
	INSERT @Date SELECT @CurrentDate
	SET @CurrentDate = @CurrentDate + 1
END

-- Query
SELECT 
	date_of_call, 
	count(date_of_call) as Count_Date_of_Call
FROM 
	call_data
RIGHT OUTER JOIN
	@Date dt
	ON call_data.date_of_call = dt.Date
where 
	date_of_call >= '2001-09-01' 
	and date_of_call <= '2001-09-15'
group by 
	date_of_call
order by 
	date_of_call

-Ryan

EDIT: Incorrect join type.

mzrax
Starting Member

2 Posts

Posted - 2007-05-09 : 23:04:33

Hey thanks

Especially for the speedy response. I've done the f_table_date option and it worked awesome.

I had a brain fart when this problem came up.

Hey Lamprey

I'm going to try your way also.

Thanks again to all

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-10 : 00:54:26

declare @tt table (dt datetime)
insert @tt
select '2001-09-03 00:00:00.000' union all
select '2001-09-07 12:56:00.000' union all
select '2001-09-10 00:00:00.000' union all
select '2001-09-13 00:00:00.000' union all
select '2001-09-13 00:00:00.000' union all
select '2001-09-13 12:56:00.000' union all
select '2001-09-13 00:00:00.000' union all
select '2001-09-13 00:00:00.000' union all
select '2001-09-14 00:00:00.000'

Select Max(Case when a.datecol = Dateadd(day,datediff(day,0,t.dt),0) then t.dt else a.datecol end) as 'Date',
Sum(Case when a.datecol = Dateadd(day,datediff(day,0,t.dt),0) then 1 else 0 end) as [Count] from
(Select dateadd(d, number,'2001-09-01') as datecol
from master..spt_values where
number between 0 and 14 and name is null) as a left outer join @tt t on
a.datecol = Dateadd(day,datediff(day,0,t.dt),0) group by a.datecol

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources