February 20, 2017 at 9:59 am
Hi,
I need to filter data with time stamp field for every 30 min, and run the job to provide the count for every half an hour.
Eg:
Run 1:
Select datekey, [half hour startime], count(ColumnID) from tableA fct
inner join dimtime t on t.timekey = fct.timekey
inner join dimdate d on d.datekey = fct.datekey
where
Fct.DateTimeColumn between '2017-02-01 00:00:00' and 2017-02-01 00:30:00' -- How do i this write query to write for every 30min
group by [datekey], [half hour startime]
Run 2:
Select datekey, [half hour startime], count(ColumnID) from tableA fct
inner join dimtime t on t.timekey = fct.timekey
inner join dimdate d on d.datekey = fct.datekey
where
Fct.DateTimeColumn between '2017-02-01 00:30:00' and 2017-02-01 01:00:00'
group by [datekey], [half hour startime]
How do I change Time for every 30 Min for every time I run the Job.
It would be great if anybody would help me.
Thank you
Sangeeth
February 21, 2017 at 1:43 am
Lynn Petttis has an article about common date routines:
http://qa.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/
The query below shows some (date/)time manipulations. Each column in the query is part of the final column with the time being rounded to 30 minutes. After you have added such a column to your results, you can use that column in the GROUP BY clause.declare @times table (half_hour time)
insert into @times
values ('02:02:05')
, ('10:13:16')
, ('14:44:06')
, ('12:25:21')
, ('06:06:56')
, ('20:37:06')
, ('18:08:01')
, ('23:49:00')
, ('00:10:06')
, ('03:53:06')
select half_hour
, dateadd(dd, datediff(dd, 0, half_hour), 0) as 'Beginning of this day'
, datediff(minute, 0, half_hour) as 'Total inutes since start of day'
, datediff(minute, 0, half_hour) % 30 as 'Minutes since last half hour'
, (datediff(minute, 0, half_hour) - datediff(minute, 0, half_hour) % 30) as 'Minutes to add to start of day'
, dateadd(minute, (datediff(minute, 0, half_hour) - datediff(minute, 0, half_hour) % 30), dateadd(dd, datediff(dd, 0, half_hour), 0)) as 'Rouded to half hour'
from @times
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply