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(*) LoginsFROM MyTableGROUP BY DATEDIFF(minute, 0, LoginTime)/30 |
 |
|
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 |
 |
|
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? |
 |
|
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 |
 |
|
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 |
 |
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-05-02 : 18:05:50
|
[code]DECLARE @date datetimeSET @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 ARIGHT JOIN I2 ON A.LoginTime BETWEEN I2.Interval AND I2.FinishGROUP 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. |
 |
|
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!! |
 |
|
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. |
 |
|
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.1272011-09-14 13:59:05.9132012-02-21 18:34:20.8302011-09-05 17:32:59.7532012-03-22 13:24:34.9572012-03-22 13:24:17.9572010-12-13 18:29:05.6602011-12-08 13:19:57.4972010-10-27 15:50:53.2172011-09-05 17:33:40.4632011-09-08 19:23:01.2872011-09-14 13:48:44.6672011-12-13 22:45:41.1132011-12-07 22:58:45.9902010-10-27 17:20:15.5602011-12-08 13:17:58.2632011-12-08 13:18:42.7432010-11-16 15:16:17.2732010-11-19 14:53:38.8632011-12-09 18:09:39.967thanks again for your help |
 |
|
pithhelmet
Posting Yak Master
183 Posts |
Posted - 2012-05-03 : 10:01:59
|
ANd here is the script as I implemented it....DECLARE @date datetimeSET @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.FinishGROUP BY I2.Interval |
 |
|
pithhelmet
Posting Yak Master
183 Posts |
Posted - 2012-05-03 : 10:04:01
|
And here are the results....Interval Logins2011-12-08 00:00:00.000 12011-12-08 00:30:00.000 12011-12-08 01:00:00.000 12011-12-08 01:30:00.000 12011-12-08 02:00:00.000 12011-12-08 02:30:00.000 12011-12-08 03:00:00.000 12011-12-08 03:30:00.000 12011-12-08 04:00:00.000 12011-12-08 04:30:00.000 12011-12-08 05:00:00.000 12011-12-08 05:30:00.000 12011-12-08 06:00:00.000 12011-12-08 06:30:00.000 12011-12-08 07:00:00.000 12011-12-08 07:30:00.000 12011-12-08 08:00:00.000 12011-12-08 08:30:00.000 12011-12-08 09:00:00.000 12011-12-08 09:30:00.000 12011-12-08 10:00:00.000 12011-12-08 10:30:00.000 12011-12-08 11:00:00.000 12011-12-08 11:30:00.000 12011-12-08 12:00:00.000 12011-12-08 12:30:00.000 12011-12-08 13:00:00.000 32011-12-08 13:30:00.000 12011-12-08 14:00:00.000 12011-12-08 14:30:00.000 12011-12-08 15:00:00.000 12011-12-08 15:30:00.000 12011-12-08 16:00:00.000 12011-12-08 16:30:00.000 12011-12-08 17:00:00.000 12011-12-08 17:30:00.000 12011-12-08 18:00:00.000 12011-12-08 18:30:00.000 12011-12-08 19:00:00.000 12011-12-08 19:30:00.000 12011-12-08 20:00:00.000 12011-12-08 20:30:00.000 12011-12-08 21:00:00.000 12011-12-08 21:30:00.000 12011-12-08 22:00:00.000 12011-12-08 22:30:00.000 12011-12-08 23:00:00.000 12011-12-08 23:30:00.000 1There should be only a single row with the count of three... in the 13:00 half hour |
 |
|
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. |
 |
|
pithhelmet
Posting Yak Master
183 Posts |
Posted - 2012-05-03 : 12:54:31
|
you sir - rock!!spot on - thank you x100btw - 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 |
 |
|
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 |
 |
|
|