Calculating the sum of hours / day with overlapping periods, SQL
I have a table with start date (event.startdate), end date (event.enddate) and hour / person (event.hrday) of the event. I have another table listing on weekdays that has a different field for each person (calendar.name). I want to fill in these columns with the total hours worked each day. I cannot figure out how to sum the hours correctly, if two events overlap in dates, I can come up with the correct value for one event in a certain period of time.
I believe that in theory this question has the answer I need: calculate the sum of values associated with overlapping date ranges
But I'm very new to SQL and I don't fundamentally understand the solution posted even after some more research. I am using Access 2013. Sorry if this is a super basic question, I was hoping that what I wanted to do could be handled "visually" with Access ...
What do I have: (table "event")
Startdate | Enddate | Hrsday | Name
5/1/2015 5/12/2015 1.25 Joe
5/7/2015 5/8/2015 8 Joe
What I'm looking for: (table "calendar", days already filled in first column)
Weekdays | Joe | name2 | name3 | ....
5/1/2015 1.25
5/4/2015 1.25
5/5/2015 1.25
5/6/2015 1.25
5/7/2015 9.25
5/8/2015 9.25
5/11/2015 1.25
5/12/2015 1.25
I tried using the in-access query builder to create an UPDATE query, but my result either does not show up at all (no updates, all nulls), or populates one event with no overlaps. (5 / 1-5 / 12 all have 1.25).
source to share
I think you will need to create a "date table" if you want this kind of result in MS-Access (without using window functions).
Here's a quick example of how this might work in SQL Server, but only using the syntax available for MS-Access (hopefully).
--Load the test data into a table variable
DECLARE @event TABLE (
[start_date] DATE,
end_date DATE,
hrsperday NUMERIC(19,2),
name VARCHAR(20));
INSERT INTO @event SELECT '20150401', '20150412', 1.25, 'Joe';
INSERT INTO @event SELECT '20150407', '20150408', 8, 'Joe';
--Add some more test data, to make it more "interesting"
INSERT INTO @event SELECT '20150401', '20150405', 0.1, 'Bill';
INSERT INTO @event SELECT '20150401', '20150430', 7.5, 'Bill';
INSERT INTO @event SELECT '20150412', '20150415', 0.5, 'Bill';
--Make a date table, this creates one on the fly but wouldn't work in MS-Access
--I store a date for each day in 2015/Apr, obviously I would want more dates eventually
DECLARE @dates TABLE (
[date] DATE);
WITH cte AS (
SELECT CONVERT(DATE, '20150401') AS [date]
UNION ALL
SELECT DATEADD(DAY, 1, [date]) FROM cte WHERE [date] < '20150430')
INSERT INTO
@dates
SELECT
[date]
FROM
cte OPTION (MAXRECURSION 0);
--Now the answer is trivial
SELECT
e.name,
d.[date],
SUM(hrsperday) AS hrs
FROM
@dates d
LEFT JOIN @event e ON d.[date] BETWEEN e.[start_date] AND e.end_date
GROUP BY
e.name,
d.[date]
ORDER BY
e.name,
d.[date];
--Note the format you want, but a PIVOT would give you this
--(I don't think PIVOT is supported by MS-Access though)
Results for this:
name date hrs
Bill 2015-04-01 7.60
Bill 2015-04-02 7.60
Bill 2015-04-03 7.60
Bill 2015-04-04 7.60
Bill 2015-04-05 7.60
Bill 2015-04-06 7.50
Bill 2015-04-07 7.50
Bill 2015-04-08 7.50
Bill 2015-04-09 7.50
Bill 2015-04-10 7.50
Bill 2015-04-11 7.50
Bill 2015-04-12 8.00
Bill 2015-04-13 8.00
Bill 2015-04-14 8.00
Bill 2015-04-15 8.00
Bill 2015-04-16 7.50
Bill 2015-04-17 7.50
Bill 2015-04-18 7.50
Bill 2015-04-19 7.50
Bill 2015-04-20 7.50
Bill 2015-04-21 7.50
Bill 2015-04-22 7.50
Bill 2015-04-23 7.50
Bill 2015-04-24 7.50
Bill 2015-04-25 7.50
Bill 2015-04-26 7.50
Bill 2015-04-27 7.50
Bill 2015-04-28 7.50
Bill 2015-04-29 7.50
Bill 2015-04-30 7.50
Joe 2015-04-01 1.25
Joe 2015-04-02 1.25
Joe 2015-04-03 1.25
Joe 2015-04-04 1.25
Joe 2015-04-05 1.25
Joe 2015-04-06 1.25
Joe 2015-04-07 9.25
Joe 2015-04-08 9.25
Joe 2015-04-09 1.25
Joe 2015-04-10 1.25
Joe 2015-04-11 1.25
Joe 2015-04-12 1.25
source to share