Sum Interval Data

[rstemper]

I have worked in Excel for years but now find that I need to perform a query
in Access because of database size limitations where the total lines exceed
the Excel limit. The database column headings are shown below, but the
Interval_DateTime and Interval_Data are the subject of my query. The data
is provided in 15 minute intervals starting with 00:00. Several years of
data may be provided. So the database looks like the following:


ID Acct Nbr Interval_DateTime Interval_Length Interval_usage

1 137264594 8/28/2002 15 234

2 137264594 8/28/2002 12:15:00 AM 15 234

3 137264594 8/28/2002 12:30:00 AM 15 234.58

4 137264594 8/28/2002 12:45:00 AM 15 235.44

5 137264594 8/28/2002 1:00:00 AM 15 236.86

Etc.

What I need to do is to sum the 15 minute intervals into hourly intervals
(00:00 - 00:45, 01:00 - 01:45, etc). The end result being a table of daily
usage by hour for each account number - generally there will be only one
account number for each record set but I'd like to have the code recognize
account numbers because there may be rare cases where the table includes
more than one account number. I thought I'd be able to find an example by
googling newsgroups but I have not been successful. I'd prefer to see the
SQL code to help me develop an understanding of the procedure.

Thank you, your assistance is greatly appreciated.

 

[Guest]

1. Does the IntervalTime indicate the start or end of the interval? Does
12:00 mean from 12:00 to 12:15, or 11:45 to 12:00? If the latter, where does
this interval's usage go, hour 11 or hour12?
2. Does it matter if two successive times in the table differ by some value
other than IntervalLength?
3. What happens if an interval spans over HH:00:00?

Assuming: 1. End; 2. Don't care; 3. Doesn't happen, then:

Add a column to your table, let's call it Hour, of type Date/Time
Run this update query:
UPDATE YourTable SET [Hour] = DateAdd("n", -DatePart("n", Interval_Date),
Interval_Date);

This subracts the minutes part out of the timestamp, resulting in an "HourID"
(If intervals can end at other than even minutes, you might want to modify
the above to also subtract the seconds part.)

This query will aggregate by account within hours:

SELECT Hour, AccountNumber, SUM(IntervalUsage)
FROM YourTable
GROUP BY Hour, AccountNumber
ORDER BY Hour, AccountNumber;

 

[Guest]

A variation, if you don't want to modify the table by adding a field:

SELECT DateAdd("n", -DatePart("n", IntervalDate), IntervalDate)
AS [Hour], Acct, SUM(Usage)
FROM test
GROUP BY DateAdd("n", -DatePart("n", IntervalDate), IntervalDate), Acct
ORDER BY DateAdd("n", -DatePart("n", IntervalDate), IntervalDate), Acct;

Not as pretty to read and harder to maintain, and I'm not sure if Jet SQL is
smart enough to avoid evaluating the functions three times.

 

[John Spencer]

