Generate All Dates in query/view

[dhookom]

I do a small amount of programming/coding in SQL Server (mostly 2005). I have a table of scheduled events and would like to create a query/view of Jan 1 to Jan 31. I need a date to appear even if there are no events scheduled on that date (using a LEFT JOIN).
There seem to be several alternatives for generating all dates:
- permanent table of every possible date
- permanent table of integers and use DateAdd()
- temporary table
- function that returns a table

I have looked at these faqs that offer similar functionality:

faq183-840 Creates a temporary table

faq183-1100 table variable

faq183-5075 function returning table

This is what I came up with but I would like some feedback. I don't think the usage of any of these alternatives would cause performance issues on the SQL Server.

This function returns a table of date/time periods from a start date/time for X number of periods. The interval is set with strInterval so the function can be used to generate years, quarters, months, weeks, days, hours, minutes, and seconds.

CREATE FUNCTION [dbo].[AllDates]
(      @datStart Datetime,
       @intPeriods int,
       @strInterval Char(1)
)
RETURNS @tDates table
(
   DT DateTime
)

AS

Begin
Declare @intI int
Declare @datNew DateTime

Set @intI = 0
While @intI < @intPeriods
  Begin
    Set @datnew = 
      CASE @strInterval
        WHEN 'd' THEN DateAdd(dd, @intI, @datStart)
        WHEN 'w' THEN DateAdd(ww, @intI, @datStart)
        WHEN 'm' THEN DateAdd(mm, @intI, @datStart)
        WHEN 'y' THEN DateAdd(yy, @intI, @datStart)
        WHEN 'q' THEN DateAdd(qq, @intI, @datStart)
        WHEN 'h' THEN DateAdd(hh, @intI, @datStart)
        WHEN 'n' THEN DateAdd(n,  @intI, @datStart)
        WHEN 's' THEN DateAdd(ss, @intI, @datStart)
      END
    INSERT INTO @tDates (DT) VALUES (@datNew)
    Set @intI = @intI + 1
  End
Return
End

Duane
Hook'D on Access
MS Access MVP

 

[RiverGuy]

- permanent table of every possible date

That would be my recommendation. You can also add columns as time goes by for month, day of week, is it a holiday, etc. It will come in useful for more purposes than your example. By the way, you obviously don't want to store every single date--you don't want 1753 and you probably don't want 3099.

 

[markros]

Check this link

http://sqlserver2000.databases.aspf...nsider-using-an-auxiliary-calendar-table.html

PluralSight Learning Library

 

[dhookom]

Great resources and link. Having a dedicated table looks like it can provide some added functionality such as holidays etc which would be very difficult using other methods.

I have generally used a table of dates in Access but SQL Server offers alternatives that might work better.

I think I will keep the function for intervals smaller than a single date.

Duane
Hook'D on Access
MS Access MVP

 

[gmmastros]

There is a "Poor Man's" number table built in to SQL Server. For SQL2000, this table has 256 values (0 to 255). For SQL2005, this table has 2048 values (0 to 2047).

Select Number 
From   master..spt_values 
Where  Type = 'P'

A number/calendar table is better because you can add bit columns for holidays and such. But, in a pinch, the Poor Mans's numbers table can come in handy.

Personally, ever database I work with has a numbers table going from 1 to 1,000,000. Thinking about this, each integer in SQL takes 4 bytes, so this table is approximately 4 megabytes big. Tiny by today's standards.

-George

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

 

[dhookom]

Again, good information. As a matter of fact I had googled "Poor Man's number table" and didn't get any hits ;-)

Duane
Hook'D on Access
MS Access MVP

 

[gmmastros]

In a google window, try this...

sql server "poor mans number table"

The 2nd (of 2) results is this... thread183-1572774

-George

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