# Week's date range

Thank you for taking the time to explain that Scott!

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?

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

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

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``````

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``````

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

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
, 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``````

Nice job!

And no calendar tables or other expensive lookups/searches.

1 Like

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

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

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
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``````

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

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

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