SQL Week Number from a specific month number and Starting day
Experts - I need to find week numbers from a specific month number and a start day.
Ex:
In SQL Table, I have FiscalDate as smalldatetime column which will have dates from 2017 start to 2030 end.
Now, I need an SP which should have a parameter of month number and starting day.
Ex:
Exec SP_UpdateFiscalWeek(7, 'Sunday')
The above sp should consider July as the first month of the year & calculate the fiscal week's accordingly for the whole datasets & update Sunday as the starting day for week's calculation.
Ex:
In SQL Table, I have FiscalDate as smalldatetime column which will have dates from 2017 start to 2030 end.
Now, I need an SP which should have a parameter of month number and starting day.
Ex:
Exec SP_UpdateFiscalWeek(7, 'Sunday')
The above sp should consider July as the first month of the year & calculate the fiscal week's accordingly for the whole datasets & update Sunday as the starting day for week's calculation.
Pawan Kumar
Week numbers are incorrect?
ASKER
@Pawan - Yes, Now, I am going to get the week numbers and qtr numbers outside of our previous sp.
however need an sp to satisfy the above criteria
however need an sp to satisfy the above criteria
Here's a week pattern generator that may be helpful.
NOTE: I am assuming that because July 01, 2017 falls on a Saturday, your week starts on June 25, 2017. However, this logic can easily be modified to change the starting date accordingly.
NOTE: I am assuming that because July 01, 2017 falls on a Saturday, your week starts on June 25, 2017. However, this logic can easily be modified to change the starting date accordingly.
USE tempdb;
GO
--Your input variables
DECLARE @fiscalYearStartingMonth TINYINT = 7;
DECLARE @weekStartDay VARCHAR(20) = 'Sunday';
--Internal Variables
DECLARE @weekStartDayNumberValue TINYINT = CASE @weekStartDay WHEN 'Sunday' THEN 1
WHEN 'Monday' THEN 2
WHEN 'Tuesday' THEN 3
WHEN 'Wednesday' THEN 4
WHEN 'Thursday' THEN 5
WHEN 'Friday' THEN 6
WHEN 'Saturday' THEN 7
END;
DECLARE @currentYear INT = DATEPART(YEAR, GETDATE());
DECLARE @recursiveWeekStartDate DATE = DATEFROMPARTS(@currentYear,@fiscalYearStartingMonth,1);
DECLARE @recursiveWeekEndDate DATE = DATEFROMPARTS(@currentYear,12,31);
--Determine where the start of the week falls
--If it matches with our desired pattern, no need to worry
--If it does not match, explicitly adjust the start date
IF (DATEPART(DW,@recursiveWeekStartDate) != @weekStartDayNumberValue)
BEGIN
SET @recursiveWeekStartDate = DATEADD(DAY,(@weekStartDayNumberValue - DATEPART(DW,@recursiveWeekStartDate)),@recursiveWeekStartDate)
END
--Now generate the pattern as requested
;WITH sqlTwinsWeekPatternGenerator (RelativeWeekNumber, WeekStartDate, WeekEndDate)
AS (--Anchor Member
SELECT 1 AS RelativeWeekNumber,
@recursiveWeekStartDate AS WeekStartDate,
DATEADD(DAY, 6, @recursiveWeekStartDate) AS WeekEndDate
UNION ALL
--Recursive Member
SELECT (wpg.RelativeWeekNumber + 1) AS RelativeWeekNumber,
DATEADD(DAY,7,wpg.WeekStartDate) AS WeekStartDate,
DATEADD(DAY,6,DATEADD(DAY,7,wpg.WeekStartDate)) AS WeekEndDate
FROM sqlTwinsWeekPatternGenerator AS wpg
WHERE wpg.WeekStartDate < DATEADD(DAY,-6,@recursiveWeekEndDate)
)
SELECT wpgCTE.RelativeWeekNumber,
wpgCTE.WeekStartDate,
wpgCTE.WeekEndDate
FROM sqlTwinsWeekPatternGenerator AS wpgCTE;
GO
/*************************************
RESULTS
=======
RelativeWeekNumber WeekStartDate WeekEndDate
------------------ ------------- -----------
1 2017-06-25 2017-07-01
2 2017-07-02 2017-07-08
3 2017-07-09 2017-07-15
4 2017-07-16 2017-07-22
5 2017-07-23 2017-07-29
6 2017-07-30 2017-08-05
7 2017-08-06 2017-08-12
8 2017-08-13 2017-08-19
9 2017-08-20 2017-08-26
10 2017-08-27 2017-09-02
11 2017-09-03 2017-09-09
12 2017-09-10 2017-09-16
13 2017-09-17 2017-09-23
14 2017-09-24 2017-09-30
15 2017-10-01 2017-10-07
16 2017-10-08 2017-10-14
17 2017-10-15 2017-10-21
18 2017-10-22 2017-10-28
19 2017-10-29 2017-11-04
20 2017-11-05 2017-11-11
21 2017-11-12 2017-11-18
22 2017-11-19 2017-11-25
23 2017-11-26 2017-12-02
24 2017-12-03 2017-12-09
25 2017-12-10 2017-12-16
26 2017-12-17 2017-12-23
27 2017-12-24 2017-12-30
28 2017-12-31 2018-01-06
*/ASKER
Unfortunately, the system i have now is 2008 R2 and DATEFROMPARTS wont work here to test.
Ok, added SQL Server 2008 to the question area so that experts are aware about the impacted system version. I am setting out for lunch, but will revert back with alternatives once I am back.
Here's the SQL 2008 R2 way (basically, used simple string concatenation instead of DATEFROMPARTS). Also, I ensured that all internal calculations are based on the fact that Sunday is the first day of the week (Default SQL Server behaviour).
USE tempdb;
GO
--Your input variables
DECLARE @fiscalYearStartingMonth TINYINT = 7;
DECLARE @weekStartDay VARCHAR(20) = 'Sunday';
--Internal Variables
--All our calculations are based on the fact that Sunday is the first day of the week (Default SQL Server behaviour)
--Just ensuring that this is the case wherever this logic is used
SET DATEFIRST 7;
DECLARE @weekStartDayNumberValue TINYINT = CASE @weekStartDay WHEN 'Sunday' THEN 1
WHEN 'Monday' THEN 2
WHEN 'Tuesday' THEN 3
WHEN 'Wednesday' THEN 4
WHEN 'Thursday' THEN 5
WHEN 'Friday' THEN 6
WHEN 'Saturday' THEN 7
END;
DECLARE @currentYear INT = DATEPART(YEAR, GETDATE());
DECLARE @recursiveWeekStartDate DATE;
DECLARE @recursiveWeekEndDate DATE;
SELECT @recursiveWeekStartDate = CAST(@currentYear AS CHAR(4)) + '-' + CAST(@fiscalYearStartingMonth AS CHAR(2)) + '-01';
SELECT @recursiveWeekEndDate = CAST(@currentYear AS CHAR(4)) + '-12-31';
--Determine where the start of the week falls
--If it matches with our desired pattern, no need to worry
--If it does not match, explicitly adjust the start date
IF (DATEPART(DW,@recursiveWeekStartDate) != @weekStartDayNumberValue)
BEGIN
SET @recursiveWeekStartDate = DATEADD(DAY,(@weekStartDayNumberValue - DATEPART(DW,@recursiveWeekStartDate)),@recursiveWeekStartDate)
END
--Now generate the pattern as requested
;WITH sqlTwinsWeekPatternGenerator (RelativeWeekNumber, WeekStartDate, WeekEndDate)
AS (--Anchor Member
SELECT 1 AS RelativeWeekNumber,
@recursiveWeekStartDate AS WeekStartDate,
DATEADD(DAY, 6, @recursiveWeekStartDate) AS WeekEndDate
UNION ALL
--Recursive Member
SELECT (wpg.RelativeWeekNumber + 1) AS RelativeWeekNumber,
DATEADD(DAY,7,wpg.WeekStartDate) AS WeekStartDate,
DATEADD(DAY,6,DATEADD(DAY,7,wpg.WeekStartDate)) AS WeekEndDate
FROM sqlTwinsWeekPatternGenerator AS wpg
WHERE wpg.WeekStartDate < DATEADD(DAY,-6,@recursiveWeekEndDate)
)
SELECT wpgCTE.RelativeWeekNumber,
wpgCTE.WeekStartDate,
wpgCTE.WeekEndDate
FROM sqlTwinsWeekPatternGenerator AS wpgCTE;
GO
/*************************************
RESULTS
=======
RelativeWeekNumber WeekStartDate WeekEndDate
------------------ ------------- -----------
1 2017-06-25 2017-07-01
2 2017-07-02 2017-07-08
3 2017-07-09 2017-07-15
4 2017-07-16 2017-07-22
5 2017-07-23 2017-07-29
6 2017-07-30 2017-08-05
7 2017-08-06 2017-08-12
8 2017-08-13 2017-08-19
9 2017-08-20 2017-08-26
10 2017-08-27 2017-09-02
11 2017-09-03 2017-09-09
12 2017-09-10 2017-09-16
13 2017-09-17 2017-09-23
14 2017-09-24 2017-09-30
15 2017-10-01 2017-10-07
16 2017-10-08 2017-10-14
17 2017-10-15 2017-10-21
18 2017-10-22 2017-10-28
19 2017-10-29 2017-11-04
20 2017-11-05 2017-11-11
21 2017-11-12 2017-11-18
22 2017-11-19 2017-11-25
23 2017-11-26 2017-12-02
24 2017-12-03 2017-12-09
25 2017-12-10 2017-12-16
26 2017-12-17 2017-12-23
27 2017-12-24 2017-12-30
28 2017-12-31 2018-01-06
*/ASKER
@Nakul - Per the above query,
Relativeweeknumber is 1 for Weekstartdate(2017-06-25) and Weekenddate(2017-07-01). However it should be WK52 / 53 for previous year. and 2nd July should be wk1.
Relativeweeknumber is 1 for Weekstartdate(2017-06-25) and Weekenddate(2017-07-01). However it should be WK52 / 53 for previous year. and 2nd July should be wk1.
That was the assumption I had called out, and in the absence of the accurate requirement was one that I had to take.
With the requirement now defined, here's the updated code. You can toggle between the two assumptions to fine tune the same.
With the requirement now defined, here's the updated code. You can toggle between the two assumptions to fine tune the same.
USE tempdb;
GO
--Your input variables
DECLARE @fiscalYearStartingMonth TINYINT = 7;
DECLARE @weekStartDay VARCHAR(20) = 'Sunday';
--Internal Variables
--All our calculations are based on the fact that Sunday is the first day of the week (Default SQL Server behaviour)
--Just ensuring that this is the case wherever this logic is used
SET DATEFIRST 7;
DECLARE @weekStartDayNumberValue TINYINT = CASE @weekStartDay WHEN 'Sunday' THEN 1
WHEN 'Monday' THEN 2
WHEN 'Tuesday' THEN 3
WHEN 'Wednesday' THEN 4
WHEN 'Thursday' THEN 5
WHEN 'Friday' THEN 6
WHEN 'Saturday' THEN 7
END;
DECLARE @currentYear INT = DATEPART(YEAR, GETDATE());
--SQL 2012 way
/*
DECLARE @recursiveWeekStartDate DATE = DATEFROMPARTS(@currentYear,@fiscalYearStartingMonth,1);
DECLARE @recursiveWeekEndDate DATE = DATEFROMPARTS(@currentYear,12,31);
*/
--SQL 2008 R2 way
DECLARE @recursiveWeekStartDate DATE;
DECLARE @recursiveWeekEndDate DATE;
SELECT @recursiveWeekStartDate = CAST(@currentYear AS CHAR(4)) + '-' + CAST(@fiscalYearStartingMonth AS CHAR(2)) + '-01';
SELECT @recursiveWeekEndDate = CAST(@currentYear AS CHAR(4)) + '-12-31';
--Determine where the start of the week falls
--If it matches with our desired pattern, no need to worry
--If it does not match, explicitly adjust the start date
----ASSUMPTION: CASE 01: Week starts on/before the week start date
--IF (DATEPART(DW,@recursiveWeekStartDate) != @weekStartDayNumberValue)
--BEGIN
-- SET @recursiveWeekStartDate = DATEADD(DAY,(@weekStartDayNumberValue - DATEPART(DW,@recursiveWeekStartDate)),@recursiveWeekStartDate)
--END
--ASSUMPTION: CASE 02: Week starts on/after the week start date
IF (DATEPART(DW,@recursiveWeekStartDate) != @weekStartDayNumberValue)
BEGIN
SET @recursiveWeekStartDate = DATEADD(DAY,(8 - DATEPART(DW,@recursiveWeekStartDate)),@recursiveWeekStartDate)
END
--Now generate the pattern as requested
;WITH sqlTwinsWeekPatternGenerator (RelativeWeekNumber, WeekStartDate, WeekEndDate)
AS (--Anchor Member
SELECT 1 AS RelativeWeekNumber,
@recursiveWeekStartDate AS WeekStartDate,
DATEADD(DAY, 6, @recursiveWeekStartDate) AS WeekEndDate
UNION ALL
--Recursive Member
SELECT (wpg.RelativeWeekNumber + 1) AS RelativeWeekNumber,
DATEADD(DAY,7,wpg.WeekStartDate) AS WeekStartDate,
DATEADD(DAY,6,DATEADD(DAY,7,wpg.WeekStartDate)) AS WeekEndDate
FROM sqlTwinsWeekPatternGenerator AS wpg
WHERE wpg.WeekStartDate < DATEADD(DAY,-6,@recursiveWeekEndDate)
)
SELECT wpgCTE.RelativeWeekNumber,
wpgCTE.WeekStartDate,
wpgCTE.WeekEndDate
FROM sqlTwinsWeekPatternGenerator AS wpgCTE;
GO
/*************************************
RESULTS (ASSUMPTION, CASE 02)
==============================
RelativeWeekNumber WeekStartDate WeekEndDate
------------------ ------------- -----------
1 2017-07-02 2017-07-08
2 2017-07-09 2017-07-15
3 2017-07-16 2017-07-22
4 2017-07-23 2017-07-29
5 2017-07-30 2017-08-05
6 2017-08-06 2017-08-12
7 2017-08-13 2017-08-19
8 2017-08-20 2017-08-26
9 2017-08-27 2017-09-02
10 2017-09-03 2017-09-09
11 2017-09-10 2017-09-16
12 2017-09-17 2017-09-23
13 2017-09-24 2017-09-30
14 2017-10-01 2017-10-07
15 2017-10-08 2017-10-14
16 2017-10-15 2017-10-21
17 2017-10-22 2017-10-28
18 2017-10-29 2017-11-04
19 2017-11-05 2017-11-11
20 2017-11-12 2017-11-18
21 2017-11-19 2017-11-25
22 2017-11-26 2017-12-02
23 2017-12-03 2017-12-09
24 2017-12-10 2017-12-16
25 2017-12-17 2017-12-23
26 2017-12-24 2017-12-30
27 2017-12-31 2018-01-06
*/ASKER
Perfect.
ASKER
@Nakul - How do i loop the @currentyear for all the years in table ?
ASKER
Ex: Select distinct year(fiscaldate) from tblname. the above sp needs to give me the data for all those years in that query
Hello!
In the sample below, I have used a table variable (@yearTable) to achieve the same outcome. Also, used the option MAXRECURSION to allow us to iterate through the CTE for more than 100 times.
In the sample below, I have used a table variable (@yearTable) to achieve the same outcome. Also, used the option MAXRECURSION to allow us to iterate through the CTE for more than 100 times.
USE tempdb;
GO
--Your input variables
DECLARE @fiscalYearStartingMonth TINYINT = 7;
DECLARE @weekStartDay VARCHAR(20) = 'Sunday';
DECLARE @yearTable TABLE (yearValue INT);
INSERT INTO @yearTable (yearValue)
VALUES (2017),
(2018),
(2019),
(2020);
--Internal Variables
--All our calculations are based on the fact that Sunday is the first day of the week (Default SQL Server behaviour)
--Just ensuring that this is the case wherever this logic is used
SET DATEFIRST 7;
DECLARE @weekStartDayNumberValue TINYINT = CASE @weekStartDay WHEN 'Sunday' THEN 1
WHEN 'Monday' THEN 2
WHEN 'Tuesday' THEN 3
WHEN 'Wednesday' THEN 4
WHEN 'Thursday' THEN 5
WHEN 'Friday' THEN 6
WHEN 'Saturday' THEN 7
END;
DECLARE @currentYear INT = DATEPART(YEAR, GETDATE());
--SQL 2012 way
/*
DECLARE @recursiveWeekStartDate DATE = DATEFROMPARTS(@currentYear,@fiscalYearStartingMonth,1);
DECLARE @recursiveWeekEndDate DATE = DATEFROMPARTS(@currentYear,12,31);
*/
--SQL 2008 R2 way
DECLARE @recursiveWeekStartDate DATE;
DECLARE @recursiveWeekEndDate DATE;
SELECT @recursiveWeekStartDate = CAST(@currentYear AS CHAR(4)) + '-' + CAST(@fiscalYearStartingMonth AS CHAR(2)) + '-01';
--Ending in the same year
--SELECT @recursiveWeekEndDate = CAST(@currentYear AS CHAR(4)) + '-12-31';
--Ending in the year from another table
SELECT @recursiveWeekEndDate = CAST(MAX(DISTINCT yt.yearValue) AS CHAR(4)) + '-12-31' FROM @yearTable AS yt;
--Determine where the start of the week falls
--If it matches with our desired pattern, no need to worry
--If it does not match, explicitly adjust the start date
----ASSUMPTION: CASE 01: Week starts on/before the week start date
--IF (DATEPART(DW,@recursiveWeekStartDate) != @weekStartDayNumberValue)
--BEGIN
-- SET @recursiveWeekStartDate = DATEADD(DAY,(@weekStartDayNumberValue - DATEPART(DW,@recursiveWeekStartDate)),@recursiveWeekStartDate)
--END
--ASSUMPTION: CASE 02: Week starts on/after the week start date
IF (DATEPART(DW,@recursiveWeekStartDate) != @weekStartDayNumberValue)
BEGIN
SET @recursiveWeekStartDate = DATEADD(DAY,(8 - DATEPART(DW,@recursiveWeekStartDate)),@recursiveWeekStartDate)
END
--Now generate the pattern as requested
;WITH sqlTwinsWeekPatternGenerator (RelativeWeekNumber, WeekStartDate, WeekEndDate)
AS (--Anchor Member
SELECT 1 AS RelativeWeekNumber,
@recursiveWeekStartDate AS WeekStartDate,
DATEADD(DAY, 6, @recursiveWeekStartDate) AS WeekEndDate
UNION ALL
--Recursive Member
SELECT (wpg.RelativeWeekNumber + 1) AS RelativeWeekNumber,
DATEADD(DAY,7,wpg.WeekStartDate) AS WeekStartDate,
DATEADD(DAY,6,DATEADD(DAY,7,wpg.WeekStartDate)) AS WeekEndDate
FROM sqlTwinsWeekPatternGenerator AS wpg
WHERE wpg.WeekStartDate < DATEADD(DAY,-6,@recursiveWeekEndDate)
)
SELECT wpgCTE.RelativeWeekNumber,
wpgCTE.WeekStartDate,
wpgCTE.WeekEndDate
FROM sqlTwinsWeekPatternGenerator AS wpgCTE
OPTION (MAXRECURSION 0);
GO
/*************************************
RESULTS (ASSUMPTION, CASE 02)
==============================
RelativeWeekNumber WeekStartDate WeekEndDate
------------------ ------------- -----------
1 2017-07-02 2017-07-08
2 2017-07-09 2017-07-15
3 2017-07-16 2017-07-22
4 2017-07-23 2017-07-29
5 2017-07-30 2017-08-05
6 2017-08-06 2017-08-12
7 2017-08-13 2017-08-19
8 2017-08-20 2017-08-26
9 2017-08-27 2017-09-02
10 2017-09-03 2017-09-09
11 2017-09-10 2017-09-16
12 2017-09-17 2017-09-23
13 2017-09-24 2017-09-30
14 2017-10-01 2017-10-07
15 2017-10-08 2017-10-14
16 2017-10-15 2017-10-21
17 2017-10-22 2017-10-28
18 2017-10-29 2017-11-04
19 2017-11-05 2017-11-11
20 2017-11-12 2017-11-18
21 2017-11-19 2017-11-25
22 2017-11-26 2017-12-02
23 2017-12-03 2017-12-09
24 2017-12-10 2017-12-16
25 2017-12-17 2017-12-23
26 2017-12-24 2017-12-30
27 2017-12-31 2018-01-06
28 2018-01-07 2018-01-13
29 2018-01-14 2018-01-20
30 2018-01-21 2018-01-27
31 2018-01-28 2018-02-03
32 2018-02-04 2018-02-10
33 2018-02-11 2018-02-17
34 2018-02-18 2018-02-24
35 2018-02-25 2018-03-03
36 2018-03-04 2018-03-10
37 2018-03-11 2018-03-17
38 2018-03-18 2018-03-24
39 2018-03-25 2018-03-31
40 2018-04-01 2018-04-07
41 2018-04-08 2018-04-14
42 2018-04-15 2018-04-21
43 2018-04-22 2018-04-28
44 2018-04-29 2018-05-05
45 2018-05-06 2018-05-12
46 2018-05-13 2018-05-19
47 2018-05-20 2018-05-26
48 2018-05-27 2018-06-02
49 2018-06-03 2018-06-09
50 2018-06-10 2018-06-16
51 2018-06-17 2018-06-23
52 2018-06-24 2018-06-30
53 2018-07-01 2018-07-07
54 2018-07-08 2018-07-14
55 2018-07-15 2018-07-21
56 2018-07-22 2018-07-28
57 2018-07-29 2018-08-04
58 2018-08-05 2018-08-11
59 2018-08-12 2018-08-18
60 2018-08-19 2018-08-25
61 2018-08-26 2018-09-01
62 2018-09-02 2018-09-08
63 2018-09-09 2018-09-15
64 2018-09-16 2018-09-22
65 2018-09-23 2018-09-29
66 2018-09-30 2018-10-06
67 2018-10-07 2018-10-13
68 2018-10-14 2018-10-20
69 2018-10-21 2018-10-27
70 2018-10-28 2018-11-03
71 2018-11-04 2018-11-10
72 2018-11-11 2018-11-17
73 2018-11-18 2018-11-24
74 2018-11-25 2018-12-01
75 2018-12-02 2018-12-08
76 2018-12-09 2018-12-15
77 2018-12-16 2018-12-22
78 2018-12-23 2018-12-29
79 2018-12-30 2019-01-05
80 2019-01-06 2019-01-12
81 2019-01-13 2019-01-19
82 2019-01-20 2019-01-26
83 2019-01-27 2019-02-02
84 2019-02-03 2019-02-09
85 2019-02-10 2019-02-16
86 2019-02-17 2019-02-23
87 2019-02-24 2019-03-02
88 2019-03-03 2019-03-09
89 2019-03-10 2019-03-16
90 2019-03-17 2019-03-23
91 2019-03-24 2019-03-30
92 2019-03-31 2019-04-06
93 2019-04-07 2019-04-13
94 2019-04-14 2019-04-20
95 2019-04-21 2019-04-27
96 2019-04-28 2019-05-04
97 2019-05-05 2019-05-11
98 2019-05-12 2019-05-18
99 2019-05-19 2019-05-25
100 2019-05-26 2019-06-01
101 2019-06-02 2019-06-08
102 2019-06-09 2019-06-15
103 2019-06-16 2019-06-22
104 2019-06-23 2019-06-29
105 2019-06-30 2019-07-06
106 2019-07-07 2019-07-13
107 2019-07-14 2019-07-20
108 2019-07-21 2019-07-27
109 2019-07-28 2019-08-03
110 2019-08-04 2019-08-10
111 2019-08-11 2019-08-17
112 2019-08-18 2019-08-24
113 2019-08-25 2019-08-31
114 2019-09-01 2019-09-07
115 2019-09-08 2019-09-14
116 2019-09-15 2019-09-21
117 2019-09-22 2019-09-28
118 2019-09-29 2019-10-05
119 2019-10-06 2019-10-12
120 2019-10-13 2019-10-19
121 2019-10-20 2019-10-26
122 2019-10-27 2019-11-02
123 2019-11-03 2019-11-09
124 2019-11-10 2019-11-16
125 2019-11-17 2019-11-23
126 2019-11-24 2019-11-30
127 2019-12-01 2019-12-07
128 2019-12-08 2019-12-14
129 2019-12-15 2019-12-21
130 2019-12-22 2019-12-28
131 2019-12-29 2020-01-04
132 2020-01-05 2020-01-11
133 2020-01-12 2020-01-18
134 2020-01-19 2020-01-25
135 2020-01-26 2020-02-01
136 2020-02-02 2020-02-08
137 2020-02-09 2020-02-15
138 2020-02-16 2020-02-22
139 2020-02-23 2020-02-29
140 2020-03-01 2020-03-07
141 2020-03-08 2020-03-14
142 2020-03-15 2020-03-21
143 2020-03-22 2020-03-28
144 2020-03-29 2020-04-04
145 2020-04-05 2020-04-11
146 2020-04-12 2020-04-18
147 2020-04-19 2020-04-25
148 2020-04-26 2020-05-02
149 2020-05-03 2020-05-09
150 2020-05-10 2020-05-16
151 2020-05-17 2020-05-23
152 2020-05-24 2020-05-30
153 2020-05-31 2020-06-06
154 2020-06-07 2020-06-13
155 2020-06-14 2020-06-20
156 2020-06-21 2020-06-27
157 2020-06-28 2020-07-04
158 2020-07-05 2020-07-11
159 2020-07-12 2020-07-18
160 2020-07-19 2020-07-25
161 2020-07-26 2020-08-01
162 2020-08-02 2020-08-08
163 2020-08-09 2020-08-15
164 2020-08-16 2020-08-22
165 2020-08-23 2020-08-29
166 2020-08-30 2020-09-05
167 2020-09-06 2020-09-12
168 2020-09-13 2020-09-19
169 2020-09-20 2020-09-26
170 2020-09-27 2020-10-03
171 2020-10-04 2020-10-10
172 2020-10-11 2020-10-17
173 2020-10-18 2020-10-24
174 2020-10-25 2020-10-31
175 2020-11-01 2020-11-07
176 2020-11-08 2020-11-14
177 2020-11-15 2020-11-21
178 2020-11-22 2020-11-28
179 2020-11-29 2020-12-05
180 2020-12-06 2020-12-12
181 2020-12-13 2020-12-19
182 2020-12-20 2020-12-26
183 2020-12-27 2021-01-02
*/ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yes, but this is increasing the week numbers continuously. However Every year should have only 52/53 weeks.
That's a simple one - I have an updated solution in ID: 42382745.
The week-number can be adjusted by working with the modulo (%) in the final SELECT. My solution is just a prototype :)
The week-number can be adjusted by working with the modulo (%) in the final SELECT. My solution is just a prototype :)
ASKER
Thank you