Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 counts by time range help

Author  Topic 

pithhelmet
Posting Yak Master

183 Posts

Posted - 2012-05-02 : 17:14:32
Hi Everyone,

I have a user log in table, that logs the time the user logs in.

I need to get a count of the logins per every 30 minute interval.

i was thinking of a nice little rollup, but i am lost on only getting the counts in a 30 minute block of time.... for example from 6:00am to 6:29am, how many people logged in.

thanks
tony

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-05-02 : 17:16:42
SELECT DATEADD(minute, DATEDIFF(minute, 0, LoginTime)/30*30, 0) Interval, COUNT(*) Logins
FROM MyTable
GROUP BY DATEDIFF(minute, 0, LoginTime)/30
Go to Top of Page

pithhelmet
Posting Yak Master

183 Posts

Posted - 2012-05-02 : 17:32:08
PERFECT!!! worked like a champ!!

now.... if i may extend this question a little bit....

the user wants to see a zero for all the time blocks (every 30 minutes) when there are no logins....

thanks again


Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-05-02 : 17:34:10
That's possible, but do they want to see an entire day of zeros if no one logged in? What about the days/weeks/months prior to the first login?
Go to Top of Page

pithhelmet
Posting Yak Master

183 Posts

Posted - 2012-05-02 : 17:41:22
yup - a full sheet of zeros (rendered as blanks in presentation) - *i know* for the selected date
Go to Top of Page

pithhelmet
Posting Yak Master

183 Posts

Posted - 2012-05-02 : 17:44:48
pressed enter to fast - they want it to look like a spreadsheet
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-05-02 : 18:05:50
[code]DECLARE @date datetime
SET @date='20120501'
;WITH n(n) AS (SELECT 0 UNION ALL SELECT n+30 FROM n WHERE n<1410),
I (Interval) AS (SELECT DATEADD(minute, n, @date) FROM n),
I2 (Interval, Finish) AS (SELECT Interval, DATEADD(ms, 1799997, Interval) FROM I)
SELECT I2.Interval, COUNT(*)
FROM MyTable A
RIGHT JOIN I2 ON A.LoginTime BETWEEN I2.Interval AND I2.Finish
GROUP BY I2.Interval[/code]This generates 30 minute intervals for the date in question, then joins to your table where LoginTime fits between the start and finish of the interval. Change the @date variable to a parameter for a stored procedure and remove the SET statement.

This is a bit elaborate and I'm sure someone can make it smaller.
Go to Top of Page

pithhelmet
Posting Yak Master

183 Posts

Posted - 2012-05-02 : 18:14:14
time blocks are 100% correct.... displays a time block for all hours!!! but the count is always returning a one (1)

any ideas?


The one block of time for the selected date has three entries,
and the value for that time block does report three....

just the rest of them have an entry of one....


thank you for your help!!

Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-05-02 : 19:28:56
You'd need to post some sample data and expected results, about 10-12 rows from 4-5 different intervals.
Go to Top of Page

pithhelmet
Posting Yak Master

183 Posts

Posted - 2012-05-03 : 10:00:57
Here is the column that holds the timein values.... the rest of the columns are not really in question...

2011-09-08 19:16:38.127
2011-09-14 13:59:05.913
2012-02-21 18:34:20.830
2011-09-05 17:32:59.753
2012-03-22 13:24:34.957
2012-03-22 13:24:17.957
2010-12-13 18:29:05.660
2011-12-08 13:19:57.497
2010-10-27 15:50:53.217
2011-09-05 17:33:40.463
2011-09-08 19:23:01.287
2011-09-14 13:48:44.667
2011-12-13 22:45:41.113
2011-12-07 22:58:45.990
2010-10-27 17:20:15.560
2011-12-08 13:17:58.263
2011-12-08 13:18:42.743
2010-11-16 15:16:17.273
2010-11-19 14:53:38.863
2011-12-09 18:09:39.967

