Counting values in time period.

[arfasih]

Hello,

I have a table with two fields. procudts and DateTime.
how can I count the number of procudts on each hour ?
from 6:00 AM until 6:00 PM for example.

Table 1
---Products---|---DateTime----
A 2009-10-20 6.00.01
A 2009-10-20 6.10.10
B 2009-10-20 6.30.31
C 2009-10-20 7.00.41
C 2009-10-20 7.10.21
B 2009-10-20 7.35.31
B 2009-10-20 7.50.05
A 2009-10-20 8.00.20
A 2009-10-20 8.24.04
B 2009-10-20 9.04.02


Output is like this table:

---TimePriod----|---Product Count----
6-7 3
7-8 4
8-9 2

instead of this time period format, I can assign simple numbers az a index.


Thank you so much,

 

[r937]

i must ask you once again, which database system is this? sql server?

because you posted in the ANSI SQL forum

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[arfasih]

I'm using MS Access SQL and ADO connection in Delphi.

 

[JarlH]

Date/time handling is one of the worst areas when it comes to ANSI/ISO compliance - a lot of DBMS products are NOT compliant, they have their own vendor specific handling.

Make sure your DBMS has implemented ANSI SQL date/time before asking in this forum. (Otherwise go to a product specific forum.)

 

[arfasih]

Thank you for your answer.
Instead of Date/Time, I can use seconde also.
I access to the unix_seconde fields, which is absolute seconde.
I want to count products in the priode of 3600 secode.

Thanks again.

 

[r937]

to make things easier for you, why don't you ask your question in forum701

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[simian336]

I think this would be close. It is just missing the sorting by date.

Hour([datetime]) is going to give you your hour blocks. Then just group it.

SELECT Hour([datetime]) AS Hour, Count(Table1.pro) AS CountOfpro
FROM Table1
GROUP BY Hour([datetime])
ORDER BY Hour([datetime]);

Simi

 

[simian336]

SELECT Format([datetime],"Short Date") AS Shortdate, Hour([dt]) AS [datetime], Count(Table1.pro) AS CountOfpro
FROM Table1
GROUP BY Format([datetime],"Short Date"), Hour([dt])
ORDER BY Hour([datetime]);

This adds in the date

Simi