SQL for calculating hours per week

[Former Member]

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 Member]

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]

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 Member]

You are my hero. Works perfectly! Thanks again.