thanks again for your help

Go to Top of Page

pithhelmet
Posting Yak Master

183 Posts

Posted - 2012-05-03 : 10:01:59
ANd here is the script as I implemented it....

DECLARE @date datetime
SET @date='20111208'
;WITH n(n) AS (SELECT 0 UNION ALL SELECT n+30 FROM n WHERE n<1410),
I (Interval) AS (SELECT DATEADD(minute, n, @date) FROM n),
I2 (Interval, Finish) AS (SELECT Interval, DATEADD(ms, 1799997, Interval) FROM I)
SELECT I2.Interval, COUNT(*) AS [Logins]
FROM dbo.AdmissionProgram A
RIGHT join I2 ON A.timein BETWEEN I2.Interval AND I2.Finish
GROUP BY I2.Interval
Go to Top of Page

pithhelmet
Posting Yak Master

183 Posts

Posted - 2012-05-03 : 10:04:01
And here are the results....

Interval Logins
2011-12-08 00:00:00.000 1
2011-12-08 00:30:00.000 1
2011-12-08 01:00:00.000 1
2011-12-08 01:30:00.000 1
2011-12-08 02:00:00.000 1
2011-12-08 02:30:00.000 1
2011-12-08 03:00:00.000 1
2011-12-08 03:30:00.000 1
2011-12-08 04:00:00.000 1
2011-12-08 04:30:00.000 1
2011-12-08 05:00:00.000 1
2011-12-08 05:30:00.000 1
2011-12-08 06:00:00.000 1
2011-12-08 06:30:00.000 1
2011-12-08 07:00:00.000 1
2011-12-08 07:30:00.000 1
2011-12-08 08:00:00.000 1
2011-12-08 08:30:00.000 1
2011-12-08 09:00:00.000 1
2011-12-08 09:30:00.000 1
2011-12-08 10:00:00.000 1
2011-12-08 10:30:00.000 1
2011-12-08 11:00:00.000 1
2011-12-08 11:30:00.000 1
2011-12-08 12:00:00.000 1
2011-12-08 12:30:00.000 1
2011-12-08 13:00:00.000 3
2011-12-08 13:30:00.000 1
2011-12-08 14:00:00.000 1
2011-12-08 14:30:00.000 1
2011-12-08 15:00:00.000 1
2011-12-08 15:30:00.000 1
2011-12-08 16:00:00.000 1
2011-12-08 16:30:00.000 1
2011-12-08 17:00:00.000 1
2011-12-08 17:30:00.000 1
2011-12-08 18:00:00.000 1
2011-12-08 18:30:00.000 1
2011-12-08 19:00:00.000 1
2011-12-08 19:30:00.000 1
2011-12-08 20:00:00.000 1
2011-12-08 20:30:00.000 1
2011-12-08 21:00:00.000 1
2011-12-08 21:30:00.000 1
2011-12-08 22:00:00.000 1
2011-12-08 22:30:00.000 1
2011-12-08 23:00:00.000 1
2011-12-08 23:30:00.000 1

There should be only a single row with the count of three... in the 13:00 half hour

Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-05-03 : 12:19:32
I'm a dumbass, change the COUNT(*) to COUNT(timein) and see if that works.
Go to Top of Page

pithhelmet
Posting Yak Master

183 Posts

Posted - 2012-05-03 : 12:54:31
you sir - rock!!

spot on - thank you x100

btw - in this line of code...
WITH n(n) AS (SELECT 0 UNION ALL SELECT n+30 FROM n WHERE n<1410),

what exactly is takimg place??

thanks tony
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-05-03 : 13:01:48
If you run it by itself:
WITH n(n) AS (SELECT 0 UNION ALL SELECT n+30 FROM n WHERE n<1410)
select * from n
You'll see.

More information on CTEs can be found here: http://msdn.microsoft.com/en-us/library/ms190766.aspx
Go to Top of Page
   

- Advertisement -