Presents your  
SQL SERVER E-NEWSLETTER for October 21, 2003 

<-------------------------------------------> 
  
SHOWING ALL DATES IN A REPORT ORDERED BY DATE   
  
Database applications almost always include reports ordered by date. 
You may run into problems when some dates are missing from the data source 
and management requires you to include those dates in your report. For 
instance, if the company doesn't sell anything on certain days, those
dates won't show up in the report.   
  
The solution is to create a table containing all the dates within the 
desired range, like this:   
  
CREATE TABLE [dbo].[Dates] (   
    [Date] [datetime] NOT NULL   
) ON [PRIMARY]   
GO   
  
Now you have to populate it. The following stored procedure accepts two 
parameters, indicating the beginning and ending dates:   
  
CREATE PROCEDURE dbo.ap_FillDates   
(   
@StartDate DateTime,   
@StopDate DateTime   
)   
As   
DELETE Dates   
DECLARE @Current DateTime   
SET @Current = @StartDate   
WHILE @Current < @StopDate   
BEGIN   
INSERT Dates VALUES (@Current)   
SET @Current = DateAdd( d, 1, @Current )   
END   
return   
  
Note that the procedure always deletes the entire table before adding a 
new date range. If you prefer to retain all current rows, you have to add 
some error checking, and be sure to test for the existence of the 
beginning and ending dates before executing the WHILE loop. This gets a
little messy since you need to check for both and respond accordingly. 
Oftentimes, I'm only interested in a month or a year, and the INSERTs
happen so quickly that I don't care to preserve the existing rows; however,
your situation may require extra work.   
  
Now you can populate the Dates table quickly and easily by calling the 
stored procedure and passing two dates.   
  
THE FINAL STEP   
  
Now that you have everything you need to generate the report as per your
requirements, OUTER JOIN the Dates table to your table or view of 
interest. That way, dates that don't have any corresponding rows will
still appear in the result set.   
  
Arthur Fuller has been developing database applications for 20 years. 
His experience includes Access ADPs, Microsoft SQL 2000, MySQL, and .NET.
----------------------------------------