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.
LVL 7
ManjuIT - Project ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.