CODEWITHSUNDEEP
sql-serverdatetimerows
user41829
user41829

Reputation: 105

SQL combine rows for date table

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)

Upvotes: 1

Views: 81

Answers (2)

Shawn Beddes
Shawn Beddes

Reputation: 11

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.

Upvotes: 1

Steve Ford
Steve Ford

Reputation: 7753

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)

Upvotes: 0

Related Questions