CODEWITHSUNDEEP
sqlsql-serverdatedayofweek
Timmo
Timmo

Reputation: 2334

SQL - Finding working days from days worked

We have a table that contains resources and the days' that they work. For example, one person could work Monday - Friday but another could only work two days of that week. Here is the data:

WorkOnSunday    WorkOnMonday    WorkOnTuesday   WorkOnWednesday WorkOnThursday  WorkOnFriday    WorkOnSaturday
--------------------------------------------------------------------------------------------------------------
0               1               1               1               1               1               0
0               0               1               1               0               0               0

(apologies for the lack of formatting but screenshots wont upload through our damn proxy.)

So the question is, how do I get the amount of days worked in a month using the data above? (Holidays are the next stage. I'm attempting to get a hold of the holidays table that we apparently have)

Here is what I have so far:

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2017/12/01'
SET @EndDate = '2018/01/01'


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

This gives me the correct amount of weekdays in the month

Upvotes: 2

Views: 75

Answers (1)

GuidoG
GuidoG

Reputation: 12014

you need to find out first how many of each day is occuring in your month,
the following query can help you with that.

declare @from datetime= '2017/12/01' 
declare @to datetime  = '2018/01/01'

select 
 datediff(day, -7, @to)/7-datediff(day, -6, @from)/7 AS MON,
 datediff(day, -6, @to)/7-datediff(day, -5, @from)/7 AS TUE,
 datediff(day, -5, @to)/7-datediff(day, -4, @from)/7 AS WED,
 datediff(day, -4, @to)/7-datediff(day, -3, @from)/7 AS THU,
 datediff(day, -3, @to)/7-datediff(day, -2, @from)/7 AS FRI,
 datediff(day, -2, @to)/7-datediff(day, -1, @from)/7 AS SAT,
 datediff(day, -1, @to)/7-datediff(day, 0, @from)/7 AS SUN

Once you have that you can do your counts in your table

Upvotes: 1

Related Questions