Hourly volume calculation

G

Guest

Hello:

I have a table with among others fields, a date-time field and a volume
field consisting multiple volume entries. I need to sum up the volumes by the
hours.

For example, there may be a sum volume of 5000 between 16:00 to 16:59 hr
after totaling the different entries.

At the end of the day, I will have another table or query with 24 records
for the 24 hours.

Would anyone be able to assist with ideas or codes? Thanks. Best Regards.
 
T

Tom Ellison

Dear Zimme:

I recommend starting with a table representing the 24 hour period. It needs
only one column containing the values of the 24 hours in a day:

Table: Hours
Hr
0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

Now, are you creating one sum for everything within the table, or one for
each day in the table, or for just one day from the table, or what?

For everything within the table:

SELECT
(SELECT MAX(Hr)
FROM Hours
WHERE Hr < DateTime)
AS Hr,
DateTime
FROM YourTable

Fix this up for your table and column names (YourTable and DateTime). Does
this give a start? What more help might you need?

Tom Ellison
 
J

John Spencer

SELECT Hour(DateTimeField) as TheHour, Sum(Volume) as Total
FROM YourTable
GROUP GY Hour(DateTimeField)

Of course, if there is no data for a particular hour, the above will not return
a record. If that is a problem, it can be solved.
 
G

Guest

Dear John:

Thank you for a second alternative. Both methods are very helpful.

Best regards.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top