30 mins report

• sahana

  SSCrazy

  Points: 2530

  More actions

  May 18, 2005 at 9:07 am

  #163593

  We are collecting the login info in our database for every minute of the day. I need to generate a report of the total logins for every 30 mins as shown below.

  time              Total_logins

  00:00-00:30     10

  00:31-01:00     15

  01:01-01:30     20

  01:31-02:00     18

  .....

  ....

  .....

  23:31-24:00 22

  I need need to generate this kind of report. I know how to group the data by an hour and a miniute, But how can I group the data by halh an hour(30 mins)

  Thanks.

   

• Kenneth Wilhelmsson

  SSC-Dedicated

  Points: 30043

  More actions

  May 18, 2005 at 9:29 am

  #559625

  Here's one way of doing it.. It's a lot of writing, but it works.

  I'm assuming that it's a datetime datatype? (that's why the convert is there)

  Also, if the table has times for different days, you need to qualify which date (as in days) the times should be counted for...

  Anyway, it goes like this... just continue adding halfhours until midnight.

  create table #x ( login datetime not null )

  insert #x select '00:00:10'

  insert #x select '00:00:11'

  insert #x select '01:00:23'

  SELECT '00:00-00:30' as logintime,

   SUM(CASE when CONVERT(CHAR(8),login,108) between '00:00:00' and '00:30:00' then 1 else 0 end) as Total_logins

  from #x

  UNION ALL

  SELECT '00:31-01:00' as logintime,

   SUM(CASE when CONVERT(CHAR(8),login,108) between '00:30:00' and '01:00:00' then 1 else 0 end) as Total_logins

  from #x

  UNION ALL ....

  etc etc etc .....

  logintime   Total_logins

  ----------- ------------

  00:00-00:30 2

  00:31-01:00 0

  (2 row(s) affected)

  /Kenneth 

• sara karasik

  SSCommitted

  Points: 1573

  More actions

  May 18, 2005 at 11:08 am

  #559662

  Here's how to group the data by half hour. Use datepart to isolate the hour, and again to isolate the minutes. Group the minutes by using a case statement to group them into 2 groups; more and less than 30 minutes.

   

  select count(logins), datepart(hh, YOUR_DATE_COLUMN),

  case

   when datepart(minute, YOUR_DATE_COLUMN) > 30 then .5

   else 0

   end

  from YOUR_TABLE

  group by  datepart(hh, YOUR_DATE_COLUMN),

  case

   when datepart(minute, YOUR_DATE_COLUMN) > 30 then .5

   else 0

   end

   

• Farrell Keough

  SSCoach

  Points: 17414

  More actions

  May 18, 2005 at 12:09 pm

  #559680

  Here is a very slick method that Frank Kalis showed me.  (It took me a while of Private Posts offline to get it - I am a little thick sometimes...). 

  Play with the Modulo [ % ] function to see just what is happening. 

  CREATE TABLE #Time( LogIn integer,

    dt datetime)

  INSERT INTO #Time

  SELECT 10,  '20050427 10:00:00'

  UNION ALL

  SELECT 15,  '20050427 10:01:00'

  UNION ALL

  SELECT 100,  '20050427 10:20:00'

  UNION ALL

  SELECT 25,  '20050427 11:21:00'

  UNION ALL

  SELECT 1,  '20050427 11:51:00'

  UNION ALL

  SELECT 101,  '20050427 12:02:00'

  UNION ALL

  SELECT 35,  '20050427 12:03:00'

  UNION ALL

  SELECT 45,  '20050427 12:04:00'

  UNION ALL

  SELECT 55,  '20050427 12:05:00'

  UNION ALL

  SELECT 56,  '20050427 01:53:00'

  UNION ALL

  SELECT 55,  '20050427 02:04:00'

  UNION ALL

  SELECT 57,  '20050427 02:14:00'

  UNION ALL

  SELECT 157,  '20050427 02:24:00'

  UNION ALL

  SELECT 99,  '20050427 03:55:00'

  UNION ALL

  SELECT 101,  '20050427 04:56:00'

        

  SELECT SUM( LogIn) AS LogIn, DATEADD( minute, -DATEPART( minute, dt) % 30, dt) AS DateBy30Minutes

  FROM #Time

  GROUP BY  DATEADD( minute, -DATEPART( minute, dt) % 30, dt)

  DROP TABLE #Time

  I wasn't born stupid - I had to study.