calculating number of weeks in the basis of customized weekend

Question

Different organizations might have a different week off. So, in this case, need to customize the week off. To do it I have tried the Sql query write in below

DECLARE @dayName VARCHAR(9);
SET @dayName = DATEName(DW, GETDATE());

IF(@dayName = 'Friday' OR @dayName = 'Saturday') 
    PRINT 'Weekend';
ELSE
    PRINT 'NOT Weekend';

From the above query I can get my specific weekoff.

for example:

if friday or any other day is mentioned as weekend, then from friday to Thursday corresponds to a week and I need to find how many complete weeks are there in a month? Or in a year?

I want to get number of weeks on the basis of customized weekOff.

What do you mean customized weekOff? Is customized weekOff pass as a parameter or save in other table? — D-Shih, Sep 01 '18 at 07:10
So you meen if sunday is mentioned as weekend, then from sunday to Saturday corresponds to a week and you need to find how many complete weeks are there in a month? Or in a year? Be more specific — Samuel A C, Sep 01 '18 at 07:12
@D-Shih if friday or any other day is mentioned as weekend, then from friday to Thursday corresponds to a week and I need to find how many complete weeks are there in a month? Or in a year? and it will pass as a parameter. — Abdullah Al Mamun, Sep 01 '18 at 07:51
@Abdullah Al mamun find my solution below.. it works for sql server 2012 and + — Samuel A C, Sep 03 '18 at 15:30

D-Shih · Answer 1 · 2018-09-02T08:13:25.397

first, you need to make calendar full year or full month, so I would use cte recursion to make it.

SELECT DATEADD(month, DATEDIFF(month, 0, getdate()), 0) AS StartOfMonth,
       DATEADD(mm,1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) - 1  AS EndOfMonth
UNION ALL
SELECT StartOfMonth +1 , EndOfMonth
FROM CTE 
WHERE StartOfMonth < EndOfMonth

there is 7 days in a week, whatever started with any week, then we will saw this a Gaps and Islands problem, because the week number will repeat from 1to 7.

