SEO

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

- a call can span several hours or occur inside of one hour
- summing the difference between timestamps in a group by query gives
a sum on time that BEGAN in a particular hour but doesn't figure in
that after 3600 seconds, you are in a different hour!

I typed this all out and accidentally pasted over much of the
explanation I hard originally enterred. My tip for any other posted,
author your post in Word where you can UNDO!

Thanks to all of you for reading, and for any advice you can leave me.

stevensjn@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).

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

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?

On Feb 6, 5:34 am, "Captain Paralytic" <paul_laut...@yahoo.com> wrote:
> 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?

No, the group by would only count it for the hour that in which it
started, and then for the entire duration of the call. So a 2.5 hour
call would SUM the entire duration of that call and add it to the
grouping of the hour in which it started. This would add an extra 1.5
hours of time to that hour, a bad thing.

On 6 Feb, 15:38, steven...@gmail.com wrote:
> On Feb 6, 5:34 am, "Captain Paralytic" <paul_laut...@yahoo.com> wrote:
>
>
>
>
>
> > 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?
>
> No, the group by would only count it for the hour that in which it
> started, and then for the entire duration of the call. So a 2.5 hour
> call would SUM the entire duration of that call and add it to the
> grouping of the hour in which it started. This would add an extra 1.5
> hours of time to that hour, a bad thing. - Hide quoted text -
>
> - Show quoted text -

Confused.
Are you saying that for a 2.5 hour call you want (upto) 60 minutes
counted in the hour slot in which the call began and the rest of the
time you don't want counted at all? So if a 2.5 hour call started at
14:55 you would only ever count 5 minutes of the call?

>
> Confused.
> Are you saying that for a 2.5 hour call you want (upto) 60 minutes
> counted in the hour slot in which the call began and the rest of the
> time you don't want counted at all? So if a 2.5 hour call started at
> 14:55 you would only ever count 5 minutes of the call?


I need to count all time that occurs servicing calls, but for the hour
that in which it occurred. If a Call starts at 11:30:00 and ends at
13:30:00, I need to count the 30 minutes for the 11:00 hour, the 60
minutes for the 12:00 hour and the 30 minutes for the 13:00 hour. This
way I will be collecting ALL time used for each hour in the
appropriate hours that the time servicing occurred in.

Here is an excerpt from a query that shows the result set I would like
to receive from the query:

Month, Hour, Total Elapsed Seconds of Calls Serviced During Row's Hour

January, 0, 123456
January, 1, 123456
.....
January, 31, 123456
February, 1, 123456
....
February, 28, 123456

Comparing this data versus a staff schedule allows you to determine
your efficiency in scheduling vehicles in a logistics company, or in
this case, an EMS service.

Thanks a lot for spending time working through this with me.
Ultimately it will have a great impact on our ability to have
ambulances available at the right times of the day to save people's
lives.

Regards,

Jake

On 6 Feb, 16:26, steven...@gmail.com wrote:
> > Confused.
> > Are you saying that for a 2.5 hour call you want (upto) 60 minutes
> > counted in the hour slot in which the call began and the rest of the
> > time you don't want counted at all? So if a 2.5 hour call started at
> > 14:55 you would only ever count 5 minutes of the call?
>
> I need to count all time that occurs servicing calls, but for the hour
> that in which it occurred. If a Call starts at 11:30:00 and ends at
> 13:30:00, I need to count the 30 minutes for the 11:00 hour, the 60
> minutes for the 12:00 hour and the 30 minutes for the 13:00 hour. This
> way I will be collecting ALL time used for each hour in the
> appropriate hours that the time servicing occurred in.
>
> Here is an excerpt from a query that shows the result set I would like
> to receive from the query:
>
> Month, Hour, Total Elapsed Seconds of Calls Serviced During Row's Hour
>
> January, 0, 123456
> January, 1, 123456
> ....
> January, 31, 123456
> February, 1, 123456
> ...
> February, 28, 123456
>
> Comparing this data versus a staff schedule allows you to determine
> your efficiency in scheduling vehicles in a logistics company, or in
> this case, an EMS service.
>
> Thanks a lot for spending time working through this with me.
> Ultimately it will have a great impact on our ability to have
> ambulances available at the right times of the day to save people's
> lives.
>
> Regards,
>
> Jake

A couple of posts ago, I said "So a call that lasted 2.5 hours would
appear in three of the hourly slots?"
Your reply in the following post was "No"

Now you are saying "If a Call starts at 11:30:00 and ends at
13:30:00, I need to count the 30 minutes for the 11:00 hour, the 60
minutes for the 12:00 hour and the 30 minutes for the 13:00 hour.
This
way I will be collecting ALL time used for each hour in the
appropriate hours that the time servicing occurred in."

Which is what I said and you said no to!

I misread your post in thinking that you asking at that time about how
the QUERY actually worked and responded no, because it would actually
count all of the time for the hour that it started in.

My apologies, I only responded to what I thought I was being asked. :/

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

Yes.