CODEWITHSUNDEEP
sqlsql-serverdatabaset-sql
Serhan Altug
Serhan Altug

Reputation: 71

How do I calculate Workdays except Weekends in SQL query?

I want to calculate which of my holidays are conflict with working days. I have a Holidays table which is below.

NameOfHoliday StartDate DurationByDay
Christmas     26.12.2015 5

26 and 27 are at weekend. So this shouldt be calculated. So I have to get only 4 days as result.

Upvotes: 1

Views: 104

Answers (2)

user3583912
user3583912

Reputation: 1322

I Hope your requirement is like this..

if object_id('tempdb..#Holidays') is not null drop table #Holidays
create table #Holidays(id int identity(1,1),Holiday date)
insert into #Holidays values ('2015-12-25'),('2015-12-28')
set dateformat ymd
declare @StartDate date = '2015-12-01'
declare @EndtDate date = '2015-12-31'
--some times @EndtDate might be NULL so, set to getdate()
set @EndtDate = isnull(@EndtDate,cast(getdate() as date))

declare @Holiday int 
set @Holiday = (select count(*) from #Holidays where Holiday between @StartDate and @EndtDate)

SELECT
   (DATEDIFF(dd, @StartDate, @EndtDate) + 1)
  -(DATEDIFF(wk, @StartDate, @EndtDate) * 2)
  -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
  -(CASE WHEN DATENAME(dw, @EndtDate) = 'Saturday' THEN 1 ELSE 0 END)
  -@Holiday

Upvotes: 0

Devart
Devart

Reputation: 122042

DECLARE @t TABLE(
   NameOfHoliday VARCHAR(10),
   StartDate DATE,
   DurationByDay SMALLINT
)

INSERT INTO @t
VALUES ('Christmas', '20151226', 5)

;WITH cte AS
(
    SELECT *, cnt = 0
    FROM @t

    UNION ALL

    SELECT t2.NameOfHoliday, DATEADD(DAY, t1.cnt + 1, t2.StartDate), t2.DurationByDay, t1.cnt + 1
    FROM cte t1
    JOIN @t t2 ON t1.NameOfHoliday = t2.NameOfHoliday
    WHERE t1.cnt < t1.DurationByDay
)
SELECT NameOfHoliday, StartDate, DT
FROM (
    SELECT *
        , DT = DATENAME(DW, StartDate)
        , RowNum = ROW_NUMBER() OVER (PARTITION BY StartDate ORDER BY NameOfHoliday)
    FROM cte
) t
WHERE RowNum = 1
    AND DT NOT IN ('Saturday', 'Sunday')
OPTION (MAXRECURSION 0)

output - 

NameOfHoliday StartDate  DT
------------- ---------- ------------------
Christmas     2015-12-28 Monday
Christmas     2015-12-29 Tuesday
Christmas     2015-12-30 Wednesday
Christmas     2015-12-31 Thursday

Upvotes: 1

Related Questions