Week's date range

Transact-SQL
21

Thank you for taking the time to explain that Scott!

22

So basically, are the days represented like this?
0 Monday
1 Tuesday
2 Wednesday
3 Thursday
4 Friday
5 Saturday
6 Sunday

I tried the following but it returned 06/30/2018, instead of 07/07/2018. Can you help me understand how to achieve 07/07/2018?

SELECT DATEADD(DAY, -DATEDIFF(DAY, 5, '2018-07-02') % 7, CAST('2018-07-02' AS date)) AS MyDate

Or the second Saturday of the beginning of the year?

23

The - will back up to the previous Saturday. So to get next Saturday after that, just add 7 days:

SELECT DATEADD(DAY, -DATEDIFF(DAY, 5, '2018-07-07') % 7 + 7, CAST('2018-07-07' AS date)) AS MyDate

Or say, as a generic example, I wanted the third Thursday of July 2018:

SELECT DATEADD(DAY, -DATEDIFF(DAY, 3, '20180707') % 7 + 14, CAST('20180707' AS date)) AS MyDate

This technique is very flexible. The 7th is used because that is the last possible day of the month to be the first of any given day.

1 Like
24

Got it Scott! So when it strips, it is stripping the previous 7 days from the date that is entered. Thank you!

25

This is extremely cool Scott. I used your explanation with Andy and Joe's function and I was able to generate the entire 26 paid period without using a loop.

	DECLARE @My_Date AS DATE = '2018-01-05'
	--DECLARE @Last_Day_Of_Paid_Period AS DATE
	--SET @Last_Day_Of_Paid_Period = DATEADD(DAY, -DATEDIFF(DAY, 5, @My_Date) % 7 + 14, CAST(@My_Date AS DATE)) 
	--SELECT @Last_Day_Of_Paid_Period AS Last_Day_Of_Paid_Period

	SELECT 
		DATEADD(DAY, -DATEDIFF(DAY, 5, @My_Date) % 7 + (14 * T.MyValue), CAST(@My_Date AS DATE)) 
	FROM fnTally(26) T
26

I am getting more familiar with how tally works. I understand in order to set a tally, a function can be created and a number can be passed into the function. That was already demonstrated with my previous post.

If I don't want to use a function, is this the only way to define the 26 pay period?

DECLARE @My_Date AS DATE = '2018-01-05'

;WITH
  cteTally10Base AS (SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number))
, cteTally10 AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number FROM cteTally10Base)

SELECT Last_Day_Of_Paid_Period FROM cteTally10 t
CROSS APPLY (SELECT DATEADD(DAY, -DATEDIFF(DAY, 5, @My_Date) % 7 + (14 * t.number), CAST(@My_Date AS DATE))  AS Last_Day_Of_Paid_Period) T2
27

I just create a physical tally table. You can create it in a shared db if you want, and reference it from there. For example, after running the code below, you can do:
SELECT .. FROM shared_db.dbo.tally t WHERE ... t.number ...
And you don't have to constantly put ctes in every query.

IF OBJECT_ID('dbo.tally') IS NOT NULL
    DROP TABLE dbo.tally;

CREATE TABLE dbo.tally (
    number int NOT NULL,
    CONSTRAINT tally__PK PRIMARY KEY CLUSTERED ( number ) WITH ( FILLFACTOR = 100 )
    );
INSERT INTO dbo.tally VALUES(0);

;WITH
cteTally10 AS (
    SELECT Number FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS Numbers(Number)
),
cteTally100 AS (
    SELECT 1 AS number
    FROM cteTally10 c1
    CROSS JOIN cteTally10 c2
),
cteTally10K AS (
    SELECT 1 AS number
    FROM cteTally100 c1
    CROSS JOIN cteTally100 c2
),
cteTally1Mil AS (
    SELECT ROW_NUMBER() OVER(ORDER BY c1.number) AS number
    FROM cteTally100 c1
    CROSS JOIN cteTally10K c2
)
INSERT INTO dbo.tally
SELECT number
FROM cteTally1Mil;
1 Like
28

That is some crazy & cool stuff there Scott! Thank You. I tweaked your query a bit for my learning curve but this is what I have and it works!

IF OBJECT_ID('tempdb..#TEMPTable_Tally') IS NOT NULL
BEGIN
	DROP TABLE #TEMPTable_Tally
