June 30, 2009 at 5:22 am
Hi friends,
i am just wondering how can i get number of activity from the table which is having date and time of last 7 days and activity.
I need to get average number of activity during last 2 hours of last 7 days.
So if it is 11 oclock now and when i run the query i need to know the number of activity today between 9 to 11 and yesterday between 9 to 11 and day before yesterday between 9 to 11 etc...then i can take the average...
any suggestion?
My query is as below:
-- time difference between arrival and assesment
select
convert(float, datediff(mi, convert(datetime, (convert(varchar, arrival_date) + ' ' + convert(varchar, arrival_time))), convert(datetime, (convert(varchar, arrival_date) + ' ' + convert(varchar, initial_assesment_time))))), arrival_date, initial_assesment_time
from
Table_1
where
initial_assesment_time is not null
and
convert(datetime, (convert(varchar, arrival_date) + ' ' + convert(varchar, arrival_time))) between dateadd(hh, -2, getdate()) and getdate()
thanks,
Vijay
July 6, 2009 at 8:49 pm
I'm having a little trouble visualize what you are attempting to accomplish, and I have looked at this several times over the past week. If you could provide the DDL for the tables (CREATE TABLE statements), sample data (in a readily consummable format that can be cut/paste/run in SSMS) to load into the tables, the expected results based on the sample data that a query should return (ie, not looking for you to write the code to create the results, just do that manually to your specs), and the code you have written so far that you are having difficulties with.
Any questions you may have on this request can be answered by reading and following the guidelines in the first article referenced below in my signature block regarding asking for assistance.
July 7, 2009 at 7:09 am
I believe this is what you are trying to achieve,
select
convert(float, datediff(mi, convert(datetime, (convert(varchar, arrival_date) + ' ' + convert(varchar, arrival_time))), convert(datetime, (convert(varchar, arrival_date) + ' ' + convert(varchar, initial_assesment_time))))), arrival_date, initial_assesment_time
from
Table_1
where
initial_assesment_time is not null
and
datediff(hour, convert(datetime, (convert(varchar, arrival_date) + ' ' + convert(varchar, arrival_time))), getdate()) % 24 <= 2
NM
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply