Selecting all dates from a table within a date ran

Question

I am trying to refactor some code in an ASP.Net website and having a problem with a stored procedure I am writing.

What I want to do is get a date range, then select all data within that range from a table BUT if a date is not present I need to still select a row.

My idea for this as you can see in the code below is to create a temporary table, and populate it with all the dates within my date range, then join this onto the table I am selecting from however this does not work. Am I doing something wrong here? The tempDate column is always null in this join however I have checked the table and it deffinately has data in it.

-- Parameters
DECLARE @DutyDate datetime='2012-01-01 00:00:00'
DECLARE @InstructorID nvarchar(2) = N'29'

DECLARE @datesTBL TABLE (tempDate DATETIME)

-- Variables
DECLARE @StartDate DATETIME 
DECLARE @EndDate DATETIME

SELECT 
    @StartDate =StartDate, 
    @EndDate = EndDate
FROM 
    DutyPeriodTbl 
WHERE 
(StartDate <= @DutyDate) 
AND 
(EndDate >= @DutyDate)


DECLARE @d DATETIME = @StartDate 
WHILE @d<=@EndDate
BEGIN
    INSERT INTO @datesTBL VALUES (CONVERT(DATETIME, @d, 102))
    SET @d=DATEADD(day,1,@d)
END

SELECT 
    dt.tempDate ,
    InstructorID,           EventStart, 
    EventEnd,               cancelled, 
    cancelledInstructor, 
    EventType,              DevName, 
    Room,                   SimLocation, 
    ClassLocation,          Event, 
    Duration,               TrainingDesc, 
    Crew,                   Notes, 
    LastAmended,            InstLastAmended, 
    ChangeAcknowledged,     Type, 
    OtherType,              OtherTypeDesc, 
    CourseType 
FROM 
    OpsInstructorEventsView iv
LEFT OUTER JOIN
    @datesTBL dt 
ON
    CONVERT(DATETIME, iv.EventStart, 102) = CONVERT(DATETIME, dt.tempDate, 102) 
WHERE 
    InstructorID = @InstructorID 
AND 
    EventStart BETWEEN CONVERT(DATETIME, @StartDate, 102) AND CONVERT(DATETIME, @EndDate, 102)
ORDER BY 
    EventStart 

Answer 1

There are several ways of dealing with missing rows, but all are about having another set of data to combine with your current results.

That could be derived from your results, created by a CTE or other process (such as your example), or (my preference) by using a permanent template to join against.

The template in your case could just be a table of dates, like your @datesTBL. The difference being that it's created in advance with, for example, 100 years worth of dates.

Your query may then be similar to your example, but I would try the following...

SELECT 
    dt.tempDate ,
    InstructorID,           EventStart, 
    EventEnd,               cancelled, 
    cancelledInstructor, 
    EventType,              DevName, 
    Room,                   SimLocation, 
    ClassLocation,          Event, 
    Duration,               TrainingDesc, 
    Crew,                   Notes, 
    LastAmended,            InstLastAmended, 
    ChangeAcknowledged,     Type, 
    OtherType,              OtherTypeDesc, 
    CourseType 
FROM 
  @datesTBL dt 
LEFT OUTER JOIN
  OpsInstructorEventsView iv
    ON  iv.EventStart >= dt.tempDate
    AND iv.EventStart <  dt.tempDate + 1
    AND iv.InstructorID = @InstructorID 
WHERE
      dt.tempDate >= @StartDate
  AND dt.tempDate <= @EndDate
ORDER BY
  dt.tempDate,
  iv.EventStart

This puts the calendar template on the LEFT, and so makes many queries easier as you know the calendar's date field is always populated, is always a date only (no time part) value, is in order, is simple to GROUP BY, etc.

Answer 2

Well, idea is the same, but i would write function, that returns table with all dates in period. Look at this:

Create Function [dbo].[Interval]
(
    @DateFrom Date,
    @DateTo Date
)
Returns @tab Table
    (
        MyDate DateTime
    )
As
Begin
    Declare @Days int
    Declare @i int
    Set @Days = DateDiff(Day, @DateFrom, @DateTo)

    Set @i = 0;
    While (@Days > @i)
    Begin
        Insert Into @tab(MyDate)
            Values (DateAdd(Day, @i, @DateTo))
        Set @i = @i + 1
    End
    return
End

And reuse the function whenever you need it..

Select *
From [dbo].[Interval]('2011-01-01', GETDATE())