Calculate Number of INCLUDING Weeks between 2 dates

I need to calculate Number of INCLUDING Weeks (even if only 1 day falls in the next week (weeks starts on Monday, ends on Sunday), count it as the week as well) between 2 dates.

If I use regular datediff, the output for both is 2.

SELECT datediff(ww, '2022-03-01','2022-03-13')
SELECT datediff(ww, '2022-03-14','2022-03-31')

But I need to get 2 and 3 number of weeks instead:

From Date	To Date	Expected
2022-03-01	2022-03-13	falls in 2 weeks (02/28/2022 - 03/06/2022 and 03/07/2022 - 03/13/2022)
2022-03-14	2022-03-31	falls in 3 weeks (03/14/2022 - 03/20/2022, 03/21/2022 - 03/27/2022 and 03/28/2022 - 04/03/2022)

CodePudding user response：

Here is a solution. NB SQL server count Sunday as the first day of the week. If we wish to count Monday as the first day of the week we have to remove one day from our dates, as in the second query.

CREATE TABLE dates (
From_Date date,   
To_Date   date);
INSERT INTO dates VALUES
('2022-03-01','2022-03-13'),  
('2022-03-14','2022-03-31');

SELECT
  From_Date ,
  DATENAME(ww, From_Date) FromWeek,
  To_Date,
  DATENAME(ww, To_Date) ToWeek,
  1   CAST( DATENAME(ww, To_Date) AS INT)
    - CAST( DATENAME(ww, From_Date) AS INT) WeekDiff
FROM dates;

From_Date  | FromWeek | To_Date    | ToWeek | WeekDiff
:--------- | :------- | :--------- | :----- | -------:
2022-03-01 | 10       | 2022-03-13 | 12     |        3
2022-03-14 | 12       | 2022-03-31 | 14     |        3

SELECT
  From_Date ,
  DATENAME(ww, From_Date) FromWeek,
  To_Date,
  DATENAME(ww, To_Date) ToWeek,
  1   CAST( DATENAME(ww, DATEADD(day, -1, To_Date)) AS INT)
    - CAST( DATENAME(ww, DATEADD(day, -1, From_Date)) AS INT) WeekDiff
FROM dates;

From_Date  | FromWeek | To_Date    | ToWeek | WeekDiff
:--------- | :------- | :--------- | :----- | -------:
2022-03-01 | 10       | 2022-03-13 | 12     |        2
2022-03-14 | 12       | 2022-03-31 | 14     |        3

db<>fiddle here