• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 201
  • Last Modified:

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.
0
Manju
Asked:
Manju
  • 8
  • 7
1 Solution
 
Pawan KumarDatabase ExpertCommented:
Week numbers are incorrect?
0
 
ManjuIT - Project ManagerAuthor Commented:
@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
0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
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.

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
*/

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
ManjuIT - Project ManagerAuthor Commented:
Unfortunately, the system i have now is 2008 R2 and DATEFROMPARTS wont work here to test.
0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
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.
0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
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
*/

Open in new window

0
 
ManjuIT - Project ManagerAuthor Commented:
@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.
0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
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.

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
*/

Open in new window

0
 
ManjuIT - Project ManagerAuthor Commented:
Perfect.
0
 
ManjuIT - Project ManagerAuthor Commented:
@Nakul - How do i loop the @currentyear for all the years in table ?
0
 
ManjuIT - Project ManagerAuthor Commented:
Ex: Select distinct year(fiscaldate) from tblname. the above sp needs to give me the data for all those years in that query
0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
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.

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
*/

Open in new window

0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
Rectified the week-numbers in this one:

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 % 52) + 1 AS [RelativeWeekNumber], --Retifying the week-number so that we start from week #1 in every financial year
       wpgCTE.WeekStartDate,
       wpgCTE.WeekEndDate
FROM sqlTwinsWeekPatternGenerator AS wpgCTE
OPTION (MAXRECURSION 0);
GO

