Seperate time intervals by workday

Question

I have the following table (but with much more data):

Workcenter ID | Status | Start Date/Time | End Date/Time SLX83D Breakdown 11/27/2016 16:00:00 11/29/2016 12:30:00 SLX83C No Operator 11/27/2016 6:40:00 11/27/2016 13:00:00 SLX83A QA Inspection 11/28/2016 13:00:00 11/28/2016 14:00:00 SLX83A QA Inspection 11/28/2016 15:00:00 11/28/2016 16:00:00

I would like to separate the status time intervals by workday (ie 12/1 6am to 12/2 6am) and sum.

My idea was to create a crosstab query in the following format:

Work Day | Workcenter ID | Breakdown | No Operator | QA Inspection 11/27/2016 SLX83D 14:00:00 11/28/2016 SLX83D 24:00:00 11/29/2016 SLX83D 6:30:00 11/27/2016 SLX83C 6:20:00 11/28/2016 SLX83A 2:00:00

Couldn't figure how to make it work, I'm open to other ideas.

P.S. I also have the following columns in my table: Start date, Start time, End Date, and End time. (these were used to calculate 'start date/time' and 'end date/time'

I'm relatively new to Access and know next to nothing about SQL, thanks.

Edit: I was able to find the status for a given day by creating the following calculated columns in my table:

Is between Date: IIf([Start Date/Time]>= "11/28/2016 06:00:00" And [End Date/Time]<= "11/29/2016 06:00:00" ,[End Date/Time]-[Start Date/Time],0)*24*60

Starts Before Date: IIf([Start Date/Time]< "11/28/2016 06:00:00" And [End Date/Time]>= "11/28/2016 06:00:00" And [End Date/Time]<= "11/29/2016 06:00:00" ,[End Date/Time]- "11/28/2016 06:00:00" ,0)*24*60

Ends After Date: IIf([End Date/Time]> "11/28/2016 06:00:00" And [Start Date/Time]>= "11/28/2016 06:00:00" And [Start Date/Time]<= "11/29/2016 06:00:00" , "11/29/2016 06:00:00" -[Start Date/Time],0)*24*60

Spans Whole Date: IIf([Start Date/Time]< "11/28/2016 06:00:00" And [End Date/Time]> "11/29/2016 06:00:00" ,1,0)*24*60

Summing these columns gives the total number of minutes a given workcenter had a given status on a given day.
However, this only works for one workday date at a time. I would much rather figure out how use SQL to get the results like the table above for a history of workdays.

Workcenter ID | Status | Start Date/Time | End Date/Time SLX83D Breakdown 11/27/2016 16:00:00 11/29/2016 12:30:00 SLX83A QA Inspection 11/28/2016 13:00:00 11/28/2016 14:00:00 SLX83A QA Inspection 11/28/2016 15:00:00 11/28/2016 16:00:00

Workcenter ID | IBD | SBD | EAD | SWD | SUM SLX83D 1440 1440 (24 hrs) SLX83A 60 60 (1 hr) SLX83A 60 60 (These 2 hrs would have to be summed later somehow, this isn't a great solution)

Edit 2: I tried the following but can't get it to work. (credit goes to: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/cca28ddd-2041-4504-b602-3bedd9ca704e/counts-by-time-interval?forum=transactsql#208e97ad-11a7-4d96-9436-c82612755d46)

SELECT DatePart(Day,[Start Date/Time]) AS [Workday Date], Count([Status Table].[Workcenter ID]) AS [Workday Interval]

FROM [Status Table]

WHERE ((([Status Table].[Start Date/Time]) Between [Start Date/Time] And [End Date/Time]))

GROUP BY DatePart(Day,interval);

Answer 1

Consider handling your needs in multiple stages:

1. Build a Schedule table of all possible workdays start (first day 6:00 AM) and end points (next day 5:59 AM). This may require using VBA loop to dynamically create a new table (TableDef) from earliest to latest date in range if days are into the hundreds or thousands.

   

2. Union query combining all three possible durations: a) partial day start; b) multiple full day(s); c) partial day end. Each using nested IIF() logic to calculate hours. Each SELECT query will run a cross join and interval filter with Actual Hours table and above Schedule table.

3. Cross tab query to aggregate in transformed output grouped by WorkDates and ID, pivoted by Status columns.

Union Query (be sure to remove comments, not compliant in Access SQL query statement).

-- ACTUAL START PARTIAL
SELECT w.[Workcenter ID], w.Status, w.[StartDate/Time], w.[End Date/Time], 
       s.WorkStartDates, s.WorkEndDates,
       IIF(w.[StartDate/Time] >= s.WorkStartDates AND w.[End Date/Time] <= s.WorkEndDates,
         DateDiff('n', w.[StartDate/Time], w.[End Date/Time]), 
         IIF(w.[StartDate/Time] >= s.WorkStartDates AND w.[End Date/Time] >= s.WorkEndDates,
             DateDiff('n', w.[StartDate/Time], s.WorkEndDates), NULL)) / 60 AS WorkHours
FROM WorkCenterHours w, Schedule s
WHERE w.[StartDate/Time] BETWEEN s.WorkStartDates AND s.WorkEndDates

UNION

-- ACTUAL START AND END ACROSS WORKDAYS
SELECT w.[Workcenter ID], w.Status, w.[StartDate/Time], w.[End Date/Time], 
       s.WorkStartDates, s.WorkEndDates,
       DateDiff('n', s.WorkStartDates, s.WorkEndDates) / 60 As WorkHours
FROM WorkCenterHours w, Schedule s
WHERE s.WorkStartDates BETWEEN w.[StartDate/Time] AND w.[End Date/Time]
AND s.WorkEndDates BETWEEN w.[StartDate/Time] AND w.[End Date/Time]

UNION 

-- ACTUAL END LEFTOVER
SELECT w.[Workcenter ID], w.Status, w.[StartDate/Time], w.[End Date/Time],
       s.WorkStartDates, s.WorkEndDates,
       IIF(w.[StartDate/Time] <= s.WorkStartDates AND w.[End Date/Time] <= s.WorkEndDates,
         DateDiff('n', w.[End Date/Time], s.WorkEndDates), 
         IIF(w.[StartDate/Time] >= s.WorkStartDates AND w.[End Date/Time] <= s.WorkEndDates,
             DateDiff('n', w.[StartDate/Time], w.[End Date/Time]), NULL)) / 60 AS WorkHours
FROM WorkCenterHours w, Schedule s
WHERE w.[End Date/Time] BETWEEN s.WorkStartDates AND s.WorkEndDates;

Cross Tab Query (uses above as data source)

TRANSFORM Sum(q.WorkHours) AS SumOfWorkHours
SELECT DateValue(q.WorkStartDates) AS WorkDate, q.[Workcenter ID]
FROM UnionSourceQuery q
GROUP BY DateValue(q.WorkStartDates), q.[Workcenter ID]
PIVOT q.Status;

NOTE: performance is not guaranteed and may vary with size of data with this solution due to the cross join filters inside a union query using nested logic and various date function calls and ultimately is then pivoted.