Simple date query question? - 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)

Simple date query question?

Author

Topic

danielhai
Yak Posting Veteran

50 Posts

Posted - 2002-09-23 : 17:52:53

I have events in the Database with a StartDate and an EndDate, how do I go about getting a select with a record for each day between the EndDate and StartDate?

declare @TempTable table (
ID int identity not null,
StartDt smalldatetime not null,
EndDt smalldatetime not null
)

insert into @TempTable select '9/1/02','9/5/02'

The select list i want would look like:

ID EventDt
-- -------
1 9/1/02
1 9/2/02
1 9/3/02
1 9/4/02
1 9/5/02

Edited by - danielhai on 09/23/2002 17:53:17

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-09-23 : 18:08:35

Try this:

http://www.sqlteam.com/item.asp?ItemID=3332

danielhai
Yak Posting Veteran

50 Posts

Posted - 2002-09-23 : 18:36:46

hmmm, tried a bunch of things - don't know how to get a looped dateadd in one query ... is this even possible without a loop of some sort?

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-23 : 19:13:45

you can create a sequence like this

This gives up to 39+12+3 = 54

select i = a.i+b.i+c.i
from
(select i = 0 union select 1 union select 2 union select 3) as a ,
(select i = 0 union select 4 union select 8 union select 12) as b ,
(select i = 0 union select 13 union select 26 union select 39) as c

so your days would be
select dateadd(dd,seq.i,@startdate)
from
(select i = a.i+b.i+c.i
from
(select i = 0 union select 1 union select 2 union select 3) as a ,
(select i = 0 union select 4 union select 8 union select 12) as b ,
(select i = 0 union select 13 union select 26 union select 39) as c
as seq
where seq.i < datediff(dd,@startdate,@enddate)

But you would have to restrict the range to use this or use dynamic sql.
You could use this sort of thing to insert multiple dates into @Table in a loop so you didn't have to do one insert at a time.

==========================================
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.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-09-24 : 04:49:30

quote:

(select i = 0 union select 13 union select 26 union select 39) as c

I think
(select i = 0 union select 16 union select 32 union select 48) as c
would be a slightly less bizarre choice!

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-24 : 04:57:41

Depends on the preceeding subqueries

you can have
select i = a.i+b.i+c.i
from
(select i = 0 union select 1 union select 2 union select 3 union select 4) as a ,
(select i = 0 union select 5 union select 10 union select 15 union select 20) as b ,
(select i = 0 union select 25 union select 50 union select 75 union select 100) as c

to get multiples of 8
select i = a.i+b.i+c.i
from
(select i = 0 union select 1 union select 2 union select 3) as a ,
(select i = 0 union select 4) as b ,
(select i = 0 union select 8 union select 16 union select 24 union select 32) as c
order by i

==========================================
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.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-09-24 : 07:08:25

My point was that what you'd written generates duplicates:


select i, count(*)
from (
    select i = a.i+b.i+c.i 
    from 
    (select i = 0 union select 1 union select 2 union select 3) as a , 
    (select i = 0 union select 4 union select 8 union select 12) as b , 
    (select i = 0 union select 13 union select 26 union select 39) as c
  ) seq
group by i
order by i

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-24 : 07:53:21

oops.

Thanks.

Really should test occasionally.

==========================================
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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources