Returning a record for each day in a range

Question

Post reply

Returning a record for each day in a range

Bill Geake

Ten Centuries

Points: 1352
More actions
March 2, 2005 at 8:20 am

#67073

I have a table of events, each with a start and finish date. I'd like to write a query which will return a row for every day in a range, with the date and count of the events that are live on that day. What I'm struggling with is how to get a record returned on days when there is no event.

Any ideas?
Bill.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

sellertools Old Hand Points: 365 More actions · Answer 1

try something like

select Count(Events), Day(eventDate) From YourTable

Group By Day(EventDate)

Order By Day(EventDate)

Bill Geake Ten Centuries Points: 1352 More actions · Answer 2

I think that would work if there where a record for each day of the event in the table - but there isn't. For instance, at one extreme, if there where no events in my table for the range of dates that I set, I'd want still want a record for each day in the range - just showing that yes, we have no ~~bananas~~ events today.

sellertools Old Hand Points: 365 More actions · Answer 3

How about this then for a quick answer, something similiar should be able to do it for you.

Declare @table table (EventDate int, RecordCount int)

Insert Into @table

select

Day(CLDateofApplication) as Expr1,

IsNULL( Count(*), 0)

From ClientData

Where CLDateOfApplication>=getDate()-30

Group By Day(CLDateofApplication)

Order By Expr1

Declare @MinDate as int

Declare @MaxDate as int

Set @MinDate = (Select Min(EventDate) From @table)

Set @MaxDate = (Select Max(EventDate) From @table)

while @MinDate <@MaxDate

BEGIN

If (select RecordCount From @table Where EventDate = @MinDate) IS NULL

BEGIN

Insert into @Table (EventDate, RecordCount) VALUES (@MinDate, 0)

END

IF @MinDate >= @MaxDate

BREAK

ELSE

Set @MinDate = @MinDate+1

CONTINUE

END

Select *

From @table

Order by EventDate

Dave-3000 SSCertifiable Points: 6186 More actions · Answer 4

Why not create a reference table of dates and use that to join against your event table?

-- @DateRef is a table variable only for this example;

-- declare @DateRef as a base table and load it ahead of time

declare @DateRef TABLE (TheDate smalldatetime PRIMARY KEY CLUSTERED)

declare @Date smalldatetime, @Counter smallint

SET @Counter = 1; SET @Date = CAST('20041231' AS smalldatetime)

-- load 30 days of reference dates; load a year or more for production

WHILE @Counter <= 30

BEGIN

INSERT INTO @DateRef (TheDate) VALUES (DATEADD(dd, @Counter, @Date))

SET @Counter = @Counter + 1

END

-- load sample events

DECLARE @Events TABLE (EventDate smalldatetime)

INSERT INTO @Events VALUES ('20050101')

INSERT INTO @Events VALUES ('20050104')

INSERT INTO @Events VALUES ('20050106')

INSERT INTO @Events VALUES ('20050108')

INSERT INTO @Events VALUES ('20050112')

-- the derived table b is only dates that have events

-- the

SELECT CONVERT(char(10), a.TheDate, 101) AS Date,

Day(a.TheDate) AS [Day],

ISNULL(b.EventCount, 0) AS EventCount

FROM @DateRef AS a

LEFT JOIN (SELECT Count(*) AS EventCount, EventDate FROM @Events

GROUP BY EventDate) AS b ON a.TheDate = b.EventDate

WHERE a.TheDate BETWEEN '20050101' AND '20050131'

Will return this:

Date Day EventCount

-----------------------------

01/01/2005 1 2

01/02/2005 2 0

01/03/2005 3 0

01/04/2005 4 2

01/05/2005 5 0

01/06/2005 6 2

01/07/2005 7 0

01/08/2005 8 2

01/09/2005 9 0

01/10/2005 10 0

01/11/2005 11 0

01/12/2005 12 2

01/13/2005 13 0

01/14/2005 14 0

01/15/2005 15 0

01/16/2005 16 0

01/17/2005 17 0

01/18/2005 18 0

01/19/2005 19 0

01/20/2005 20 0

01/21/2005 21 0

01/22/2005 22 0

01/23/2005 23 0

01/24/2005 24 0

01/25/2005 25 0

01/26/2005 26 0

01/27/2005 27 0

01/28/2005 28 0

01/29/2005 29 0

01/30/2005 30 0

There is no "i" in team, but idiot has two.

Carl Federl One Orange Chip Points: 25384 More actions · Answer 5

