On 2 Feb, 16:21, steven...@gmail.com wrote:
> Just for the record, I use the hours table for joining against to
> create a report of used hours of activity in a log of calls. So, if
> you have 20 scheduled hours during a particular hour (say 20 staff
> from 12-1), how much of that time is actually recorded as an active
> call. Then I use that data and group it by hours etc for statistical
> analysis.
>
> Its really quite complicated. If anyone is interested in the whole
> scenario I would love to share because I think there is a lot of room
> for improvement in my current solution, but it might be a time sink
> (or simple to some!).
>
> The volume of call data makes some of my queries run overnight. :/
>
> Here is the gist of it.
> CREATE TABLE tblUHADurations (
> SELECT
> tblUHAData.CallId,
> tblUHAData.CallStart,
> tblUHAData.CallEnd,
> tblUHAHours.StartDate,
> DATE_FORMAT(tblUHAHours.StartDate, "%H") AS Hour,
> UNIX_TIMESTAMP(tblUHAHours.EndDate)+1 -
> UNIX_TIMESTAMP(tblUHAData.CallStart) AS Duration
> FROM
> tblUHAData INNER JOIN tblUHAHours ON
> tblUHAHours.StartDate <= tblUHAData.CallStart AND
> tblUHAData.CallStart <= tblUHAHours.EndDate AND
> tblUHAHours.EndDate < tblUHAData.CallEnd
> );
>
> INSERT INTO tblUHADurations (
> SELECT
> tblUHAData.CallId,
> tblUHAData.CallStart,
> tblUHAData.CallEnd,
> tblUHAHours.StartDate,
> DATE_FORMAT(tblUHAHours.StartDate, "%H") AS Hour,
> 3600 AS Duration
> FROM
> tblUHAData INNER JOIN tblUHAHours ON
> tblUHAHours.StartDate > tblUHAData.CallStart AND
> tblUHAHours.EndDate < tblUHAData.CallEnd
> );
>
> INSERT INTO tblUHADurations (
> SELECT
> tblUHAData.CallId,
> tblUHAData.CallStart,
> tblUHAData.CallEnd,
> tblUHAHours.StartDate,
> DATE_FORMAT(tblUHAHours.StartDate, "%H") AS Hour,
> UNIX_TIMESTAMP(tblUHAData.CallEnd)+1 -
> UNIX_TIMESTAMP(tblUHAHours.StartDate) AS Duration
> FROM
> tblUHAData INNER JOIN tblUHAHours ON
> tblUHAHours.StartDate <= tblUHAData.CallEnd AND
> tblUHAData.CallEnd <= tblUHAHours.EndDate AND
> tblUHAHours.StartDate > tblUHAData.CallStart
> );
>
> INSERT INTO tblUHADurations (
> SELECT
> tblUHAData.CallId,
> tblUHAData.CallStart,
> tblUHAData.CallEnd,
> tblUHAHours.StartDate,
> DATE_FORMAT(tblUHAHours.StartDate, "%H") AS Hour,
> UNIX_TIMESTAMP(tblUHAData.CallEnd)+1 -
> UNIX_TIMESTAMP(tblUHAData.CallStart) AS Duration
> FROM
> tblUHAData INNER JOIN tblUHAHours ON
> tblUHAHours.StartDate <= tblUHAData.CallStart AND
> tblUHAData.CallEnd <= tblUHAHours.EndDate
> )

I have always used GROUP BY for stuff like that rather than joins to a
table containing all the hours.