END

CREATE TABLE #TEMPTable_Tally 
(
    number int NOT NULL,
    --CONSTRAINT tally__PK PRIMARY KEY CLUSTERED ( number ) WITH ( FILLFACTOR = 100 )
);
--INSERT INTO #TEMPTable_Tally VALUES(0);

;WITH
cteTally10 AS
(
    SELECT Number FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS Numbers(Number)
)
, cteTally100 AS 
(
    SELECT 1 AS number FROM cteTally10 c1
    CROSS JOIN cteTally10 c2
    CROSS JOIN cteTally10 c3
)
--, cteTally10K AS
--(
--    SELECT 1 AS number FROM cteTally100 c1
--    CROSS JOIN cteTally100 c2
--)
,cteTally1Mil AS 
(
    --SELECT ROW_NUMBER() OVER(ORDER BY c1.number) AS number
	SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number
    FROM cteTally100 c1
    --CROSS JOIN cteTally10K c2
)

INSERT INTO #TEMPTable_Tally
SELECT number FROM cteTally1Mil;

--SELECT * FROM #TEMPTable_Tally



DECLARE @My_Date AS DATE = '2018-01-05'

SELECT
	  DATEADD(DAY, -13, Last_Day_Of_Paid_Period)	AS First_Day_Of_Paid_Period
	, Last_Day_Of_Paid_Period
FROM #TEMPTable_Tally aT1
CROSS APPLY (SELECT DATEADD(DAY, -DATEDIFF(DAY, 5, @My_Date) % 7 + (14 * aT1.number), CAST(@My_Date AS DATE))  AS Last_Day_Of_Paid_Period) T2
29

Nice job!

And no calendar tables or other expensive lookups/searches.

1 Like
30

A physical table is certainly another option, though it is generally less efficient due to the IO overhead. CPUs are actually a lot better a just generating the numbers on the fly. And, of course, with a physical table there is always the possibility that someone comes along and deletes a row from it....

1 Like
31

If you've got people arbitrarily deleting rows from your SQL tables, you've got a vastly bigger problem than an identity table.

32

Thank you to all.

The reason why I asked to do this without a function is because of two reasons.

First, I only have read access to the vendor database. I can setup a function locally and call it across server which eventually I will do this.

Second, the more important reason is because I just picked up on this "TALLY" concept and therefore, I am fascinated that it is a way to avoid loops. I want to continue to play with it in the code until I am super familiarize with it. Eventually, I will use it in a function but as I am coding and practicing, I just want the tally part in front of me, rather than hidden in the function. Sorry, this is just how I learn by keep questioning the code. I totally agree this may not be the best performance and maintenance but for now, it will make me more fluent. Eventually, I will use the provided function (above). Thanks again.

I am now able to do a double tally by breaking the Pay Period into two groups. This is almost like two nested loops. Very cool

DECLARE @pi_Date_Today AS DATE
SET @pi_Date_Today = '2018-07-01'
SELECT
	  T1.My_Week
	, T1.My_Date
	, DATENAME(WEEKDAY, T1.My_Date) AS Day_Name
FROM
(
	SELECT
		  CAST(DATEADD(DAY,-(DATEDIFF(DAY, -1, DATEADD(WEEK, nn, @pi_Date_Today)		) % 7) +n,		DATEADD(WEEK, nn, @pi_Date_Today)) AS DATE)		AS My_Date
		, CAST(DATEADD(DAY,-(DATEDIFF(DAY, -1, DATEADD(WEEK, nn, @pi_Date_Today)		) % 7),			DATEADD(WEEK, nn, @pi_Date_Today)) AS DATE)		AS My_Week
	FROM (VALUES (0),(1),(2),(3),(4),(5),(6)) AS tally1(n)
	CROSS JOIN (VALUES (0),(1)) AS tally2(nn)
)T1
ORDER BY T1.My_Date
33

I have been thinking about versus. When would you use a loop over a tally?

34

Generally never. SQL is a set based language and loops are mostly counter to the way it is supposed to work. Dealing with things a row at a time generally leads to very poor performing code.

1 Like
35

When you had to. When what you were doing was so complex or involved that you needed multiple statements of code to do it.

1 Like