×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Counting values in time period.

Counting values in time period.

Counting values in time period.

(OP)
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,

RE: Counting values in time period.

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

RE: Counting values in time period.

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

RE: Counting values in time period.

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

RE: Counting values in time period.

(OP)
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.

 

RE: Counting values in time period.

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

RE: Counting values in time period.

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
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close