May 24, 2011 at 10:36 pm
-- Count items per hour
SELECT Year, Month, Day, Hour, /*COUNT(Hour) AS "Items per Hour",*/ COUNT(subtask_id) AS No_of_SubIssues, COUNT(task_id) AS No_of_Issues
FROM
(
SELECT DATEPART(YEAR,create_time) Year, DATEPART(MONTH,create_time) Month,
DATEPART(DAY,create_time) Day, DATEPART(HOUR,create_time) Hour
FROM tasks
) temp
group by Year, Month, Day, Hour
order by Year desc, Month desc, Day desc, Hour desc
I want to get the count of another column too, but how? So, i want to see:
--------------------------------------------
Task_Count Sub_Task_Count Hour
14 11 2
----------------------------------------------
Please advise!
May 25, 2011 at 12:14 am
It would be great if there was more information. for example, the table structure of "tasks" and some sample data.
Please see the article for more information on best practices How to post data/code on a forum to get the best help
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 27, 2011 at 2:51 pm
Not knowing what your tasks table looks like, I'm not sure if this is answering your question. But if I create a table like this:
CREATE TABLE tasks (
PKCol tinyint, task_id smallint, subtask_id smallint, create_time datetime
)
And populate it with some data looking like this:
PKColtask_idsubtask_idcreate_time
1142011-05-28 16:25:05.177
2132011-05-29 02:01:05.173
3122011-05-29 16:25:05.177
4112011-05-30 06:49:05.177
5222011-05-30 16:25:05.177
6212011-05-30 21:13:05.177
7232011-05-30 21:13:05.180
Then I can run a query like this to group by year, month and hour, using a DISTINCT count for the task level, and get two different counts:
SELECT COUNT(DISTINCT task_id) AS [Task_Count],
COUNT(1) AS [Sub_Task_Count],
YEAR(create_time) AS [Year],
MONTH(create_time) AS [Month],
DATEPART(HOUR,create_time) AS [Hour]
FROM tasks
GROUP BY YEAR(create_time),
MONTH(create_time),
DATEPART(HOUR,create_time);
Is this close to what you are trying to do?
------------------------------------------------------
Katherine
May 27, 2011 at 3:12 pm
Unless there was a specific reason to breakout the date parts - I wouldn't do it that way:
SELECT dateadd(hour, datediff(hour, '20110101', t.create_time), '20110101') As DateHour
,count(*) As CountPerHour
FROM dbo.Tasks t
GROUP BY dateadd(hour, datediff(hour, '20110101', t.create_time), '20110101');
This way, we still have a datetime column and can still use all of the date/time functions as needed.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply