T-SQL task - find all dates with specified WeekDays in two dates range

Question

I have quite tough task for me to do in T-SQL (MSSQL 2005). I have a table like this:

WeekDay| SlotTime
------------------
|  1   |   07:00
|  3   |   09:00
|  7   |   14:00
|  1   |   15:00
|  4   |   22:00
|  6   |   08:00

where 1st column is WeekDay number and 2nd column is some Time value.

As parameters for my query I have 2 dates, for example:

StartDate = '2011-07-20'
EndDate = '2011-08-17'

This is a range definition for my data. I have to generate for these range all dates where WeekDay (from table above) happens and add to them SlotTime value. So for example, for above dates range result column should be:

2011-07-20 9:00
2011-07-21 22:00
2011-07-23 8:00
2011-07-24 14:00
2011-07-25 7:00
2011-07-25 15:00
2011-07-27 9:00
2011-07-28 22:00
2011-07-30 8:00
etc.
...

Any idea how to achieve this? Any tips? :) I'm considering this quite impossible without some huge(?) calculations and additional tables...

Edit (maybe this snippet will help) I was playing with this function to use it as part of my calculations but was unable to achieve my goal. Maybe some part of this can be used in final solution...

create function dbo.NthWeekDay(
   @first datetime,   -- First of the month of interest (no time part)
   @nth tinyint,      -- Which of them - 1st, 2nd, etc.
   @dow tinyint       -- Day of week we want
) returns datetime as begin
-- Note: Returns a date in a later month if @nth is too large
  declare @result datetime
  set @result = @first + 7*(@nth-1)
  return @result + (7 + @dow - datepart(weekday,@result))%7
end
go

SET DATEFORMAT ymd
SET DATEFIRST 1

select dbo.NthWeekDay('2011-07-20',1,1) as D

go

drop function NthWeekDay 

Answer 1

You can use what's called a numbers table. Just create a table with as many rows as there are days between your dates, number them sequentially.

Here's a pretty slick way to create a numbers table in SQL 2008, may also work in 2005: http://archive.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=NumbersTable

Alternatively, you just create a table with an identity and then insert TOP x rows into it.

From there you can compute the rest

number
1          DateAdd(dd, '2011/07/20', number)      DatePart(dw, DateAdd(dd, '2011/07/20', number))
2          DateAdd(dd, '2011/07/20', number)      DatePart(dw, DateAdd(dd, '2011/07/20', number))
3          DateAdd(dd, '2011/07/20', number)      DatePart(dw, DateAdd(dd, '2011/07/20', number))
4          DateAdd(dd, '2011/07/20', number)      DatePart(dw, DateAdd(dd, '2011/07/20', number))
5          DateAdd(dd, '2011/07/20', number)      DatePart(dw, DateAdd(dd, '2011/07/20', number))
6          DateAdd(dd, '2011/07/20', number)      DatePart(dw, DateAdd(dd, '2011/07/20', number))
7          DateAdd(dd, '2011/07/20', number)      DatePart(dw, DateAdd(dd, '2011/07/20', number))

Join that table to your original results, and then insert the product into your final table.

Query:

SELECT TOP 5000
    IDENTITY( INT, 0, 1 ) AS N
INTO
    Number
FROM
    sys.objects a,
    sys.objects b,
    sys.objects c

SELECT
    N,
    DATEADD(dd, N, '7/20/2011') AS Date,
    DATEPART(dw, DATEADD(dd, N, '7/20/2011')) AS DayofWeek
FROM
    Number
WHERE
    DATEADD(dd, N, '7/20/2011') BETWEEN '7/20/2011'
                                AND     '8/17/2011'

Result:

N           Date                    DayofWeek
----------- ----------------------- -----------
0           2011-07-20 00:00:00.000 4
1           2011-07-21 00:00:00.000 5
2           2011-07-22 00:00:00.000 6
3           2011-07-23 00:00:00.000 7
4           2011-07-24 00:00:00.000 1
5           2011-07-25 00:00:00.000 2
6           2011-07-26 00:00:00.000 3
7           2011-07-27 00:00:00.000 4
8           2011-07-28 00:00:00.000 5
9           2011-07-29 00:00:00.000 6
10          2011-07-30 00:00:00.000 7
11          2011-07-31 00:00:00.000 1
12          2011-08-01 00:00:00.000 2
13          2011-08-02 00:00:00.000 3
14          2011-08-03 00:00:00.000 4
15          2011-08-04 00:00:00.000 5
16          2011-08-05 00:00:00.000 6
17          2011-08-06 00:00:00.000 7
18          2011-08-07 00:00:00.000 1
19          2011-08-08 00:00:00.000 2
20          2011-08-09 00:00:00.000 3
21          2011-08-10 00:00:00.000 4
22          2011-08-11 00:00:00.000 5
23          2011-08-12 00:00:00.000 6
24          2011-08-13 00:00:00.000 7
25          2011-08-14 00:00:00.000 1
26          2011-08-15 00:00:00.000 2
27          2011-08-16 00:00:00.000 3
28          2011-08-17 00:00:00.000 4

Answer 2

This will do the trick

SET DATEFIRST 1 
-- temp table
declare @t table(WeekDay tinyint, SlotTime time)
-- fill table
insert @t values (1, '7:00')
insert @t values (3, '9:00')
insert @t values (7, '14:00')
insert @t values (1, '15:00')
insert @t values (4, '22:00')
insert @t values (6, '8:00')

-- declare interval
declare @startdate datetime
declare @enddate   datetime
set @StartDate = '2011-07-20'
set @EndDate   = '2011-08-17'

;with cte as
(
-- recusive to make timeline
SELECT @StartDate loopday
UNION ALL
SELECT loopday + 1
FROM cte
WHERE loopday  < @EndDate 
), b as
(
-- join timeline with Weekday and add Slottime to timeline
SELECT loopday + t.SlotTime col
FROM cte
JOIN @t t
ON t.WeekDay = datepart(weekday, cte.loopday)
)
SELECT col 
FROM b
ORDER BY 1
OPTION( MAXRECURSION 0)

(Result looks like your output)

Answer 3

I agree with @Lucent Fox that a number table can be very handy here. However, you don't have to create it if your requested ranges can never span more than 5½ years. A system table called master..spt_values, or, more precisely, its subset where type = 'P', can be used as a number table in your query:

WITH datelist AS (
  SELECT
    Date = DATEADD(DAY, number, @StartDate)
  FROM master..spt_values
  WHERE type = 'P'
    AND number BETWEEN 0 AND DATEDIFF(DAY, @StartDate, @EndDate)
)
SELECT
  Timestamp = d.Date + s.SlotTime
FROM datelist d
  INNER JOIN SlotTable s ON s.WeekDay = DATEPART(WEEKDAY, d.Date)
ORDER BY Timestamp