CODEWITHSUNDEEP
sqloracle-database
John
John

Reputation: 299

Count business days in month excluding holidays

I have the below table.

TableA

DaysInMonth    CalDate    CalendarMonth  MonthEndInd  CalDateMonth  WorkDay  HolidayInd
   31          3/26/2018     MAR 2018      N             3/31/2018     1         N
   31          3/25/2018     MAR 2018      N             3/31/2018     0         N

How can I calculate the number of WorkDays in a month? I'm not sure where to start, so I don't have any work to show.

Expected output 

DaysInMonth    CalDate    CalendarMonth  MonthEndInd  CalDateMonth  WorkDay  HolidayInd   WorkDaysInMonth
   31          3/26/2018     MAR 2018      N             3/31/2018     1         N
   31          3/25/2018     MAR 2018      N             3/31/2018     0         N

SQL I have the below but how can I add this to my query 

SELECT COUNT(*),C.CALENDAR_MONTH FROM HUM.CALENDAR C WHERE 1=1 AND C.WORKDAY = 1 GROUP BY C.CALENDAR_MONTH

Upvotes: 1

Views: 121

Answers (2)

BriteSponge
BriteSponge

Reputation: 1054

In much the same way as SUM you can also use COUNT if you want to limit it to just working days;

SELECT c.*,
       COUNT(*) OVER(PARTITION BY calendarmonth ORDER BY calendarmonth) WDIM
FROM   calendar
WHERE  workday = 1

Depends how you want to use it I guess.

Upvotes: 0

MT0
MT0

Reputation: 167811

You can use an analytic function:

SELECT c.*,
       SUM( WorkDay ) OVER ( PARTITION BY CalendarMonth ) AS WorkDaysInMonth
FROM   Calendar C;

SQLFIDDLE

Upvotes: 1

Related Questions