SQL 2000 Query concerning dates.

[Djbell]

Hi All

I have a table like the following.

Name||||Hours||||StartDate||||EndDate
Dougie|| 8 ||||01/11/2008|||01/11/2008
Colin ||| 5 ||||03/11/2008|||03/11/2008
Frank || 39 ||||05/11/2008|||09/11/2008

I would like the query to do the following.

Name||||Hours||||StartDate||||EndDate
Dougie|| 8 ||||01/11/2008|||01/11/2008
Colin ||| 5 ||||03/11/2008|||03/11/2008
Frank || 39 ||||05/11/2008|||05/11/2008
Frank || 39 ||||06/11/2008|||06/11/2008
Frank || 39 ||||07/11/2008|||07/11/2008
Frank || 39 ||||08/11/2008|||08/11/2008
Frank || 39 ||||09/11/2008|||09/11/2008

As you can see it has taken the Franks range of dates and created a row for each individual date. Is this possible and if so, any help would be appreciated.

Cheers

Djbell

 

[gmmastros]

I have a numbers table in my database that has 1 column (num) and has 1,000,000 rows. When you think about it, an int column takes 4 bytes, so the numbers table only adds about 4 megs to the size of the database. Actually, it's probably closer to 8 megs because of the index that is also created. If you have a numbers table, then you can easily write a query to do this.

Do you have a numbers table in your database? If not, are you willing to add one?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Now that I think about it a little more, I would suggest that you create a calendar table with one column and rows that span 1/1/1970 to 1/1/2100.

You can create the table with this:

Create Table Cal(CalendarDate DateTime Primary Key Clustered)

Declare @i DateTime
Set @i = '19700101'

While @i < '21000101'
  Begin
    Insert Into Cal Values(@i)
    Set @i = @i + 1
  End

You can expect it to take about a minute to create the table a populate it with data.

Then, your query would be:

Select T.Name, T.Hours, Cal.CalendarDate
From   [!]YourTableName[/!] T
       Inner Join Cal 
         On Cal.CalendarDate Between T.StartDate And T.EndDate

Running the sp_spaceused command on this table, you get:

47482 rows, and 840 KB of space reserved for this table (data + index + unused). That's less than 1 meg, which is a small price to pay. Once you have this table in your database, you will probably find other uses for it.


Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RyanEK]

Hi,

Cool question. You could write a function that returns you dates between a date range. Personally I avoid functions where I can, so if you're using SQL2K5 how about this? I use a common table expression...

declare @temp table (id int, dstart datetime, dend datetime)
insert into @temp (id,dstart,dend) values (1,'2008-11-1','2008-11-1')
insert into @temp (id,dstart,dend) values (2,'2008-11-3','2008-11-3')
insert into @temp (id,dstart,dend) values (3,'2008-11-5','2008-11-9')

declare @dMax datetime;	
select @dMax = max(dend) from @temp
with mycte as
(
	select min(dstart) as DateValue from @temp
	union all
	select DateValue + 1
	from    mycte   
	where   DateValue + 1 <= @dMax
)
select id, d.datevalue, d.datevalue
from @temp as t
join mycte as d on d.datevalue between t.dstart and t.dend
order by id

Ryan

 

[RyanEK]

Ahhh I just read the title its SQL2000 hehe. Oh well...