Try creating a table (named "sequences"&nbsp with all numbers between 0 and 32767 as detailed below.

Your actual query can then reference this "sequences" table such as:

declare @StartDate datetime
, @EndDate datetime
set @StartDate = '2005-01-01'
set @EndDate = '2005-02-28'
select seq , @StartDate + seq
from dbo.Sequences
where seq between 0 and @EndDate - @StartDate

Here is the SQL to create and populate the "Sequences" table:

set nocount on
set xact_abort on
create table Sequences
( Seq smallint not null
, constraint Sequences_PK primary key (Seq) )
-- Create a new table to turn columns into rows or rows into columns
declare @SmallIntMax integer
, @SeqMax integer
set @SmallIntMax= power(2,15) - 1
-- Populate the Sequences table:
-- zero is also useful.
Insert into Sequences (Seq) values (0)
Insert into Sequences (Seq) values (1)
-- Now repeat the following insert 10 times to get 1024 sequence Sequences
set @SeqMax = 1
while @SeqMax < @SmallIntMax
begin
Insert into Sequences
SELECT NewSeq
FROM (select Seq + @SeqMax + 1 AS NewSeq
from Sequences
&nbsp as S
Order by NewSeq
select @SeqMax = max(Seq) from Sequences
end
go
select 'Largest Seq is ' , max(Seq) from Sequences
go

SQL = Scarcely Qualifies as a Language

sellertools Old Hand Points: 365 More actions · Answer 6

That is a really interesting way of doing that Dave. It seems like it would create more overhead though when used as a temp table like that. Interesting though, because if you had a physical table like that, it could be a handy "Tool" for situations like this that might come up.

Dave-3000 SSCertifiable Points: 6186 More actions · Answer 7

You're right. In the comments for my example I note that I am only using a temp table for purposes of this example.

There is no "i" in team, but idiot has two.

Jeff Moden SSC Guru Points: 1004300 More actions · Answer 8

A fellow on this forum who goes by the name of "Adam Mechanic" would have you create a permanent date table so that you can easily solve these types of date problems (and, many others) using a method similar to Dave's (above) with ease. If you only wanted a date column, all dates from 01/01/1900 (INT = 0) to 01/01/2200 (INT = 109573) would only occupy about a meg of disk space. Obviously, if you started at 01/01/2000, the table would be even smaller but if you do start at 0 or 1, the date table will double up as a "Sequence" or "Talley" table which also has some pretty good uses. Add holidays and a couple of other columns and most of your date problems will quickly vanish.

Adam had previously referred me to the following URL and it's a pretty good place to start...

http://www.aspfaq.com/show.asp?id=2519

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bill Geake Ten Centuries Points: 1352 More actions · Answer 9

Many, many thanks for all these replies. I've struggled on with increasingly nasty loops of dynamic SQL, learning some good stuff along the way and generally having fun, but haven't solved the problem. I really thought I could solve this without a dates table, but the time has come for pragmatism.

I will now head along the permanent table of dates route, and update the forum when I'm done.

Once again, thank you - what a great bunch of people SQLServerCentral members are!

Bill.

Adrienne250 Right there with Babe Points: 737 More actions · Answer 10

Hi Bill -- If you haven't finished yet. I wrote a function that fills in dates:

create function fnDateFill(@startDate datetime, @endDate datetime, @recurrence varchar(10))

returns @dateTable table(selectDate datetime)

as

begin

declare @tempDate datetime

if @startDate > @endDate begin

return -- some kind of error

end

-- set the temp date to the start date

set @tempDate = @startDate

while @tempDate <= @endDate begin

if @recurrence = 'weekday' begin

while datename(dw,@tempDate) = 'Saturday' or datename(dw,@tempDate) = 'Sunday' begin

set @tempDate = dateadd(dd,1,@tempDate)

continue end

insert into @dateTable values(@tempDate)

set @tempDate = dateadd(dd,1,@tempDate) -- add a day

end

if @recurrence = 'daily' begin

insert into @dateTable values(@tempDate)

set @tempDate = dateadd(dd,1,@tempDate)

end

continue end

return

end

--------------------------------

use it like this:

insert into #yourTable values (1, '03/02/05')

insert into #yourTable values (1, '03/10/05')

insert into #yourTable values (1, '03/12/05')

insert into #yourTable values (1, '03/19/05')

insert into #yourTable values (1, '03/30/05')

select cnt = count(event), eventDate

from #yourTable

group by eventDate

union all

select 0, selectDate

from appUtility.dbo.fnDateFill('03/01/05','03/31/05','weekday')

where selectDate not in (select eventDate from #yourTable)

order by eventDate