Reputation: 37
Group by Date -Month -Day Hour and Time Query
I would like to group by Rundate and then JobDateStamp by yy/mm/dd hh:mm no seconds
Results
[RunDate] [count]
12/11/2014 21:00 3
13/11/2014 21:00 1
3 lots of jobs were run on 12/11/2014 (3 date and time) 1 lots of jobs were run on 13/11/2014 (1 date and time)
**create table tbl_tasks**
(
Rundate datetime,
JobDateStamp datetime,
Runs int
)
insert into tbl_tasks values
('2014-11-13 21:00:46.393','2014-11-13 21:36:27.393',1),
('2014-11-13 21:00:46.393','2014-11-13 21:36:25.393',1),
('2014-11-13 21:00:46.393','2014-11-13 21:36:24.393',1),
('2014-11-12 21:00:47.000','2014-11-13 14:14:46.393',1),
('2014-11-12 21:00:47.000','2014-11-13 14:12:46.393',1),
('2014-11-12 21:00:47.000','2014-11-12 21:04:43.393',1),
('2014-11-12 21:00:47.000','2014-11-12 21:04:41.393',1)
This data is a result of a query and next step is to group by yy/mm/dd hh:mm
Rundate JobDateStamp Runs
2014-11-13 21:00:46.393 2014-11-13 21:36:27.393 1
2014-11-13 21:00:46.393 2014-11-13 21:36:25.393 1
2014-11-13 21:00:46.393 2014-11-13 21:36:24.393 1
2014-11-12 21:00:47.000 2014-11-13 14:14:46.393 1
2014-11-12 21:00:47.000 2014-11-13 14:12:46.393 1
2014-11-12 21:00:47.000 2014-11-12 21:04:43.393 1
2014-11-12 21:00:47.000 2014-11-12 21:04:41.393 1
Upvotes: 0
Views: 399
Reputation: 16917
If you are using SQL Server 2012
, you can also make some use of the Format
to group on:
Select Rundate, Count(*) Count
From
(
Select Format(Rundate, 'yyyy/MM/dd HH:mm') Rundate, JobDateStamp, Runs
From tbl_tasks
)A
Group By Rundate
Upvotes: 0
Reputation: 13425
you can do it by converting time to minutes and using count(distinct .. )
As you need further filter by jobdatestamp, need to use it in count
SELECT dateadd(minute, datediff(minute, 0, rundate), 0) ,
count( distinct dateadd(minute, datediff(minute, 0, JobDateStamp), 0))
FROM tbl_tasks
GROUP by dateadd(minute, datediff(minute, 0, rundate), 0)
Upvotes: 2
Reputation: 152654
Just truncate the datetime to the previous minute and group by that value:
select
dateadd(minute, datediff(minute, 0, Rundate ), 0) RunDate,
COUNT(*) Count
FROM tbl_tasks
GROUP BY dateadd(minute, datediff(minute, 0, Rundate ), 0)
Upvotes: 1