SQL combine rows for date table

Question

I need to make a date table to use for a search criteria for a query. I'm basically trying to make a table that gets the last Sunday of the previous year, to the last Saturday of the current year. I also need to combine the rows.

Example:

"1", "12/27/2015", "1/2/2016"
"2", "1/3/2016", "1/10/2016"
...

Here is my current code that is getting all Saturdays and Sundays of the current year. Can anyone suggest an easy approach to this?

DECLARE 
    @Year AS INT,
    @FirstDateOfYear AS DATETIME,
    @LastDateOfYear AS DATETIME


SELECT @year = 2016
SELECT @FirstDateOfYear = DATEADD(yyyy, @Year - 1900, 0)
SELECT @LastDateOfYear = DATEADD(yyyy, @Year - 1900 + 1, 0);

WITH cte AS (
    SELECT 
        1 AS DayID,
        @FirstDateOfYear AS FromDate,
        DATENAME(dw, @FirstDateOfYear) AS Dayname

    UNION ALL
    SELECT 
        cte.DayID + 1 AS DayID,
        DATEADD(d, 1 ,cte.FromDate),
        DATENAME(dw, DATEADD(d, 1 ,cte.FromDate)) AS Dayname
    FROM cte
    WHERE DATEADD(d,1,cte.FromDate) < @LastDateOfYear
)

SELECT FromDate, Dayname
FROM CTE
WHERE DayName IN ('Saturday', 'Sunday')
OPTION (MaxRecursion 370)

Use a Tally (or Numbers) table: http://www.sqlservercentral.com/articles/T-SQL/62867/ — Pieter Geerkens, Jan 19 '16 at 16:45
Not only is the tally table a much better option you should probably also read this article about using a recursive cte like this for counting. It is the same thing as a loop when the query actually executes. http://www.sqlservercentral.com/articles/T-SQL/74118/ — Sean Lange, Jan 19 '16 at 16:49
Is it possible to create a temp tally table? I can't seem to find anything on using tally tables as a temporary table — user41829, Jan 19 '16 at 17:01
Why not just create a permanent numbers table, once, instead of a temporary one every time this query runs? See Generating a set without loops [part 1](http://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1), [part 2](http://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-2), [part 3](http://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-3) to show why a permanent Numbers (or Calendar) table is best and a recursive CTE is by far the worst. — Aaron Bertrand, Jan 19 '16 at 17:09
Well since the date ranges will constantly change every time there is a new query its going to be running every time regardless. — user41829, Jan 19 '16 at 17:16
@user41829 did any of the answers help you? If so accept one of them. — Steve Ford, Feb 12 '16 at 14:56

score 1 · Answer 1 · answered Jan 19 '16 at 18:58

Depending on what your use case for this you may want to consider creating a Date Dimension or Calendar Table. This is simply a table that has every day between a set number of years with information about each of those days. For instance the table will probably key off the date and have the day of week, month, year, etc.

Whenever I create these tables I usually fill it with every day between the years 2010 and 2100. The table in this instance only has 32000 records and takes up 3MB of space on disk.

See this post for information on how to create a date table and for an example of some information you may want in it:

https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/

Once the table is created and populated you can simply query the table asking for any day in the year for the days of week in question.

score 0 · Answer 2 · answered Jan 19 '16 at 17:37

How about this utilising DATEFIRST to set the first day of the week to Saturday then using this fact to get the appropriate number of days to add or subtract:

SET DATEFIRST 6
;with nums
AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY x.num) as num
    FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11)) as x(num)
    CROSS APPLY (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11)) as y(num)
    CROSS APPLY (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11)) as z(num)
),
MinDate
AS
(
    select DATEADD(d, 2+ -1*datepart(dw,dateadd(DD, -1, dateadd(YY,datediff(yy,0,getdate()),0))), dateadd(DD, -1, dateadd(YY,datediff(yy,0,getdate()),0))) d
),
MaxDate
As
(
    select DATEADD(d,  -1*datepart(dw,dateadd(DD, -1, dateadd(YY,datediff(yy,0,getdate()),1))), dateadd(DD, -1, dateadd(YY,1+datediff(yy,0,getdate()),0))) d
)
SELECT DATEADD(d, num-1, MinDate.d) dat
FROM nums
cross JOIN MinDate
WHERE DATEADD(d, num-1, MinDate.d) <= (SELECT TOP 1 d FROM MaxDate)

SQL combine rows for date table

2 Answers2