Get working days in SQL Server

问题:

I have posted this problem ago but I guess I wasn't able to be as clear as I should have. I want to calculate difference between 2 dates let's say 2018/01/01 and 2018/01/31.

I have a table Calendar which is storing what day is a holiday and what is not. I am marking Sunday and Saturday as holidays, and want to calculate the working days remaining which should be 23.

But what actually the problem is that I can also mark Sunday as holiday and not Saturday.

That's why I want to use this table as well. I see so many solutions that are giving me 23 result because they are marking Sunday and Saturday as holiday which is fine too but I want it to be using this table as well.

I tried much but I'm sure where I am going wrong. Maybe somebody can help now.

Table is like this

CREATE TABLE Calendar
(
    Day Varchar(25), --name of the day i.e Sunday, Saturday
    IsOffDay Binary  --1 for Yes and 0 for False
)

So if I mark Saturday and Sunday as holiday the result should be 23 days but if I only mark Sunday as holiday and make Saturday inactive then it should be 27 days

The proposed solution that one mate says is possible duplicate is getting Saturday and Sunday as holiday but I don't want that to be hard coded or whatever you say.

回答1:

Try with this query .

Your Table :

 CREATE TABLE #Calendar
    ([Day] Varchar(25), --name of the day i.e Sunday, Saturday
     IsOffDay BIT  --1 for Yes and 0 for False
     )

    INSERT INTO #Calendar ([Day],IsOffDay)
    SELECT 'Sunday',1  union
    SELECT 'Saturday',0

Query

DECLARE @STARTDATE  DATE='2018-01-01',@ENDDATE  DATE='2018-01-31'

;WITH CTE AS (
SELECT @STARTDATE  AS STARTDATE
UNION ALL
select DATEADD(D,1,STARTDATE) 
FROM CTE
WHERE STARTDATE <@ENDDATE
)
,WORKINGDAYS AS (
SELECT STARTDATE,DATENAME(DW,STARTDATE)WEEKDAYS,C1.Day AS isweekend
FROM CTE c
left JOIN #Calendar C1 ON DATENAME(DW,STARTDATE)=C1.Day AND C1.IsOffDay=1
)

SELECT COUNT(WEEKDAYS)as WORKINGDAYS FROM WORKINGDAYS WHERE isweekend is null

Note: The above query gives you Working Days =27 .If you want saturday as holiday Update Calendar table IsOffDay=1 where [Day]='Saturday' then gives you Working Days =23

回答2:

Is this helpfull.?

    CREATE TABLE #Calendar
    (
        [Day] Varchar(25), --name of the day i.e Sunday, Saturday
        IsOffDay BIT  --1 for Yes and 0 for False
    )

    INSERT INTO #Calendar ([Day],IsOffDay)
    SELECT 'Sunday',1
    union
    SELECT 'Saturday',0

    --SElect * from #Calendar

    DECLARE @StartDate DATETIME='01/01/2018'
    DECLARE @EndDate DATETIME='01/31/2018'



    SELECT  TOP (DATEDIFF(DAY, @StartDate, @EndDate) + 1)
            DATENAME(dw,DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @StartDate)) as [Day]
    INTO #tmpDays
    FROM    sys.all_objects a
            CROSS JOIN sys.all_objects b;


    --SELECT * FROM #tmpDays

    SELECT COUnt(*) as WorkingDays
    FROM #tmpDays t
    LEFT JOIN #Calendar c on t.day=c.Day
    WHERE (c.Day IS NULL)
            OR
         (c.IsOffDay =0)    


    Drop Table #Calendar
    drop table #tmpDays

回答3:

GEneral approach would be to make a calendar table that lists all the days for few years and marks them as holidays under certain law areas - you know it won't be big table as for a 10y period it's under 4000 rows. In this case you have very flexible solution that will handle not only weekends but also holidays, ad-hoc holidays like - I don't know - day of mourning or whatever, Easter etc.

But if your heart is set on omitting certain weekdays then -

CREATE TABLE Calendar (
  DayOfWeek tinyint, --name of the day i.e 7 for Sunday, 6 for Saturday, 1 for Monday
  IsOffDay Binary  --1 for Yes and 0 for False
)

insert into Calendar (DayOfWeek, IsOffDay) values (7,1),(6,1)

And query:

set datefirst 1
declare @startDate date = '2018-01-01', @endDate date = '2018-01-31'
;
with numberOfDays as (
    select datediff(day, @startDate, @endDate) + 1 /*including start end end*/ as days 
),
numberOfHolidays as (
    select FLOOR(days/7) + case when DayOfWeek between datepart(dw, @startDate) and datepart(dw, @endDate) or DayOfWeek between datepart(dw, @endDate) and datepart(dw, @startDate) then 1 else 0 end as holidays
    from numberOfDays, Calendar
    where IsOffDay = 1
),
aggregatedHolidays as (
    select sum(holidays) as holidays from numberOfHolidays
)
select days - holidays
from numberOfDays, aggregatedHolidays