0

Here is the problem: table has login timestamp (unix) and I need to find maximum number of logins in a period of time. Let's assume a have a time slice 30 minutes. Find the start time and maximum login count of 30 minutes period where timestamp is between last midnight and this midnight.

I have no idea how to write query. GROUP BY ? TRUNCATE ? PARTITION ? Any suggestions are welcomed for MS SQL 2008 or 2012

TABLE x ( LoginUTC int )

Thank you.

  • 1
    Possible duplicate of [How to group time by hour or by 10 minutes](http://stackoverflow.com/questions/5002661/how-to-group-time-by-hour-or-by-10-minutes) – AHiggins Dec 09 '15 at 18:47
  • 1
    Take the accepted answer of the question linked above and change the final line to divide by 30. – AHiggins Dec 09 '15 at 18:48

2 Answers2

0

go with the possible duplicate

this is a start 

select yy, mm, dd, hh, count(*) as ccount
  from table 
 group by yy, mm, dd, hh 
 order by count(*) desc
paparazzo
  • 44,497
  • 23
  • 105
  • 176
0

So finally I wrote query to show me how they login in 3 time slices, but we may cut the time on more if necessary:

DECLARE @dtz AS VARCHAR(30);
SET @dtz = '1969-12-31 18:00:00';
WITH Logins_CTE (
yy,
mm,
dd,
hh,
ur
)
AS (
SELECT year(dateadd(ss, login_time, @dtz)) AS yy,
  month(dateadd(ss, login_time, @dtz)) AS mm,
  day(dateadd(ss, login_time, @dtz)) AS dd,
  CASE 
   WHEN (datepart(hour, dateadd(ss, login_time, @dtz)) < 7)
    THEN 1
   WHEN (
     datepart(hour, dateadd(ss, login_time, @dtz)) > 6
     AND datepart(hour, dateadd(ss, login_time, @dtz)) < 18
     )
    THEN 2
   WHEN (datepart(hour, dateadd(ss, login_time, @dtz)) > 17)
    THEN 3
   ELSE 4
   END AS hh,
  userid AS ur
FROM login_log WITH (NOLOCK)
WHERE login_time BETWEEN datediff(ss, '1970-01-01', '2015-11-15')
   AND datediff(ss, '1970-01-01', '2015-12-20')
GROUP BY userid, login_time
)
SELECT yy,
mm,
dd,
hh,
count(DISTINCT ur) AS cc
FROM Logins_CTE
GROUP BY yy,
mm,
dd,
hh
ORDER BY cc DESC