ajmoon1313 (9/1/2014)
Thank you all in advance to new blogger and SQL user,I've been task to pull daily report that covers Thursday throught Wednesday (date field as varchar(10) 2014-09-02)
for example today is 2 Sept so my week (dates) I need to pull from is 28 Aug through 3 Sept.
I am at a complete loss as where to start on this. Any suggestions and thoughts would be wonderful.
Thanks AJ
Here is some code that should get you started. The code creates a group number for each week starting Thursday
😎
USE tempdb;
GO
DECLARE @TEST_DATA TABLE
(
TD_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
,TD_TEXT VARCHAR(20) NOT NULL
,TD_STRDATE VARCHAR(10) NOT NULL
);
DECLARE @SAMPLE_SIZE INT = 100;
DECLARE @SAMPLE_FIRST_DAY DATETIME = '2014-08-01';
;WITH T(N) AS (SELECT N FROM ( VALUES (NULL),(NULL),(NULL),(NULL),(NULL)
,(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,NUMS(N) AS(SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N
FROM T T1, T T2, T T3, T T4, T T5, T T6, T T7)
INSERT INTO @TEST_DATA (TD_TEXT, TD_STRDATE)
SELECT
REPLICATE(CHAR(64 + (((NM.N + 25) % 26) + 1)),3) + CHAR(32)
+ DATENAME(WEEKDAY,DATEADD(DAY,NM.N - 1,@SAMPLE_FIRST_DAY))
,CONVERT(VARCHAR(10),DATEADD(DAY,NM.N - 1,@SAMPLE_FIRST_DAY),21)
FROM NUMS NM;
/*
Create a group number for each reporting week by counting days since
1900-01-01 (Monday), shifting the modulo 7 of the number by 4 (Thursday)
and finally count the weeks from 1900-01-01. The number 5217 are the
weeks since 2000-01-01, purely cosmetic ;-)
*/
SELECT
TX.TD_ID
,TX.TD_TEXT
,TX.TD_STRDATE
,DATEDIFF(WEEK,CAST(0 AS DATETIME)
,DATEADD(DAY,-((DATEDIFF(DAY
,CAST(0 AS DATETIME)
,CONVERT(DATETIME,TX.TD_STRDATE,21)) + 4 ) % 7)
,CONVERT(DATETIME,TX.TD_STRDATE,21))) - 5217 AS GROUP_NUMBER
FROM @TEST_DATA TX
Top 20 results
TD_ID TD_TEXT TD_STRDATE GROUP_NUMBER
----------- -------------------- ---------- ------------
1 AAA Friday 2014-08-01 761
2 BBB Saturday 2014-08-02 761
3 CCC Sunday 2014-08-03 761
4 DDD Monday 2014-08-04 761
5 EEE Tuesday 2014-08-05 761
6 FFF Wednesday 2014-08-06 761
7 GGG Thursday 2014-08-07 762
8 HHH Friday 2014-08-08 762
9 III Saturday 2014-08-09 762
10 JJJ Sunday 2014-08-10 762
11 KKK Monday 2014-08-11 762
12 LLL Tuesday 2014-08-12 762
13 MMM Wednesday 2014-08-13 762
14 NNN Thursday 2014-08-14 763
15 OOO Friday 2014-08-15 763
16 PPP Saturday 2014-08-16 763
17 QQQ Sunday 2014-08-17 763
18 RRR Monday 2014-08-18 763
19 SSS Tuesday 2014-08-19 763
20 TTT Wednesday 2014-08-20 763