So I would use CASE WHEN with DateNameto make your customer week date number, which number starts onFriday` or depending on your logic.

 ;WITH CTE AS (
      SELECT DATEADD(month, DATEDIFF(month, 0, getdate()), 0) AS StartOfMonth,
             DATEADD(mm,1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) - 1  AS EndOfMonth
      UNION ALL
      SELECT StartOfMonth +1 , EndOfMonth
      FROM CTE 
      WHERE StartOfMonth < EndOfMonth
    )
 SELECT *,
          CASE DateName(DW, StartOfMonth)
              WHEN 'Friday' THEN 0
              WHEN 'Saturday' THEN 1
              WHEN 'Sunday' THEN 2
              WHEN 'Monday' THEN 3
              WHEN 'Tuesday' THEN 4
              WHEN 'Wednesday' THEN 5
              WHEN 'Thursday' THEN 6
              WHEN 'Tuesday' THEN 7 
          END grp,
           ROW_NUMBER() OVER(ORDER BY StartOfMonth) rn
    FROM CTE

[Results]:

|         StartOfMonth |           EndOfMonth | grp | rn |
|----------------------|----------------------|-----|----|
| 2018-09-01T00:00:00Z | 2018-09-30T00:00:00Z |   1 |  1 |
| 2018-09-02T00:00:00Z | 2018-09-30T00:00:00Z |   2 |  2 |
| 2018-09-03T00:00:00Z | 2018-09-30T00:00:00Z |   3 |  3 |
| 2018-09-04T00:00:00Z | 2018-09-30T00:00:00Z |   4 |  4 |
| 2018-09-05T00:00:00Z | 2018-09-30T00:00:00Z |   5 |  5 |
| 2018-09-06T00:00:00Z | 2018-09-30T00:00:00Z |   6 |  6 |
| 2018-09-07T00:00:00Z | 2018-09-30T00:00:00Z |   0 |  7 |
| 2018-09-08T00:00:00Z | 2018-09-30T00:00:00Z |   1 |  8 |
| 2018-09-09T00:00:00Z | 2018-09-30T00:00:00Z |   2 |  9 |
| 2018-09-10T00:00:00Z | 2018-09-30T00:00:00Z |   3 | 10 |
| 2018-09-11T00:00:00Z | 2018-09-30T00:00:00Z |   4 | 11 |
| 2018-09-12T00:00:00Z | 2018-09-30T00:00:00Z |   5 | 12 |
| 2018-09-13T00:00:00Z | 2018-09-30T00:00:00Z |   6 | 13 |
| 2018-09-14T00:00:00Z | 2018-09-30T00:00:00Z |   0 | 14 |
| 2018-09-15T00:00:00Z | 2018-09-30T00:00:00Z |   1 | 15 |
| 2018-09-16T00:00:00Z | 2018-09-30T00:00:00Z |   2 | 16 |
| 2018-09-17T00:00:00Z | 2018-09-30T00:00:00Z |   3 | 17 |
| 2018-09-18T00:00:00Z | 2018-09-30T00:00:00Z |   4 | 18 |
| 2018-09-19T00:00:00Z | 2018-09-30T00:00:00Z |   5 | 19 |
| 2018-09-20T00:00:00Z | 2018-09-30T00:00:00Z |   6 | 20 |
| 2018-09-21T00:00:00Z | 2018-09-30T00:00:00Z |   0 | 21 |
| 2018-09-22T00:00:00Z | 2018-09-30T00:00:00Z |   1 | 22 |
| 2018-09-23T00:00:00Z | 2018-09-30T00:00:00Z |   2 | 23 |
| 2018-09-24T00:00:00Z | 2018-09-30T00:00:00Z |   3 | 24 |
| 2018-09-25T00:00:00Z | 2018-09-30T00:00:00Z |   4 | 25 |
| 2018-09-26T00:00:00Z | 2018-09-30T00:00:00Z |   5 | 26 |
| 2018-09-27T00:00:00Z | 2018-09-30T00:00:00Z |   6 | 27 |
| 2018-09-28T00:00:00Z | 2018-09-30T00:00:00Z |   0 | 28 |
| 2018-09-29T00:00:00Z | 2018-09-30T00:00:00Z |   1 | 29 |
| 2018-09-30T00:00:00Z | 2018-09-30T00:00:00Z |   2 | 30 |

then we can try to use rn - grp to get the group, which is continuous.

[Results]:

|         StartOfMonth |           EndOfMonth | grp |
|----------------------|----------------------|-----|
| 2018-09-01T00:00:00Z | 2018-09-30T00:00:00Z |   0 |
| 2018-09-02T00:00:00Z | 2018-09-30T00:00:00Z |   0 |
| 2018-09-03T00:00:00Z | 2018-09-30T00:00:00Z |   0 |
| 2018-09-04T00:00:00Z | 2018-09-30T00:00:00Z |   0 |
| 2018-09-05T00:00:00Z | 2018-09-30T00:00:00Z |   0 |
| 2018-09-06T00:00:00Z | 2018-09-30T00:00:00Z |   0 |
| 2018-09-07T00:00:00Z | 2018-09-30T00:00:00Z |   7 |
| 2018-09-08T00:00:00Z | 2018-09-30T00:00:00Z |   7 |
| 2018-09-09T00:00:00Z | 2018-09-30T00:00:00Z |   7 |
| 2018-09-10T00:00:00Z | 2018-09-30T00:00:00Z |   7 |
| 2018-09-11T00:00:00Z | 2018-09-30T00:00:00Z |   7 |
| 2018-09-12T00:00:00Z | 2018-09-30T00:00:00Z |   7 |
| 2018-09-13T00:00:00Z | 2018-09-30T00:00:00Z |   7 |
| 2018-09-14T00:00:00Z | 2018-09-30T00:00:00Z |  14 |
| 2018-09-15T00:00:00Z | 2018-09-30T00:00:00Z |  14 |
| 2018-09-16T00:00:00Z | 2018-09-30T00:00:00Z |  14 |
| 2018-09-17T00:00:00Z | 2018-09-30T00:00:00Z |  14 |
| 2018-09-18T00:00:00Z | 2018-09-30T00:00:00Z |  14 |
| 2018-09-19T00:00:00Z | 2018-09-30T00:00:00Z |  14 |
| 2018-09-20T00:00:00Z | 2018-09-30T00:00:00Z |  14 |
| 2018-09-21T00:00:00Z | 2018-09-30T00:00:00Z |  21 |
| 2018-09-22T00:00:00Z | 2018-09-30T00:00:00Z |  21 |
| 2018-09-23T00:00:00Z | 2018-09-30T00:00:00Z |  21 |
| 2018-09-24T00:00:00Z | 2018-09-30T00:00:00Z |  21 |
| 2018-09-25T00:00:00Z | 2018-09-30T00:00:00Z |  21 |
| 2018-09-26T00:00:00Z | 2018-09-30T00:00:00Z |  21 |
| 2018-09-27T00:00:00Z | 2018-09-30T00:00:00Z |  21 |
| 2018-09-28T00:00:00Z | 2018-09-30T00:00:00Z |  28 |
| 2018-09-29T00:00:00Z | 2018-09-30T00:00:00Z |  28 |
| 2018-09-30T00:00:00Z | 2018-09-30T00:00:00Z |  28 |

final, we just get all count(*) = 7 group in month or year number, which mean complete weeks.

Query 1:

full month

;WITH CTE AS (
  SELECT DATEADD(month, DATEDIFF(month, 0, getdate()), 0) AS StartOfMonth,
         DATEADD(mm,1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) - 1  AS EndOfMonth
  UNION ALL
  SELECT StartOfMonth +1 , EndOfMonth
  FROM CTE 
  WHERE StartOfMonth < EndOfMonth
)
SELECT COUNT(*) fullweekAmount from (
  SELECT 
      MIN(StartOfMonth) startdt,
      MAX(StartOfMonth) enddt
  FROM (
    SELECT *,
          ROW_NUMBER() OVER(ORDER BY StartOfMonth) - 
          CASE DateName(DW, StartOfMonth)
              WHEN 'Friday' THEN 0
              WHEN 'Saturday' THEN 1
              WHEN 'Sunday' THEN 2
              WHEN 'Monday' THEN 3
              WHEN 'Tuesday' THEN 4
              WHEN 'Wednesday' THEN 5
              WHEN 'Thursday' THEN 6
              WHEN 'Tuesday' THEN 7 
          END grp
    FROM CTE
  ) t1
  GROUP BY grp
  having count(*) = 7
) t1

Results:

| fullweekAmount |
|----------------|
|              3 |

full year

;WITH CTE AS (
  SELECT  DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AS StartOfMonth,
          DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, -1)  - 1  AS EndOfMonth
  UNION ALL
  SELECT StartOfMonth +1 , EndOfMonth
  FROM CTE 
  WHERE StartOfMonth < EndOfMonth
)
SELECT COUNT(*) fullweekAmount from (
  SELECT 
      MIN(StartOfMonth) startdt,
      MAX(StartOfMonth) enddt
  FROM (
    SELECT *,
          ROW_NUMBER() OVER(ORDER BY StartOfMonth) - 
          CASE DateName(DW, StartOfMonth)
              WHEN 'Friday' THEN 0
              WHEN 'Saturday' THEN 1
              WHEN 'Sunday' THEN 2
              WHEN 'Monday' THEN 3
              WHEN 'Tuesday' THEN 4
              WHEN 'Wednesday' THEN 5
              WHEN 'Thursday' THEN 6
              WHEN 'Tuesday' THEN 7 
          END grp
    FROM CTE
  ) t1
  GROUP BY grp
  having count(*) = 7
) t1

option (maxrecursion 0)

sqlfiddle

Note

if your cte recursion date more than 100 you will get the error

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

you can set

option (maxrecursion 0)

The Case When number 0 and 1 mean your weekoff day

As per your code, you said that, this month has `3 Friday- Thursday weeks`..!! Am I correct.?? — Pugal, Sep 01 '18 at 10:11
can you please explain your eighth case, `WHEN 'Tuesday' THEN 7`..? — Pugal, Sep 01 '18 at 14:14
Yes but I am busy today, no time to write the full explain could you wait a moment. I am available tomorrow thanks. — D-Shih, Sep 01 '18 at 14:31
I am waiting. Okay I have no idea about `Gaps and Islands problem`. But I start learn about it. — Pugal, Sep 01 '18 at 14:39
@D-Shih we could solve this without taking into account the days between the start and end date..kindly look into my solution and provide feedback — Samuel A C, Sep 03 '18 at 15:28

score 2 · Answer 2 · answered Sep 03 '18 at 14:57

The alternative solution,

DECLARE @StartDate DATE='20200722'--any valid date as your requirement needs(it is inclusive of the specified date)
DECLARE @MonthorYear CHAR='Y'--Valid inputs: 'M'->for month and 'Y'-> for year
DECLARE @WeekdayBeginning nVarchar(15)='Sunday'
----Valid inputs run the following query, in my case it is english, for you it may differ based on your language
--SELECT DATENAME(dw, DATEADD(d,-1,'20180903')) Union all
--SELECT DATENAME(dw, DATEADD(d,0,'20180903')) Union all
--SELECT DATENAME(dw, DATEADD(d,1,'20180903')) Union all
--SELECT DATENAME(dw, DATEADD(d,2,'20180903')) Union all
--SELECT DATENAME(dw, DATEADD(d,3,'20180903')) Union all
--SELECT DATENAME(dw, DATEADD(d,4,'20180903')) Union all
--SELECT DATENAME(dw, DATEADD(d,5,'20180903')) 


DECLARE @week TABLE
(ID int identity(0,1),DoW nVarchar(15),isWeekstart bit default 0)

INSERT INTO @week (DoW)
SELECT DATENAME(dw, DATEADD(d,-1,'20180903')) Union all
SELECT DATENAME(dw, DATEADD(d,0,'20180903')) Union all
SELECT DATENAME(dw, DATEADD(d,1,'20180903')) Union all
SELECT DATENAME(dw, DATEADD(d,2,'20180903')) Union all
SELECT DATENAME(dw, DATEADD(d,3,'20180903')) Union all
SELECT DATENAME(dw, DATEADD(d,4,'20180903')) Union all
SELECT DATENAME(dw, DATEADD(d,5,'20180903')) 

-- input validations
if not exists (SELECT 1 FROM @week where  DoW=@WeekdayBeginning)
BEGIN
    PRINT 'Invalid @WeekdayBeginning input'
    RETURN
END 
if (@MonthorYear NOT IN ('M','Y'))
BEGIN
    PRINT 'Invalid @MonthorYear input'
    RETURN
END 

--Start of find logic
DECLARE @EndDate date
DECLARE @DaysMissed tinyint,@TempDays smallint,@Weeks tinyint

SELECT  @EndDate=
    case @MonthorYear WHEN 'M' THEN EOMONTH(@StartDate)
    ELSE DATEFROMPARTS(YEAR(@StartDate),12,31) END

UPDATE @week set isWeekstart=1 where Dow=@WeekdayBeginning
SELECT @TempDays=DATEDIFF(d,@StartDate,@EndDate)+1

if not exists(SELECT 1 FROM @week WHERE DATENAME(dw,@StartDate)=DoW and isWeekstart=1)
BEGIN
    DECLARE @tempID tinyint,@WeekstartID tinyint
    SELECT @tempID=ID from @week WHERE DATENAME(dw,@StartDate)=DoW
    SELECT @WeekstartID=ID from @week WHERE isWeekstart=1
    WHILE(@WeekstartID<>@tempID)
    BEGIN
        SET @TempDays=@TempDays-1
        SET @tempID=@tempID+1
        if(@tempID>6)
        BEGIN
            SET @tempID=0
        END
    END
END
SET @Weeks=@TempDays/7
SELECT @Weeks as 'CompleteWeeks'

This solution gives the count of weeks with simple math operations and max iteration of 6 rounds — Samuel A C, Sep 03 '18 at 15:33

calculating number of weeks in the basis of customized weekend

2 Answers2