Help with Counting of Time

[linuxjr]

Table Example
ID time_assigned date_assigned
-----------------------------------
1 10:30 am 06/30/02
2 11:30 am 06/29/02
3 12:00 pm 06/30/02

System clock is 12:00 pm 06/30/02

I have a few simple queries that I am trying to make and like all of us at one point or time I'm having sql script block. I am trying to count all the records that have a status of 'AA' and 'I' which I got:

select count(*) from [table name] where status = 'AA' and status = 'I' <-- this works great now. Out of those totals I have a time field in their of a time_assigned and I'm trying to count out of that total how many are less than five minutes, between 5-10 minutes, 10-15 minutes and over 15 minutes. The close I can come is

select datediff(mi,now(),time_assigned) from [table_name]

but I need to also take in account of the 24 hours so that if its been more than a day it will go in the over 15 minutes. I do have a date_assigned field which I'm using the same select statement but its:

select datediff(dd,now(),date_assigned) from [table_name] but I am stuck on how to create this part of if the time is less than five and not greater than a day then it goes to less than 5 etc etc.

So using the table above comparing the time and date with the time on the system's clock the answer should be this:

Total records = 3
less than 5 minutes = 1
between 5-10 minutes = 0
between 10-15 minutes = 0
over 15 minutes = 2


So any help or tips will be greatly appreciated. Have a great day.

 

[r937]

what are the datatypes of the time_assigned and date_assigned fields? what database is this running on? (ms access?)

rudy

 

[linuxjr]

The time_assigned is a time field and the date_assigned is a date field. I am using sybase as my database to test out my queries. I think I have something that I will have to take the time but also check the date so if its greater than one to add the extra minutes so it will be say instead of 60 minutes from 11:00 to 12:00 where the date_assigned for the 11:00 time is 06/29/02 and today is 06/30/02 the minutes should be 1500. So if you know of any ways I can be able to break it apart I will greatly appreciate it. Thanks and have a nice day.

 

[grega]

The easy (and sensible) way would be to combine time_assigned and date_assigned into a single datetime field.

Greg.

 

[linuxjr]

Grega,
You are absolutely right about the database field should be combined but I finally got it to work for me which is:
select datediff(mi,(date_assigned+time_assigned), now()) from [table name]. Thanks for everyone's help and hope you have a great day.