SQL for calculating hours per week

[former_SQLA_member1694869]

I need help with a SQL... I have a field with an available capacity per day for hours in table tblCapacity. With this field, I want to calculate the available capacity per week for hours. I will exclude weekends and the dates that are in my table tblExcludeDates. The current week is submitted as a where condition.

CREATE TABLE tblCapacity ( 
   WorkCenter VARCHAR(12), 
   Capacity  Numeric(16,2) );
INSERT tblCapacity VALUES ('WC1', 7.5 );
INSERT tblCapacity VALUES ('WC2', 8 );

COMMIT;

CREATE TABLE tblExcludeDates ( 
   ExceptionDate DATE, 
   Type  INTEGER );
INSERT tblExcludeDates VALUES ('2019-03-27', 1);
INSERT tblExcludeDates VALUES ('2019-04-24', 1);
INSERT tblExcludeDates VALUES ('2019-04-25', 1);
INSERT tblExcludeDates VALUES ('2019-05-01', 1);
INSERT tblExcludeDates VALUES ('2019-12-24', 1);
INSERT tblExcludeDates VALUES ('2019-12-25', 1);

COMMIT;

As an example, with my sql, I want to type type like this:

SELECT 
    WeekCapacity 
FROM 
    ...
WHERE
    WorkCenter = 'WC1' AND
    YearWeek = '1917'

WeekCapacity for week 17 in work center WC1 will then be 22.5 hours. If I specify week 19 in the condition, WeekCapacity will be 37,5 hours in work center WC1.

[VolkerBarth]

So what exactly is the problem you face?

[former_SQLA_member1694869]

The problem is that I don't understand how to calculate the capacity per week (in hours). That is, if I enter a week in the condition where I will count off a day (in hours) from the table tblExcludeDates and get it on the right week.

Week Capacity for YearWeek 1917 in work center WC1 is 22.5 hours if I manually calculate it .. But how can get it with a sql.

[VolkerBarth · Accepted solution]

Here's a starting point that lists the number of work days in the specified week.

begin
   declare strYearWeek varchar(4) = '1917';
   declare nYear int;
   declare nWeek int;
   declare dtNewYear date;
   set nYear = 2000 + cast(left(strYearWeek, 2) as int);
   set nWeek = cast(substr(strYearWeek, 3) as int);
   set dtNewYear = ymd(nYear, 1, 1);

   select count(*) as NumberOfWeekdays
   from
       (select cast(dateadd(day, numberOfDays.row_num, dtNewYear) as date)
                 as dtDayOfYear
          from sa_rowgenerator(0, datediff(day, dtNewYear, ymd(nYear +  1, 1, 1)) - 1)
                 numberOfDays
          where datepart(weekday, dtDayOfYear) between 2 and 6
             and datepart(calweekofyear, dtDayOfYear) = nWeek
       except
       select ExceptionDate
          from tblExcludeDates) DT
end;

returns 3 for '1917' and 5 for '1919'.

Some hints:

I'm using a "brute force" approach to list all days in the specified year and then check whether they fall into the according week.

• "datediff(day, dtNewYear, ymd(nYear + 1, 1, 1))" counts the number of days between the specified year's first day and that of the new year, thereby calculating this year's number of days, so 365 for 2019.

• "sa_rowgenerator(... -1) numberOfDays" then generates a row with a number for each day, starting with 0, so for 2019 up to 364.

• "select cast(dateadd(day, numberOfDays.row_num, dtNewYear) as date) as dtDayOfYear from ... numberOfDays" then lists all days in the according year.

• The WHERE clause checks via "datepart(weekday, dtDayOfYear) between 2 and 6" whether they are "working days" (assuming those are Monday -Friday, note that Sunday = 1)

• and checks via "datepart(calweekofyear, dtDayOfYear) = nWeek" whether they belong to the specified week. (Note, there are different definitions of when the first week of a year starts for different countries, so that may have to be adapted!) - So that query will usually return 5 days unless the week partly falls into another year.

• The EXCEPT is added to exclude those days from your table.

• The whole query is then used as a derived table and its result set is simply counted.

So finally you would just calculate the resulting "NumberOfWeekdays" with the capacity per Work Center.

[former_SQLA_member1694869 · Accepted solution]

Thank you very much! This is exactly the functionality I was looking for. Thanks also for your exhaustive description of the functionality.

One thing I missed is that the dates available tblException are not always full days. There is an additional column in the table that I missed (ExceptionDay) that tells how much of the day is the exception. 1 is full day, 0.5 is half day, 0.25 is 2h etc. Full day is always 8 hour. I also need to include this column in the calculation. Do you have any good suggestions on how to do it too?

The table looks like this:

CREATE TABLE tblExcludeDates ( 
   ExceptionDate DATE, 
   Type  INTEGER, ExceptionDay INTEGER );
INSERT tblExcludeDates VALUES ('2019-03-27', 1, 1);
INSERT tblExcludeDates VALUES ('2019-04-24', 1, 1);
INSERT tblExcludeDates VALUES ('2019-04-25', 1, 0.5);
INSERT tblExcludeDates VALUES ('2019-05-01', 1, 0.25);
INSERT tblExcludeDates VALUES ('2019-12-24', 1, 1);
INSERT tblExcludeDates VALUES ('2019-12-25', 1, 0.75);

