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