MS Access Calculate seconds by hour

Question

I have a table in an Access Database where the only information that i have is:

start_time (date & time);
end_time (date & time);
duration (int, seconds);

What i need to have in my output is how many time spent in each hour bracket.

One example:

start_time: 2016/07/01 09:00:00
end_time: 2016/07/03 09:00:00

in this example, the pretended output is:

Date      | Hour | Duration
2016/07/01   9     3600
2016/07/01   10    3600
2016/07/01   11    3600
2016/07/01   12    3600
   ...       ...    ...
2016/07/03   8     3600

Thanks in advance.

Answer 1

If you only count in full hours, this will do:

PARAMETERS 
    StartDate DateTime, 
    EndDate DateTime;
SELECT 
    DateValue(DateAdd("h",10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10),[StartDate])) AS [Date], 
    Hour(DateAdd("h",10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10),[StartDate])) AS [Hour], 
    DateDiff("s",
        DateAdd("h",10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10),[StartDate]),
        DateAdd("h",10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10)+1,[StartDate])) AS Duration
FROM 
    MsysObjects AS Uno, 
    MsysObjects AS Deca
GROUP BY 
    DateValue(DateAdd("h",10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10),[StartDate])), 
    Hour(DateAdd("h",10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10),[StartDate])), 
    DateDiff("s",
        DateAdd("h",10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10),[StartDate]),
        DateAdd("h",10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10)+1,[StartDate])), DateAdd("h",10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10),[StartDate])
HAVING 
    DateAdd("h",10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10),[StartDate])<=[EndDate];

Edit:

You can include your table (Durations) directly:

SELECT 
    DateValue(DateAdd("h",10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10),[StartDate])) AS [Date], 
    Hour(DateAdd("h",10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10),[StartDate])) AS [Hour], 
    DateDiff("s",
        DateAdd("h",10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10),[StartDate]),
        DateAdd("h",10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10)+1,[StartDate])) AS Duration
FROM 
    MsysObjects AS Uno, 
    MsysObjects AS Deca, 
    Durations
WHERE 
    DateAdd("h",10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10),[StartDate])<=[EndDate]
GROUP BY 
    DateValue(DateAdd("h",10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10),[StartDate])), 
    Hour(DateAdd("h",10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10),[StartDate])), 
    DateDiff("s",
        DateAdd("h",10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10),[StartDate]),
        DateAdd("h",10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10)+1,[StartDate]));

To respect minutes and seconds:

SELECT 
    DateValue(DateAdd("h",10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10),[StartDate])) AS [Date], 
    Hour(DateAdd("h",10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10),[StartDate])) AS [Hour], 
    DateDiff("s",
        IIf(DateDiff("h",[StartDate],DateAdd("h",10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10),[StartDate]))=0,
            [StartDate],
            DateValue([StartDate])+TimeSerial(Hour([StartDate])+10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10),0,0)),
        IIf(DateDiff("h",[EndDate],DateAdd("h",10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10),[StartDate]))=0,
            [EndDate],
            DateValue([StartDate])+TimeSerial(Hour([StartDate])+10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10)+1,0,0))) AS Duration
FROM 
    MsysObjects AS Uno, 
    MsysObjects AS Deca, 
    Durations
WHERE 
    DateAdd("h",10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10),[StartDate])<[EndDate]
GROUP BY 
    DateValue(DateAdd("h",10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10),[StartDate])), 
    Hour(DateAdd("h",10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10),[StartDate])), 
    DateDiff("s",
        IIf(DateDiff("h",[StartDate],DateAdd("h",10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10),[StartDate]))=0,
            [StartDate],
            DateValue([StartDate])+TimeSerial(Hour([StartDate])+10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10),0,0)),
        IIf(DateDiff("h",[EndDate],DateAdd("h",10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10),[StartDate]))=0,
            [EndDate],
            DateValue([StartDate])+TimeSerial(Hour([StartDate])+10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10)+1,0,0)));

Answer 2

I've managed to add the table that i want and its running. However, i may not be clear when i asked the first question.

What i want is the duration breakdown by hour. And the query you're giving to me will return always 3600 as a duration (no matter what time is in start and end date).

This is a real example of 1 record that i have in my table:

campaign    agent   start_time              end_time
279          542    23/05/2016  17:05:21    24/05/2016 09:58:54

With this record, what i want is to know this:

date        hour duration
23/05/2016  17   3279
23/05/2016  18   3600
23/05/2016  19   3600
23/05/2016  20   3600
23/05/2016  21   3600
23/05/2016  22   3600
23/05/2016  23   3600
24/05/2016  0    3600
24/05/2016  1    3600
24/05/2016  2    3600
24/05/2016  3    3600
24/05/2016  4    3600
24/05/2016  5    3600
24/05/2016  6    3600
24/05/2016  7    3600
24/05/2016  8    3600
24/05/2016  9    3534

Please note that in first and last row i have the difference between start_time and the exact next hour.