Tricky SQL? - 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 2000 Forums

SQL Server Development (2000)

Tricky SQL?

Author

Topic

Psiclonik
Starting Member

2 Posts

Posted - 2003-06-16 : 14:46:36

Folks,

I have a query that is stumping me. I have some data that looks like the following:

event_id, eventstart ( date) , eventend(date)

I need to design a query that can return a row for each day that an event occurs on for the current month with a count of the total number of events for that day. The eventstart and eventend are ranges that an event occurs. If the event occurs on one day the start and end dates are the same.

I would prefer to do this in a single query if possible.

Thanks,
Christian

nr
SQLTeam MVY

12543 Posts

Posted - 2003-06-16 : 15:24:44

What do you mean by each day that an event occurs?
If an event spans 3 days is it included in the count for all 3 days, just when it finishes or just when it starts.

If all 3 days then you will need a table which includes the days spanned by events (which you can build in the query). If you just want the start or end dates or both then it's easier.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Psiclonik
Starting Member

2 Posts

Posted - 2003-06-16 : 15:31:53

I want all days that the event would cover.
if and event ranged 3 days, each of those days would up the count by one for the appropriate day.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-06-16 : 16:27:30

Noticed that you just want the current month but that's easy to change.
In fact could create that as a derived table and do it all in one query.
something like
create table #a (dte datetime)
declare @dte datetime ,@maxdte datetime
select @dte = min(eventstart) from tbl
select @dte = min(eventend) from tbl
while @dte < @maxdte
begin
insert #a select @dte
select @dte = dateadd(dd,1,@dte)
while @dte < @maxdte not exists (select * from tbl where @dte between eventstart and eventend)
select @dte = dateadd(dd,1,@dte)
end
then

select #a.dte ,
(select count(*) from tbl where @dte between eventstart and eventend)
from #a

or

select #a.dte ,
count(*)
from #a, tbl
where #a.dte between eventstart and eventend

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-06-16 : 16:43:21

For current month something like (assuming you don't have future events).

select Days.dte ,
count(*)
from
(select dte = dateadd(dd,i1.i + i2.i + i3.i + i4.i + i5.i,convert(varchar(6),getdate(),112)+ '01')
from
(select i = 0 union select 1) i1 ,
(select i = 0 union select 2) i2 ,
(select i = 0 union select 4) i3 ,
(select i = 0 union select 8) i4 ,
(select i = 0 union select 16) i5
) Days ,tbl
where Days.dte between eventstart and eventend
order by dte

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-06-16 : 16:45:30

or, use a tally table (search this forum for more info).

it is just a table of numbers from 1- whatever, usually 1000 or so.

you create it once and keep it there for problems like this.

thus, if you have a table called "Numbers" with a column called Number with values from 1 - 1000:

first, look at this:

select eventID,
dateadd(d,Number - 1, eventstart) as Day
from
Yourtable
cross join
Numbers N
WHERE
N.Number <= DateDiff(d,eventstart, eventEnd) + 1

then, you take that and get your answer:

select day, count(*)
from
(above SQL) a
group by day

warning: not tested, you may need to tweak it a little to get the counts and the days exactly right.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources