March 10, 2011 at 8:55 am
i hav a table in which am entering answers .....i want to calculate answer /hour....means tht on a specific date how many answers i enterd in the first hour...how many in the 2nd hour and so on for the whole day...
March 10, 2011 at 9:00 am
i think we'd need a CREATE TABLE definition and a few rows of sample data;
for the most part, i think it's joining a Calendar table to get all possible hours (ie 7am to 7pm, or 12am to 11pm) to a query of your data grouped by hour(somedatefield) to get the counts and overall totals.
Lowell
March 10, 2011 at 9:05 am
i am trying to do it like this but this is not working
select Count(AnswerID) AS T
FROM answers WHERE DateDiff("hh", '2011-03-10', ReceivedOn) <=1
where Receivedon is the time wen the answer is recieved.....
March 10, 2011 at 9:08 am
Please read Lowell's post.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 10, 2011 at 9:13 am
well, this will get you the hours where at least one answer exists i think;
if you need to know that zero answers were answered at 3am, that's why you'd need a calendar table:
select
Count(AnswerID) AS T,
datediff(hh,ReceivedOn,DATEADD(dd, DATEDIFF(dd,0,ReceivedOn), 0)) AS WhichHour,
DATEADD(dd, DATEDIFF(dd,0,ReceivedOn) AS WhichDay
FROM answers
WHERE ReceivedOn BETWEEN '2011-03-10 00:00:00.000' AND '2011-03-10 23:59:59.997'
Lowell
March 10, 2011 at 9:26 am
The easiest way to do this kind of thing is build a "DateTimes" table and join to that.
create table dbo.DateTimes (
DTVal datetime primary key,
constraint CK_DTVal_Hourly check (datepart(minute, DTVal) = 0 and datepart(second, DTVal) = 0 and datepart(millisecond, DTVal) =0));
Then insert a cross join between dates and hours (easy to do with a Numbers table and a simple dateadd).
Join to that by testing that the timestamp in your table is greater than or equal to the DTVal and less than DTVal plus one hour. Then count based on DTVal.
Very simple, very effective.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 10, 2011 at 9:43 am
Here's a short article on calendar tables, if you need one: http://www.sqlservercentral.com/articles/70482/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply