On Feb 6, 1:09 am, steven...@gmail.com wrote:
> On Feb 5, 3:35 pm, "Paul Lautman" <paul.laut...@btinternet.com> wrote:
>
>
>
>
>
> > steven...@gmail.com wrote:
> > > Hi Everyone,
>
> > > I have a solution to creating a report of our company's unit hours of
> > > activity (time spent serving a call) based on an accompanying call log
> > > table, however, my queries take HOURS and HOURS to run (8+). As a
> > > result I can't provide the report as often as the powers that be would
> > > like it. Our table contains more than 1 million records and sensitive
> > > data so I can't provide test data. :/
>
> > > Let me explain the situation.
>
> > > - Start and Stop times for each call are available
> > > USE stevensjn;
>
> > > DROP TABLE IF EXISTS `tbluhadata`;
> > > CREATE TABLE `tbluhadata` (
> > > `CallId` bigint(20) default NULL,
> > > `CallStart` datetime default NULL,
> > > `CallEnd` datetime default NULL,
> > > PRIMARY KEY (`CallId`)
> > > ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
>
> > > DROP TABLE IF EXISTS `_helper`;
> > > CREATE TABLE `_helper` (
> > > `i` tinyint(3) unsigned NOT NULL,
> > > PRIMARY KEY (`i`)
> > > ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
>
> > > DROP TABLE IF EXISTS `tbluhahours`;
> > > CREATE TABLE tbluhahours (
> > > StartDate datetime NOT NULL,
> > > EndDate datetime NOT NULL,
> > > PRIMARY KEY (`StartDate`,`EndDate`)
> > > ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
>
> > > CREATE TABLE _helper (i TINYINT UNSIGNED NOT NULL PRIMARY KEY);
> > > INSERT INTO _helper (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7),
> > > (8), (9);
>
> > > SET @counter := -1;
> > > SET @start := '2000-01-01 00:00:00';
> > > SET @finish := '2010-12-31 23:59:59';
> > > INSERT INTO tbluhahours (StartDate, EndDate)
> > > SELECT
> > > @start + INTERVAL H.c HOUR,
> > > @start + INTERVAL H.c + 1 HOUR - INTERVAL 1 SECOND
>
> > > FROM (
> > > SELECT @counter := @counter + 1 AS c
> > > FROM _helper h1 -- 10
> > > CROSS JOIN _helper h2 -- 100
> > > CROSS JOIN _helper h3 -- 1000
> > > CROSS JOIN _helper h4 -- 10000
> > > CROSS JOIN _helper h5 -- 100000
> > > ) H
>
> > > WHERE
> > > @start + INTERVAL H.c HOUR < @finish;
> > > ////////////////// end
>
> > > Here is a set of queries I have put together that uses joins to
> > > determine elapsed times for each hour. Perhaps optimizing these
> > > queries will be enough to get the script working at a workable speed?
> > > These are the 8 hour queries. Any suggestions at all would be
> > > fantastic.
>
> > > USE stevensjn;
>
> > > 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
> > > );
>
> > > Thanks for reading. With any luck someone has had to do something like
> > > this in the past, of will find the challenge interesting.
>
> > > - Jake
>
> > I seem to remember posting about this question the other day and saying that
> > group by was a better solution (I just checked and I certainly did, but you
> > never responded to my poost).
>
> Using your advice I went ahead and wrote what I thought was a great
> solution using a group by query. Unfortunately, I ended up with a
> query that would sum elapsed seconds for calls that began in each hour
> of the day, but with disregard for calls that overlapped more than a
> single hour. So for instance with an elapsed time of > 3600 seconds
> all of those seconds would be grouped into the hour that which the
> call began in.
>
> Is there some way of doing the group by taking into account the
> possibility that calls may be longer than 3600 seconds? I'm not sure
> how.
>
> I had something like this:
>
> SELECT
> MAX(Year(CallStart)) AS theYear,
> MAX(Month(CallStart)) AS theMonth,
> MAX(Hour(CallStart) AS theHour,
> COUNT(*) AS NumCalls,
> SUM(UNIX_TIMESTAMP(CallEnd)-UNIX_TIMESTAMP(CallStart)) AS
> ElapsedSecond
> FROM tblUHAData
> WHERE
> Year(CallStart)=2006
> GROUP BY
> Year(CallStart), Month(CallStart), Hour(CallStart)
> ORDER BY
> 1,2,3
>
> Which of course SUMs total elapsed second for calls that start in each
> of the hours of the 24 hour clock.
>
> Can we change the group by to somehow reflect the beginning and ends
> of each call? I would love to know how.
>
> Thanks.
>
> - Jake- Hide quoted text -
>
> - Show quoted text -

So a call that lasted 2.5 hours would appear in three of the hourly
slots?