/*************************************
RESULTS (ASSUMPTION, CASE 02)
==============================
RelativeWeekNumber WeekStartDate WeekEndDate
------------------ ------------- -----------
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
29                 2018-01-07    2018-01-13
30                 2018-01-14    2018-01-20
31                 2018-01-21    2018-01-27
32                 2018-01-28    2018-02-03
33                 2018-02-04    2018-02-10
34                 2018-02-11    2018-02-17
35                 2018-02-18    2018-02-24
36                 2018-02-25    2018-03-03
37                 2018-03-04    2018-03-10
38                 2018-03-11    2018-03-17
39                 2018-03-18    2018-03-24
40                 2018-03-25    2018-03-31
41                 2018-04-01    2018-04-07
42                 2018-04-08    2018-04-14
43                 2018-04-15    2018-04-21
44                 2018-04-22    2018-04-28
45                 2018-04-29    2018-05-05
46                 2018-05-06    2018-05-12
47                 2018-05-13    2018-05-19
48                 2018-05-20    2018-05-26
49                 2018-05-27    2018-06-02
50                 2018-06-03    2018-06-09
51                 2018-06-10    2018-06-16
52                 2018-06-17    2018-06-23
1                  2018-06-24    2018-06-30
2                  2018-07-01    2018-07-07
3                  2018-07-08    2018-07-14
4                  2018-07-15    2018-07-21
5                  2018-07-22    2018-07-28
6                  2018-07-29    2018-08-04
7                  2018-08-05    2018-08-11
8                  2018-08-12    2018-08-18
9                  2018-08-19    2018-08-25
10                 2018-08-26    2018-09-01
11                 2018-09-02    2018-09-08
12                 2018-09-09    2018-09-15
13                 2018-09-16    2018-09-22
14                 2018-09-23    2018-09-29
15                 2018-09-30    2018-10-06
16                 2018-10-07    2018-10-13
17                 2018-10-14    2018-10-20
18                 2018-10-21    2018-10-27
19                 2018-10-28    2018-11-03
20                 2018-11-04    2018-11-10
21                 2018-11-11    2018-11-17
22                 2018-11-18    2018-11-24
23                 2018-11-25    2018-12-01
24                 2018-12-02    2018-12-08
25                 2018-12-09    2018-12-15
26                 2018-12-16    2018-12-22
27                 2018-12-23    2018-12-29
28                 2018-12-30    2019-01-05
29                 2019-01-06    2019-01-12
30                 2019-01-13    2019-01-19
31                 2019-01-20    2019-01-26
32                 2019-01-27    2019-02-02
33                 2019-02-03    2019-02-09
34                 2019-02-10    2019-02-16
35                 2019-02-17    2019-02-23
36                 2019-02-24    2019-03-02
37                 2019-03-03    2019-03-09
38                 2019-03-10    2019-03-16
39                 2019-03-17    2019-03-23
40                 2019-03-24    2019-03-30
41                 2019-03-31    2019-04-06
42                 2019-04-07    2019-04-13
43                 2019-04-14    2019-04-20
44                 2019-04-21    2019-04-27
45                 2019-04-28    2019-05-04
46                 2019-05-05    2019-05-11
47                 2019-05-12    2019-05-18
48                 2019-05-19    2019-05-25
49                 2019-05-26    2019-06-01
50                 2019-06-02    2019-06-08
51                 2019-06-09    2019-06-15
52                 2019-06-16    2019-06-22
1                  2019-06-23    2019-06-29
2                  2019-06-30    2019-07-06
3                  2019-07-07    2019-07-13
4                  2019-07-14    2019-07-20
5                  2019-07-21    2019-07-27
6                  2019-07-28    2019-08-03
7                  2019-08-04    2019-08-10
8                  2019-08-11    2019-08-17
9                  2019-08-18    2019-08-24
10                 2019-08-25    2019-08-31
11                 2019-09-01    2019-09-07
12                 2019-09-08    2019-09-14
13                 2019-09-15    2019-09-21
14                 2019-09-22    2019-09-28
15                 2019-09-29    2019-10-05
16                 2019-10-06    2019-10-12
17                 2019-10-13    2019-10-19
18                 2019-10-20    2019-10-26
19                 2019-10-27    2019-11-02
20                 2019-11-03    2019-11-09
21                 2019-11-10    2019-11-16
22                 2019-11-17    2019-11-23
23                 2019-11-24    2019-11-30
24                 2019-12-01    2019-12-07
25                 2019-12-08    2019-12-14
26                 2019-12-15    2019-12-21
27                 2019-12-22    2019-12-28
28                 2019-12-29    2020-01-04
29                 2020-01-05    2020-01-11
30                 2020-01-12    2020-01-18
31                 2020-01-19    2020-01-25
32                 2020-01-26    2020-02-01
33                 2020-02-02    2020-02-08
34                 2020-02-09    2020-02-15
35                 2020-02-16    2020-02-22
36                 2020-02-23    2020-02-29
37                 2020-03-01    2020-03-07
38                 2020-03-08    2020-03-14
39                 2020-03-15    2020-03-21
40                 2020-03-22    2020-03-28
41                 2020-03-29    2020-04-04
42                 2020-04-05    2020-04-11
43                 2020-04-12    2020-04-18
44                 2020-04-19    2020-04-25
45                 2020-04-26    2020-05-02
46                 2020-05-03    2020-05-09
47                 2020-05-10    2020-05-16
48                 2020-05-17    2020-05-23
49                 2020-05-24    2020-05-30
50                 2020-05-31    2020-06-06
51                 2020-06-07    2020-06-13
52                 2020-06-14    2020-06-20
1                  2020-06-21    2020-06-27
2                  2020-06-28    2020-07-04
3                  2020-07-05    2020-07-11
4                  2020-07-12    2020-07-18
5                  2020-07-19    2020-07-25
6                  2020-07-26    2020-08-01
7                  2020-08-02    2020-08-08
8                  2020-08-09    2020-08-15
9                  2020-08-16    2020-08-22
10                 2020-08-23    2020-08-29
11                 2020-08-30    2020-09-05
12                 2020-09-06    2020-09-12
13                 2020-09-13    2020-09-19
14                 2020-09-20    2020-09-26
15                 2020-09-27    2020-10-03
16                 2020-10-04    2020-10-10
17                 2020-10-11    2020-10-17
18                 2020-10-18    2020-10-24
19                 2020-10-25    2020-10-31
20                 2020-11-01    2020-11-07
21                 2020-11-08    2020-11-14
22                 2020-11-15    2020-11-21
23                 2020-11-22    2020-11-28
24                 2020-11-29    2020-12-05
25                 2020-12-06    2020-12-12
26                 2020-12-13    2020-12-19
27                 2020-12-20    2020-12-26
28                 2020-12-27    2021-01-02
*/

Open in new window

0
 
ManjuIT - Project ManagerAuthor Commented:
Yes, but this is increasing the week numbers continuously. However Every year should have only 52/53 weeks.
0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
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 :)
0
 
ManjuIT - Project ManagerAuthor Commented:
Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now