[VolkerBarth · Accepted solution]

How does the "half day" relate to the different working hours per day in the different "Work Centers"? For WC1, you say the working day is 7.5 hours long, how long is it for "half days" and the like?

In other words: Are these "ExpectionDay" values

• percents of a "full day" in the according WC (so 0.5 per 7.5 = 3.75 whereas 0.5 * 8 = 4.0) or
• percents of 8 hours (so in both WCs the working hours would need to be reduced for 0.5 * 8 = 4.0)?

---

Besides that, that additional requirement makes the query way more complicated. I guess you would need a two-step approach:

1. Find out the according possible working days per week (i.e. without "subtracting" the exception days, just the days from MO-FR).
2. Count the maximum possible number for working hours for those 5 days and subtract the sum of the exception hours for the according week.

[former_SQLA_member1694869 · Accepted solution]

It was a bad example of me. A full working day is always 8h. Your example is excellent, but I can't make it work with part of a day. The field (ExceptionDay) that I missed in table tblExcludeDates I do not understand how to use. Did you have any idea how to do this in the best way?

[VolkerBarth · Accepted solution]

Hm, you have not yet answered my question...

[former_SQLA_member1694869 · Accepted solution]

Excuse my faintness.

Half day is 4 hours. Full day is 8 hours ExpectionDay is always for hours. What is in that field is the number of hours on that day. Max 8 hours for full day.

[VolkerBarth]

Well, Rolle, your latest details about the excluding day portions don't fit the extended table tblExcludeDates schema, so I tried to use the original values, except that field "ExceptionDay" must apparently be a DECIMAL to store decimal values.

I assume "ExceptionDay" = 0.25 means that at that day, the full working day is reduced to 3/4 of a full day, meaning 2 hours less capacity than normal for WorkCenters with 8 hour capacity and 1.875 houers less for those with 7.5 hour capacity:

CREATE TABLE tblExcludeDates ( 
   ExceptionDate DATE, 
   Type  INTEGER, ExceptionDay Numeric(16,2) );
INSERT tblExcludeDates VALUES ('2019-03-27', 1, 1);
INSERT tblExcludeDates VALUES ('2019-04-24', 1, 1);
INSERT tblExcludeDates VALUES ('2019-04-25', 1, 0.5);
INSERT tblExcludeDates VALUES ('2019-05-01', 1, 0.25);
INSERT tblExcludeDates VALUES ('2019-12-24', 1, 1);
INSERT tblExcludeDates VALUES ('2019-12-25', 1, 0.75);

The provided solution uses a "stack" of common table expresssions to build query blocks that are combined to answer the question.

The result set returns three values, apparently you only need the third.

begin
   declare strYearWeek varchar(4) = '1917';
   declare nYear int;
   declare nWeek int;
   declare dtNewYear date;
   declare varWorkCenter varchar(12) = 'WC1';
   set nYear = 2000 + cast(left(strYearWeek, 2) as int);
   set nWeek = cast(substr(strYearWeek, 3) as int);
   set dtNewYear = ymd(nYear, 1, 1);

   -- CTE_Workweek: returns all work days of the specified week
   with CTE_Workweek as
         (select cast(dateadd(day, numberOfDays.row_num, dtNewYear) as date)
             as dtDayOfYear
          from sa_rowgenerator(0, datediff(day, dtNewYear, ymd(nYear +  1, 1, 1)) - 1)
             numberOfDays
          where datepart(weekday, dtDayOfYear) between 2 and 6
             and datepart(calweekofyear, dtDayOfYear) = nWeek),
   -- CTE_WeekRange: returns date of first and last work day of that week
      CTE_WeekRange as
         (select min(dtDayOfYear) as dtFirstDay, max(dtDayOfYear) as dtLastDay
          from CTE_Workweek),
   -- CTE_TotalCapacity: returns full capacity per WorkCenter of that week
   -- without regarding exceptions
      CTE_TotalCapacity as
         (select isnull(sum(Capacity), 0) as TotalCapacity
          from CTE_Workweek cross join tblCapacity
          where WorkCenter = varWorkCenter),
   -- CTE_ExludeCapacity: returns sum of excluding hours per WorkCenter of that week
      CTE_ExludeCapacity as
       (select isnull(sum(ExceptionDay * Capacity), 0) as ExludeCapacity
          from CTE_WeekRange cross join tblExcludeDates cross join tblCapacity
          where ExceptionDate between dtFirstDay and dtLastDay
             and WorkCenter = varWorkCenter)

   select TotalCapacity, ExludeCapacity, TotalCapacity - ExludeCapacity as RealCapacity
   from CTE_TotalCapacity cross join CTE_ExludeCapacity     
end;

For week 2019-17, this returns

• 37.50, 11.25, 26,25 for WorkCenter 1 and
• 40.00, 12.00, 28.00 for WorkCenter 2.

[former_SQLA_member1694869]

You are my hero. Works perfectly! Thanks again.