Create Table of Sequential Hours

Hi all,

I was wondering if anyone knew of a way to fill a table with rows of
sequential hours spanning a number of years using a query or set of
queries.

What I am looking for is a way to mimic this VBA code I use in Access
to create the table currently. I'll post the VBA at the bottom of the
post. What I am looking for is the following:

CREATE TABLE tbluhahours (
StartDate datetime NOT NULL,
EndDate datetime NOT NULL,
PRIMARY KEY (`StartDate`,`EndDate`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO tblUHAHours (StartDate, EndDate)
VALUES ('2000-01-01 00:00:00' , '2000-01-01 00:59:59');
INSERT INTO tblUHAHours (StartDate, EndDate)
VALUES ('2000-01-01 01:00:00' , '2000-01-01 01:59:59');

But would l need a row for every hour of every day for say, from 2000
to 2010. Is it possible to script this somehow?

This is the VBA I used:

Private Sub makeTable_Click()
Dim theDate As Date
Dim theTempDate As Date
Dim endDate As Date
Dim rsInsert As Recordset
Dim strSQL As String
Dim db As Database

Set db = CurrentDb

theDate = "2000-12-01 00:00:00"
theTempDate = "2000-12-01 00:59:59"

endDate = "2007-01-01 00:00:00"

While theDate < endDate
strSQL = "INSERT INTO tblUHAHours (StartDate, EndDate) VALUES
(#" & _
Format(theDate, "yyyy-mm-dd hh:mm:ss") & "#, #" & _
Format(theTempDate, "yyyy-mm-dd hh:mm:ss") & "#)"
If DatePart("yyyy", theDate) < DatePart("yyyy", endDate) Then
db.Execute (strSQL)
End If
theDate = DateAdd("s", 3600, theDate)
theTempDate = DateAdd("s", 3600, theTempDate)
' MsgBox strSQL & " | " & theDate & " | " & theTempDate
Wend
End Sub

Thanks so much for any ideas, or advice you can share!

- Jake

On 2 Feb, 16:04, steven...@gmail.com wrote:
> Hi all,
>
> I was wondering if anyone knew of a way to fill a table with rows of
> sequential hours spanning a number of years using a query or set of
> queries.
>
> What I am looking for is a way to mimic this VBA code I use in Access
> to create the table currently. I'll post the VBA at the bottom of the
> post. What I am looking for is the following:
>
> CREATE TABLE tbluhahours (
> StartDate datetime NOT NULL,
> EndDate datetime NOT NULL,
> PRIMARY KEY (`StartDate`,`EndDate`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
>
> INSERT INTO tblUHAHours (StartDate, EndDate)
> VALUES ('2000-01-01 00:00:00' , '2000-01-01 00:59:59');
> INSERT INTO tblUHAHours (StartDate, EndDate)
> VALUES ('2000-01-01 01:00:00' , '2000-01-01 01:59:59');
>
> But would l need a row for every hour of every day for say, from 2000
> to 2010. Is it possible to script this somehow?
>
> This is the VBA I used:
>
> Private Sub makeTable_Click()
> Dim theDate As Date
> Dim theTempDate As Date
> Dim endDate As Date
> Dim rsInsert As Recordset
> Dim strSQL As String
> Dim db As Database
>
> Set db = CurrentDb
>
> theDate = "2000-12-01 00:00:00"
> theTempDate = "2000-12-01 00:59:59"
>
> endDate = "2007-01-01 00:00:00"
>
> While theDate < endDate
> strSQL = "INSERT INTO tblUHAHours (StartDate, EndDate) VALUES
> (#" & _
> Format(theDate, "yyyy-mm-dd hh:mm:ss") & "#, #" & _
> Format(theTempDate, "yyyy-mm-dd hh:mm:ss") & "#)"
> If DatePart("yyyy", theDate) < DatePart("yyyy", endDate) Then
> db.Execute (strSQL)
> End If
> theDate = DateAdd("s", 3600, theDate)
> theTempDate = DateAdd("s", 3600, theTempDate)
> ' MsgBox strSQL & " | " & theDate & " | " & theTempDate
> Wend
> End Sub
>
> Thanks so much for any ideas, or advice you can share!
>
> - Jake

What's wrong with the script you've got?

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
)

On Feb 2, 11:18 am, "Captain Paralytic" <paul_laut...@yahoo.com>
wrote:
> On 2 Feb, 16:04, steven...@gmail.com wrote:
>
>
>
> > Hi all,
>
> > I was wondering if anyone knew of a way to fill a table with rows of
> > sequential hours spanning a number of years using a query or set of
> > queries.
>
> > What I am looking for is a way to mimic this VBA code I use in Access
> > to create the table currently. I'll post the VBA at the bottom of the
> > post. What I am looking for is the following:
>
> > CREATE TABLE tbluhahours (
> > StartDate datetime NOT NULL,
> > EndDate datetime NOT NULL,
> > PRIMARY KEY (`StartDate`,`EndDate`)
> > ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
>
> > INSERT INTO tblUHAHours (StartDate, EndDate)
> > VALUES ('2000-01-01 00:00:00' , '2000-01-01 00:59:59');
> > INSERT INTO tblUHAHours (StartDate, EndDate)
> > VALUES ('2000-01-01 01:00:00' , '2000-01-01 01:59:59');
>
> > But would l need a row for every hour of every day for say, from 2000
> > to 2010. Is it possible to script this somehow?
>
> > This is the VBA I used:
>
> > Private Sub makeTable_Click()
> > Dim theDate As Date
> > Dim theTempDate As Date
> > Dim endDate As Date
> > Dim rsInsert As Recordset
> > Dim strSQL As String
> > Dim db As Database
>
> > Set db = CurrentDb
>
> > theDate = "2000-12-01 00:00:00"
> > theTempDate = "2000-12-01 00:59:59"
>
> > endDate = "2007-01-01 00:00:00"
>
> > While theDate < endDate
> > strSQL = "INSERT INTO tblUHAHours (StartDate, EndDate) VALUES
> > (#" & _
> > Format(theDate, "yyyy-mm-dd hh:mm:ss") & "#, #" & _
> > Format(theTempDate, "yyyy-mm-dd hh:mm:ss") & "#)"
> > If DatePart("yyyy", theDate) < DatePart("yyyy", endDate) Then
> > db.Execute (strSQL)
> > End If
> > theDate = DateAdd("s", 3600, theDate)
> > theTempDate = DateAdd("s", 3600, theTempDate)
> > ' MsgBox strSQL & " | " & theDate & " | " & theTempDate
> > Wend
> > End Sub
>
> > Thanks so much for any ideas, or advice you can share!
>
> > - Jake
>
> What's wrong with the script you've got?

Well I need Access to run it and then I have to export the table to
MySQL. I would prefer to do everything in MySQL with a script or query
if possible.

Other parts of the solution I have to a larger problem includes
queries that never complete in Access. I have another reply post that
outlines the bigger problem below.

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.

stevensjn@gmail.com wrote in news:1170432275.685818.65760
@p10g2000cwp.googlegroups.com:

> I was wondering if anyone knew of a way to fill a table with rows of
> sequential hours spanning a number of years using a query or set of
> queries.

USE test;

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:00';
INSERT INTO tbluhahours (StartDate, EndDate)
SELECT
DATE(@start) + INTERVAL H.c HOUR,
DATE(@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;



--
felix

Thank you felix, that is a fantastic script.

I'll go look up how this works.

Thank you very much.

stevensjn@gmail.com wrote in news:1170440839.065111.183270
@a75g2000cwd.googlegroups.com:


> I'll go look up how this works.

Unfortunately, there is a (small) bug. You should leave out the DATE()
calls (leftovers from testing).
And the finish datetime should be '2010-12-31 23:59:59'.

--
felix