Assumptions:
-- Interval_DateTime is a datetime field
-- Interval_Length can always be Completed within the hour specified (or you
don't care). So there is no such record as
137264594 8/28/2002 12:45:00 AM 30 235.44

SELECT [Acct Nbr]
, DateValue(Interval_DateTime) as IntDate
, Hour(Interval_DateTime) as IntHour
, Sum (Interval_Length) as HourlySum
FROM YOUR TABLE
WHERE Interval_DateTime is Not Null
GROUP BY [Acct Nbr]
, DateValue(Interval_DateTime)
, Hour(Interval_DateTime)

If you don't want to have a daily total, but just totals by hour, then drop
the DateValue calculations. If the length of the interval matters then you
can post back and perhaps someone can come up with a solution that will
adjust for this problem.

 

[Guest]

John: Neat alternative solution. Some comments:
The SUM should be taken on Interval_Usage, Not Interval_Length - probably
just a typo.
DateValue *is* necessary whether or not daily totals are required -
otherwise all the usages from 12 - 1 AM would aggregate across dates!
If daily totals are necessary, I would do that by grouping in a report.
Does anyone know if JET is smart enough to evaluate the functions only once?
--
Ted

Assumptions:
-- Interval_DateTime is a datetime field
-- Interval_Length can always be Completed within the hour specified (or you
don't care). So there is no such record as
137264594 8/28/2002 12:45:00 AM 30 235.44

SELECT [Acct Nbr]
, DateValue(Interval_DateTime) as IntDate
, Hour(Interval_DateTime) as IntHour
, Sum (Interval_Length) as HourlySum
FROM YOUR TABLE
WHERE Interval_DateTime is Not Null
GROUP BY [Acct Nbr]
, DateValue(Interval_DateTime)
, Hour(Interval_DateTime)

If you don't want to have a daily total, but just totals by hour, then drop
the DateValue calculations. If the length of the interval matters then you
can post back and perhaps someone can come up with a solution that will
adjust for this problem.

I have worked in Excel for years but now find that I need to perform a
query in Access because of database size limitations where the total lines
exceed the Excel limit. The database column headings are shown below, but
the Interval_DateTime and Interval_Data are the subject of my query. The
data is provided in 15 minute intervals starting with 00:00. Several years
of data may be provided. So the database looks like the following:


ID Acct Nbr Interval_DateTime Interval_Length
Interval_usage

1 137264594 8/28/2002 15 234

2 137264594 8/28/2002 12:15:00 AM 15 234

3 137264594 8/28/2002 12:30:00 AM 15 234.58

4 137264594 8/28/2002 12:45:00 AM 15 235.44

5 137264594 8/28/2002 1:00:00 AM 15 236.86

Etc.

What I need to do is to sum the 15 minute intervals into hourly intervals
(00:00 - 00:45, 01:00 - 01:45, etc). The end result being a table of
daily usage by hour for each account number - generally there will be only
one account number for each record set but I'd like to have the code
recognize account numbers because there may be rare cases where the table
includes more than one account number. I thought I'd be able to find an
example by googling newsgroups but I have not been successful. I'd prefer
to see the SQL code to help me develop an understanding of the procedure.

Thank you, your assistance is greatly appreciated.

 

[BobS]

The Interval_DateTime is a datetime field
Interval_length is alway a 15 minute interval

I changed the ",Sum(Interval_Length) as HourlySum"
to ",Sum(Interval_Usage) as HourlySum" and got the answer I was looking for.
I did not get any Daily Totals even though I used the DateValue
calculations.

My only concern (and I need to verify this) is that as it's being done now
hours 00:00, 00:15, 00:30 and 00:45 are being added to become 00:00

If, as I suspect (subject to verification) these are actually hour ending
data, then
I would need to add 23:15, 23:30, 23:45 and 00:00 to get the data for hour
ending
at midnight. And 00:15, 00:30, 00:45 and 01:00 to get the data for hour
ending at
1 AM. etc.

Any suggestions?

Also, when I run the query a dialog box pops up asking for the Acc_Nbr. Can
the
code be modified to provide the data summary by hour for all Acc_Nbr's in
the table
without asking for an input?

Really appreciate the assistance!!!!!

Assumptions:
-- Interval_DateTime is a datetime field
-- Interval_Length can always be Completed within the hour specified (or
you don't care). So there is no such record as
137264594 8/28/2002 12:45:00 AM 30 235.44

SELECT [Acct Nbr]
, DateValue(Interval_DateTime) as IntDate
, Hour(Interval_DateTime) as IntHour
, Sum (Interval_Length) as HourlySum
FROM YOUR TABLE
WHERE Interval_DateTime is Not Null
GROUP BY [Acct Nbr]
, DateValue(Interval_DateTime)
, Hour(Interval_DateTime)

If you don't want to have a daily total, but just totals by hour, then
drop the DateValue calculations. If the length of the interval matters
then you can post back and perhaps someone can come up with a solution
that will adjust for this problem.

I have worked in Excel for years but now find that I need to perform a
query in Access because of database size limitations where the total lines
exceed the Excel limit. The database column headings are shown below, but
the Interval_DateTime and Interval_Data are the subject of my query. The
data is provided in 15 minute intervals starting with 00:00. Several
years of data may be provided. So the database looks like the following:


ID Acct Nbr Interval_DateTime Interval_Length Interval_usage

1 137264594 8/28/2002 15 234

2 137264594 8/28/2002 12:15:00 AM 15 234

3 137264594 8/28/2002 12:30:00 AM 15 234.58

4 137264594 8/28/2002 12:45:00 AM 15 235.44

5 137264594 8/28/2002 1:00:00 AM 15 236.86

Etc.

What I need to do is to sum the 15 minute intervals into hourly intervals
(00:00 - 00:45, 01:00 - 01:45, etc). The end result being a table of
daily usage by hour for each account number - generally there will be
only one account number for each record set but I'd like to have the code
recognize account numbers because there may be rare cases where the table
includes more than one account number. I thought I'd be able to find an
example by googling newsgroups but I have not been successful. I'd prefer
to see the SQL code to help me develop an understanding of the procedure.

Thank you, your assistance is greatly appreciated.

 

[BobS]

Thank you for your assistance. When I ran this code I got a dialog box
asking for the IntervalDate and then a dialog box asking for the Acc_Nbr.
The query result was one line identifying the account number and sum of
total usage. I think I'm on the right track with the code provided by John
Spencer. Really appreciate your input. Thank you very much.

A variation, if you don't want to modify the table by adding a field:

SELECT DateAdd("n", -DatePart("n", IntervalDate), IntervalDate)
AS [Hour], Acct, SUM(Usage)
FROM test
GROUP BY DateAdd("n", -DatePart("n", IntervalDate), IntervalDate), Acct
ORDER BY DateAdd("n", -DatePart("n", IntervalDate), IntervalDate), Acct;

Not as pretty to read and harder to maintain, and I'm not sure if Jet SQL
is
smart enough to avoid evaluating the functions three times.
--
Ted

I have worked in Excel for years but now find that I need to perform a
query
in Access because of database size limitations where the total lines
exceed
the Excel limit. The database column headings are shown below, but the
Interval_DateTime and Interval_Data are the subject of my query. The
data
is provided in 15 minute intervals starting with 00:00. Several years of
data may be provided. So the database looks like the following:


ID Acct Nbr Interval_DateTime Interval_Length
Interval_usage

1 137264594 8/28/2002 15 234

2 137264594 8/28/2002 12:15:00 AM 15 234

3 137264594 8/28/2002 12:30:00 AM 15 234.58

4 137264594 8/28/2002 12:45:00 AM 15 235.44

5 137264594 8/28/2002 1:00:00 AM 15 236.86

Etc.

What I need to do is to sum the 15 minute intervals into hourly intervals
(00:00 - 00:45, 01:00 - 01:45, etc). The end result being a table of
daily
usage by hour for each account number - generally there will be only one
account number for each record set but I'd like to have the code
recognize
account numbers because there may be rare cases where the table includes
more than one account number. I thought I'd be able to find an example by
googling newsgroups but I have not been successful. I'd prefer to see the
SQL code to help me develop an understanding of the procedure.

Thank you, your assistance is greatly appreciated.

 

[John Spencer]

If it is asking you for Acct_Nbr, that means you don't have a field with
that name in the query. So check the spelling of the field name (extra
spaces, missing spaces, No underscore, etc)

SELECT [Acct Nbr]
, DateValue(Interval_DateTime) as IntDate
, Hour(Interval_DateTime) as IntHour
, Sum (Interval_USAGE) as HourlySum
FROM [YOURTABLE]
WHERE Interval_DateTime is Not Null
GROUP BY [Acct Nbr]
, DateValue(Interval_DateTime)
, Hour(Interval_DateTime)

Hour returns the hour of the day from 0 to 23

You can adjust the date with the date add function. It may be as simple as
adding minus 1 minute (or even one second) to force the 00:00 times into the
previous hour.

DateValue(DateAdd("n",-1,Interval_DateTime))

The Interval_DateTime is a datetime field
Interval_length is alway a 15 minute interval

I changed the ",Sum(Interval_Length) as HourlySum"
to ",Sum(Interval_Usage) as HourlySum" and got the answer I was looking
for.
I did not get any Daily Totals even though I used the DateValue
calculations.

My only concern (and I need to verify this) is that as it's being done now
hours 00:00, 00:15, 00:30 and 00:45 are being added to become 00:00

If, as I suspect (subject to verification) these are actually hour ending
data, then
I would need to add 23:15, 23:30, 23:45 and 00:00 to get the data for hour
ending
at midnight. And 00:15, 00:30, 00:45 and 01:00 to get the data for hour
ending at
1 AM. etc.

Any suggestions?

Also, when I run the query a dialog box pops up asking for the Acc_Nbr.
Can the
code be modified to provide the data summary by hour for all Acc_Nbr's in
the table
without asking for an input?

Really appreciate the assistance!!!!!

Assumptions:
-- Interval_DateTime is a datetime field
-- Interval_Length can always be Completed within the hour specified (or
you don't care). So there is no such record as
137264594 8/28/2002 12:45:00 AM 30 235.44

SELECT [Acct Nbr]
, DateValue(Interval_DateTime) as IntDate
, Hour(Interval_DateTime) as IntHour
, Sum (Interval_Length) as HourlySum
FROM YOUR TABLE
WHERE Interval_DateTime is Not Null
GROUP BY [Acct Nbr]
, DateValue(Interval_DateTime)
, Hour(Interval_DateTime)

If you don't want to have a daily total, but just totals by hour, then
drop the DateValue calculations. If the length of the interval matters
then you can post back and perhaps someone can come up with a solution
that will adjust for this problem.

I have worked in Excel for years but now find that I need to perform a
query in Access because of database size limitations where the total
lines exceed the Excel limit. The database column headings are shown
below, but the Interval_DateTime and Interval_Data are the subject of my
query. The data is provided in 15 minute intervals starting with 00:00.
Several years of data may be provided. So the database looks like the
following:


ID Acct Nbr Interval_DateTime Interval_Length
Interval_usage

1 137264594 8/28/2002 15 234

2 137264594 8/28/2002 12:15:00 AM 15 234

3 137264594 8/28/2002 12:30:00 AM 15 234.58

4 137264594 8/28/2002 12:45:00 AM 15 235.44

5 137264594 8/28/2002 1:00:00 AM 15 236.86

Etc.

What I need to do is to sum the 15 minute intervals into hourly
intervals (00:00 - 00:45, 01:00 - 01:45, etc). The end result being a
table of daily usage by hour for each account number - generally there
will be only one account number for each record set but I'd like to have
the code recognize account numbers because there may be rare cases where
the table includes more than one account number. I thought I'd be able
to find an example by googling newsgroups but I have not been
successful. I'd prefer to see the SQL code to help me develop an
understanding of the procedure.

Thank you, your assistance is greatly appreciated.

 

[BobS]

Thanks you so very much for your help!!!!

Think I got it.

If it is asking you for Acct_Nbr, that means you don't have a field with
that name in the query. So check the spelling of the field name (extra
spaces, missing spaces, No underscore, etc)

SELECT [Acct Nbr]
, DateValue(Interval_DateTime) as IntDate
, Hour(Interval_DateTime) as IntHour
, Sum (Interval_USAGE) as HourlySum
FROM [YOURTABLE]
WHERE Interval_DateTime is Not Null
GROUP BY [Acct Nbr]
, DateValue(Interval_DateTime)
, Hour(Interval_DateTime)

Hour returns the hour of the day from 0 to 23

You can adjust the date with the date add function. It may be as simple
as adding minus 1 minute (or even one second) to force the 00:00 times
into the previous hour.

DateValue(DateAdd("n",-1,Interval_DateTime))

The Interval_DateTime is a datetime field
Interval_length is alway a 15 minute interval

I changed the ",Sum(Interval_Length) as HourlySum"
to ",Sum(Interval_Usage) as HourlySum" and got the answer I was looking
for.
I did not get any Daily Totals even though I used the DateValue
calculations.

My only concern (and I need to verify this) is that as it's being done
now
hours 00:00, 00:15, 00:30 and 00:45 are being added to become 00:00

If, as I suspect (subject to verification) these are actually hour ending
data, then
I would need to add 23:15, 23:30, 23:45 and 00:00 to get the data for
hour ending
at midnight. And 00:15, 00:30, 00:45 and 01:00 to get the data for hour
ending at
1 AM. etc.

Any suggestions?

Also, when I run the query a dialog box pops up asking for the Acc_Nbr.
Can the
code be modified to provide the data summary by hour for all Acc_Nbr's in
the table
without asking for an input?

Really appreciate the assistance!!!!!

Assumptions:
-- Interval_DateTime is a datetime field
-- Interval_Length can always be Completed within the hour specified (or
you don't care). So there is no such record as
137264594 8/28/2002 12:45:00 AM 30 235.44

SELECT [Acct Nbr]
, DateValue(Interval_DateTime) as IntDate
, Hour(Interval_DateTime) as IntHour
, Sum (Interval_Length) as HourlySum
FROM YOUR TABLE
WHERE Interval_DateTime is Not Null
GROUP BY [Acct Nbr]
, DateValue(Interval_DateTime)
, Hour(Interval_DateTime)

If you don't want to have a daily total, but just totals by hour, then
drop the DateValue calculations. If the length of the interval matters
then you can post back and perhaps someone can come up with a solution
that will adjust for this problem.



I have worked in Excel for years but now find that I need to perform a
query in Access because of database size limitations where the total
lines exceed the Excel limit. The database column headings are shown
below, but the Interval_DateTime and Interval_Data are the subject of my
query. The data is provided in 15 minute intervals starting with 00:00.
Several years of data may be provided. So the database looks like the
following:


ID Acct Nbr Interval_DateTime Interval_Length
Interval_usage

1 137264594 8/28/2002 15 234

2 137264594 8/28/2002 12:15:00 AM 15 234

3 137264594 8/28/2002 12:30:00 AM 15 234.58

4 137264594 8/28/2002 12:45:00 AM 15 235.44

5 137264594 8/28/2002 1:00:00 AM 15 236.86

Etc.

What I need to do is to sum the 15 minute intervals into hourly
intervals (00:00 - 00:45, 01:00 - 01:45, etc). The end result being a
table of daily usage by hour for each account number - generally there
will be only one account number for each record set but I'd like to
have the code recognize account numbers because there may be rare cases
where the table includes more than one account number. I thought I'd be
able to find an example by googling newsgroups but I have not been
successful. I'd prefer to see the SQL code to help me develop an
understanding of the procedure.

Thank you, your assistance is greatly appreciated.

 

[David Cox]

Does anyone know if JET is smart enough to evaluate the functions only

once?

I do not, but it might be interesting to try and find out by entering a
complex function that resolves to a known constant, and timing queries where
the function is used always , or sometimes a mix of constant and functions.

John: Neat alternative solution. Some comments:
The SUM should be taken on Interval_Usage, Not Interval_Length - probably
just a typo.
DateValue *is* necessary whether or not daily totals are required -
otherwise all the usages from 12 - 1 AM would aggregate across dates!
If daily totals are necessary, I would do that by grouping in a report.
Does anyone know if JET is smart enough to evaluate the functions only
once?
--
Ted

Assumptions:
-- Interval_DateTime is a datetime field
-- Interval_Length can always be Completed within the hour specified (or
you
don't care). So there is no such record as
137264594 8/28/2002 12:45:00 AM 30 235.44

SELECT [Acct Nbr]
, DateValue(Interval_DateTime) as IntDate
, Hour(Interval_DateTime) as IntHour
, Sum (Interval_Length) as HourlySum
FROM YOUR TABLE
WHERE Interval_DateTime is Not Null
GROUP BY [Acct Nbr]
, DateValue(Interval_DateTime)
, Hour(Interval_DateTime)

If you don't want to have a daily total, but just totals by hour, then
drop
the DateValue calculations. If the length of the interval matters then
you
can post back and perhaps someone can come up with a solution that will
adjust for this problem.

I have worked in Excel for years but now find that I need to perform a
query in Access because of database size limitations where the total
lines
exceed the Excel limit. The database column headings are shown below,
but
the Interval_DateTime and Interval_Data are the subject of my query.
The
data is provided in 15 minute intervals starting with 00:00. Several
years
of data may be provided. So the database looks like the following:


ID Acct Nbr Interval_DateTime Interval_Length
Interval_usage

1 137264594 8/28/2002 15 234

2 137264594 8/28/2002 12:15:00 AM 15 234

3 137264594 8/28/2002 12:30:00 AM 15 234.58

4 137264594 8/28/2002 12:45:00 AM 15 235.44

5 137264594 8/28/2002 1:00:00 AM 15 236.86

Etc.

What I need to do is to sum the 15 minute intervals into hourly
intervals
(00:00 - 00:45, 01:00 - 01:45, etc). The end result being a table of
daily usage by hour for each account number - generally there will be
only
one account number for each record set but I'd like to have the code
recognize account numbers because there may be rare cases where the
table
includes more than one account number. I thought I'd be able to find an
example by googling newsgroups but I have not been successful. I'd
prefer
to see the SQL code to help me develop an understanding of the
procedure.

Thank you, your assistance is greatly appreciated.