2

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

Timmo
  • 2,266
  • 4
  • 34
  • 54
  • this could do with normalising. Have a subtable with the following fields instead: "Resource ID", "DayOfWeek", "Works" (where Works is a boolean 0/1 field, and "DayofWeek" is a number representing the day of the week. Primary key is a compound of Resource ID and Day of Week. This should then make it a lot easier to write useful queries relating to this data. You may also need to refine it with other things, e.g. start/end dates to allow you to understand changes in working patterns over time. What you have got now is inflexible and won't allow you to get the info you need, not easily anyway – ADyson Oct 09 '17 at 15:41
  • It seems like important information is missing from your first table -- specifically Resource ID and WeekStarting or Ending date. Do you have this information too? – Ryan B. Oct 09 '17 at 15:41
  • Post your correct table structure – Amit Kumar Singh Oct 09 '17 at 15:44
  • Yes I have Resource ID etc. but I would be running this command on a resource by resource, month by month basis. ie. ResourceID 1 for the month of december etc. – Timmo Oct 09 '17 at 15:45
  • Do you have week ID column also. Table structure will definitely make it very easy. – Amit Kumar Singh Oct 09 '17 at 15:48
  • Do you have freedom to add tables? If so, I suggest you add a calendar table that provides a map of the working weeks in each month (one row for each (part-)week in a month), which you can use to join with the resources work table. That would avoid the on-the-fly calculations in your select statements and, possibly usefully, help with things like public holidays and weekend days where different rules might apply. It would be quite easy to generate the records for the table running into the foreseeable future. – JohnRC Oct 09 '17 at 16:31

1 Answers1

1

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

GuidoG
  • 11,359
  • 6
  • 44
  • 79
  • Thanks this helps. I can probably use this against the other data to count the amount of days worked – Timmo Oct 09 '17 at 15:48
  • That is indeed the idea,I am sure you will get it worked out now – GuidoG Oct 09 '17 at 15:49
  • Got it working. I used this to get the amount of days in the month, then added to the counter if the resource works on that day. – Timmo Oct 09 '17 at 16:10