|
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. |
|
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.
So finally you would just calculate the resulting "NumberOfWeekdays" with the capacity per Work Center. 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);
(20 Feb '19, 14:06)
Rolle
Replies hidden
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
Besides that, that additional requirement makes the query way more complicated. I guess you would need a two-step approach:
(25 Feb '19, 04:00)
Volker Barth
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?
(26 Feb '19, 16:03)
Rolle
Replies hidden
Hm, you have not yet answered my question...
(27 Feb '19, 02:48)
Volker Barth
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.
(27 Feb '19, 16:39)
Rolle
|
|
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
You are my hero. Works perfectly! Thanks again.
(03 Mar '19, 15:58)
Rolle
|
So what exactly is the problem you face?
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.