locked
Need ongoing daily total in 5 minute intervals RRS feed

  • Question

  • Dear knowledgeable ppl,

    Here's a tough one for SQL Server 2000. Atm i got a query that shows count of alerts in 5 minute intervals for, let's say, a month. What i need is a column that will show the ongoing daily total of alerts up to and including each 5 minute interval.

    Here's what i have so far:
    Code Snippet

    select rounded_time, rounded_day, alert_count, SUM(alert_count) as daily, unix_time
    from
    (
    SELECT rounded_time, rounded_day, count(0) as alert_count, DATEDIFF(second,'1970-01-01 00:00:00',rounded_time)+18000 as unix_time
    FROM
    (
    (SELECT
    dateadd(minute,(datediff(minute,0,initiate_time)/(5))*5+5,0) as rounded_time,
    dateadd(dd,(datediff(dd,0,initiate_time)),0) as rounded_day
    FROM
    [procMsg_Session] WITH (nolock)
    WHERE
    initiate_time >= dateadd(mm,0,'2008-01-01 00:00')
    AND initiate_time < dateadd (mm, 0 + 1, '2008-01-01 00:00'))
    ) [procMsg_Session]
    GROUP BY rounded_time,rounded_day
    ) procMsg_Session
    GROUP BY rounded_time, rounded_day, alert_count,unix_time
    ORDER BY rounded_time


    The result looks like:

    Code Snippet

    rounded_time rounded_day alert_count daily unix_time

    2008-01-01 00:05:00.000 2008-01-01 00:00:00.000 25 25 1199163900
    2008-01-01 00:10:00.000 2008-01-01 00:00:00.000 4 4 1199164200
    2008-01-01 00:15:00.000 2008-01-01 00:00:00.000 24 24 1199164500
    2008-01-01 00:20:00.000 2008-01-01 00:00:00.000 7 7 1199164800
    2008-01-01 00:25:00.000 2008-01-01 00:00:00.000 17 17 1199165100
    2008-01-01 00:30:00.000 2008-01-01 00:00:00.000 8 8 1199165400


    Instead, i need [daily] to look like:

    Code Snippet

    alert_count daily

    25 25
    4 29
    24 53
    7 60
    17 77

    At the end of the day (this will be rounded up to 00:00) i want to see the total for all day (2008-01-01 00:05 to 2008-01-02 00:00) and at the start of the next day the count will start over.

    I researched this problem and found that in SQL Server 2005 there is an OVER keyword that you can use in something like
    SUM(alert_count) OVER (PARTITION BY rounded_time ORDER BY rounded_time 
    ROWS UNBOUNDED PRECEDING)
    but it doesn't look like it works for SQL Server 2000 - i get an "Incorrect synax near keyword OVER" error.

    I will appreciate any help at all! If possible, please be as specific as you can, because i might not know what to do with an answer like "Try using keyword OVER" - please write the actual query.

    Thank you in advance!
    Thursday, September 4, 2008 7:10 PM

Answers

  • This kind of problem is where CTE's in 2005 are so handy to have.  There are two possible solutions in SQL 2000 that I can think of off the top of my head, though others probably exist.

     

    First you can do an inline derived table join to the existing derived table, in your from clause like this:

     

    Code Snippet

    select procMsg_Session.rounded_time,
     procMsg_Session.rounded_day,
     procMsg_Session.alert_count,
     SUM(procMsg_Session2.alert_count) as daily,
     procMsg_Session.unix_time
    from
    (
     SELECT rounded_time, rounded_day, count(0) as alert_count, DATEDIFF(second,'1970-01-01 00:00:00',rounded_time)+18000 as unix_time
     FROM
     (
      SELECT
       dateadd(minute,(datediff(minute,0,initiate_time)/(5))*5+5,0) as rounded_time,
       dateadd(dd,(datediff(dd,0,initiate_time)),0) as rounded_day
      FROM
       [procMsg_Session] WITH (nolock)
      WHERE
       initiate_time >= dateadd(mm,0,'2008-01-01 00:00')
       AND initiate_time < dateadd (mm, 0 + 1, '2008-01-01 00:00')
      ) [procMsg_Session]
     GROUP BY rounded_time,rounded_day
    ) procMsg_Session
    JOIN
    ( SELECT rounded_time, rounded_day, count(0) as alert_count, DATEDIFF(second,'1970-01-01 00:00:00',rounded_time)+18000 as unix_time
     FROM
     (
      SELECT
       dateadd(minute,(datediff(minute,0,initiate_time)/(5))*5+5,0) as rounded_time,
       dateadd(dd,(datediff(dd,0,initiate_time)),0) as rounded_day
      FROM
       [procMsg_Session] WITH (nolock)
      WHERE
       initiate_time >= dateadd(mm,0,'2008-01-01 00:00')
       AND initiate_time < dateadd (mm, 0 + 1, '2008-01-01 00:00')
      ) [procMsg_Session]
     GROUP BY rounded_time,rounded_day
    ) procMsg_Session2 ON procMsg_Session.rounded_day = procMsg_Session2.rounded_day
     and procMsg_Session.rounded_time <= procMsg_Session2.rounded_time
    GROUP BY procMsg_Session.rounded_time, procMsg_Session.rounded_day, procMsg_Session.alert_count, procMsg_Session.unix_time
    ORDER BY procMsg_Session.rounded_time

     

     

    The join is on the day, and the time less than the time in the first derived output.  Then you are summing the alert_count off the joined result, grouping by the columns in the first result.  Very nasty code, but it should do what you want.

     

    The other way, would be to create a table variable with an identity seed, and insert the inner query with predefined sort order into the table variable.  Then select off this table with the same join as doing the derived table:

     

    Code Snippet

    DECLARE @Table TABLE
    (RowID int identity primary key,
     rounded_time datetime,
     rounded_day datetime,
     alert_count int,
     unix_time bigint)
     
    INSERT INTO @Table (rounded_time, rounded_day, alert_count, unix_time)
    SELECT rounded_time, rounded_day, count(0) as alert_count, DATEDIFF(second,'1970-01-01 00:00:00',rounded_time)+18000 as unix_time
    FROM
    (
     SELECT
      dateadd(minute,(datediff(minute,0,initiate_time)/(5))*5+5,0) as rounded_time,
      dateadd(dd,(datediff(dd,0,initiate_time)),0) as rounded_day
     FROM
      [procMsg_Session] WITH (nolock)
     WHERE
      initiate_time >= dateadd(mm,0,'2008-01-01 00:00')
      AND initiate_time < dateadd (mm, 0 + 1, '2008-01-01 00:00')
     ) [procMsg_Session]
    GROUP BY rounded_time,rounded_day
    ORDER BY rounded_time

     

    --Output result:
    SELECT t1.rounded_time, t1.rounded_day, t1.alert_count, SUM(t2.alert_count), t1.unix_time
    FROM @Table t1
    JOIN @Table t2 on t1.RowID > = t2.RowID

     

     

    A bit cleaner to read and will probably work faster, but it might not fit your need.
    Friday, September 5, 2008 3:38 PM

All replies

  • I think I might be confused.  Your query is adding all the extra stuff to the output you are recieving.  Try something like...

     

    select alert_count, SUM(alert_count) as daily
    from
    (
    SELECT rounded_time, rounded_day, count(0) as alert_count, DATEDIFF(second,'1970-01-01 00:00:00',rounded_time)+18000 as unix_time
    FROM
    (
    (SELECT
    dateadd(minute,(datediff(minute,0,initiate_time)/(5))*5+5,0) as rounded_time,
    dateadd(dd,(datediff(dd,0,initiate_time)),0) as rounded_day
    FROM
    [procMsg_Session] WITH (nolock)
    WHERE
    initiate_time >= dateadd(mm,0,'2008-01-01 00:00')
    AND initiate_time < dateadd (mm, 0 + 1, '2008-01-01 00:00'))
    ) [procMsg_Session]
    GROUP BY rounded_time,rounded_day
    ) procMsg_Session
    GROUP BY rounded_time, rounded_day, alert_count,unix_time
    ORDER BY rounded_time

    Thursday, September 4, 2008 10:24 PM
  • David, thank you for the answer and the awesome format of it Smile

    However, this doesnt help Sad The query produces 2 columns,
    alert_count and daily, the data in which is teh same:

    25    25
    4    4
    24    24
    7    7
    17    17
    8    8
    10    10

    Perhaps i did confuse you. In my question i did not mean that i want the output to be only 2 columns - i just sorta "zoomed in" on the ones that are important for this question. To eliminate the confusion, the actual exact output i want to see is:

    rounded_time rounded_day alert_count daily unix_time
    2008-01-01 00:05:00.000 2008-01-01 00:00:00.000 25 25 1199163900
    2008-01-01 00:10:00.000 2008-01-01 00:00:00.000 4 29 1199164200
    2008-01-01 00:15:00.000 2008-01-01 00:00:00.000 24 53 1199164500
    2008-01-01 00:20:00.000 2008-01-01 00:00:00.000 7 60 1199164800
    2008-01-01 00:25:00.000 2008-01-01 00:00:00.000 17 77 1199165100

    where [daily] is the sum of all [alert_count]s up until and including this row.

    Hope this is more clear now.

    Friday, September 5, 2008 1:48 PM
  • David, thank you for the answer and the awesome format of it Smile

    However, this doesnt help Sad The query produces 2 columns,
    alert_count and daily, the data in which is teh same:

    25    25
    4    4
    24    24
    7    7
    17    17
    8    8
    10    10

    Perhaps i did confuse you. In my question i did not mean that i want the output to be only 2 columns - i just sorta "zoomed in" on the ones that are important for this question. To eliminate the confusion, the actual exact output i want to see is:

    rounded_time rounded_day alert_count daily unix_time
    2008-01-01 00:05:00.000 2008-01-01 00:00:00.000 25 25 1199163900
    2008-01-01 00:10:00.000 2008-01-01 00:00:00.000 4 29 1199164200
    2008-01-01 00:15:00.000 2008-01-01 00:00:00.000 24 53 1199164500
    2008-01-01 00:20:00.000 2008-01-01 00:00:00.000 7 60 1199164800
    2008-01-01 00:25:00.000 2008-01-01 00:00:00.000 17 77 1199165100

    where [daily] is the sum of all [alert_count]s up until and including this row.

    Hope this is more clear now.

    Friday, September 5, 2008 1:51 PM
  • Oh ok.  This makes a lot more sense now!  Smile

     

    It looks like the issue you are having is with the (SUM(alert_count) as daily) part of your query.  From what I can tell, you do not want the sum of the alert_counts, you want the previous alert_count + the new alert_count. 

     

    The reason you are getting the same number in each column is because the alert_count is in the group by as well as the aggerate.

     

    Way to maybe do this...

     

    Write your query to return the dataset like you have posted above except instead of Sum(alert_count) as daily, use '' as daily.

     

    Then write a query to return the dataset like you have posted above except instead of alert_count, use '' as 'alert_count'.

    Union the two together

     

    OR

     

    You may need to do a subselect.  Take the query you have posted above.  Instead of using (SUM(alert_count) as daily) use something like (tbl1.alertCount + (select tbl2.alertCount from tbl2 where tbl2.unixTime = tbl1.unixTime - 18000))

     

    I have no way to actually try this right now (Not on my sql box).  Hope it at least provides some insight.

     

     

    OR

     

    This may be something you need to do write a UDF for.

     

     

     

     

    Friday, September 5, 2008 2:32 PM
  • This kind of problem is where CTE's in 2005 are so handy to have.  There are two possible solutions in SQL 2000 that I can think of off the top of my head, though others probably exist.

     

    First you can do an inline derived table join to the existing derived table, in your from clause like this:

     

    Code Snippet

    select procMsg_Session.rounded_time,
     procMsg_Session.rounded_day,
     procMsg_Session.alert_count,
     SUM(procMsg_Session2.alert_count) as daily,
     procMsg_Session.unix_time
    from
    (
     SELECT rounded_time, rounded_day, count(0) as alert_count, DATEDIFF(second,'1970-01-01 00:00:00',rounded_time)+18000 as unix_time
     FROM
     (
      SELECT
       dateadd(minute,(datediff(minute,0,initiate_time)/(5))*5+5,0) as rounded_time,
       dateadd(dd,(datediff(dd,0,initiate_time)),0) as rounded_day
      FROM
       [procMsg_Session] WITH (nolock)
      WHERE
       initiate_time >= dateadd(mm,0,'2008-01-01 00:00')
       AND initiate_time < dateadd (mm, 0 + 1, '2008-01-01 00:00')
      ) [procMsg_Session]
     GROUP BY rounded_time,rounded_day
    ) procMsg_Session
    JOIN
    ( SELECT rounded_time, rounded_day, count(0) as alert_count, DATEDIFF(second,'1970-01-01 00:00:00',rounded_time)+18000 as unix_time
     FROM
     (
      SELECT
       dateadd(minute,(datediff(minute,0,initiate_time)/(5))*5+5,0) as rounded_time,
       dateadd(dd,(datediff(dd,0,initiate_time)),0) as rounded_day
      FROM
       [procMsg_Session] WITH (nolock)
      WHERE
       initiate_time >= dateadd(mm,0,'2008-01-01 00:00')
       AND initiate_time < dateadd (mm, 0 + 1, '2008-01-01 00:00')
      ) [procMsg_Session]
     GROUP BY rounded_time,rounded_day
    ) procMsg_Session2 ON procMsg_Session.rounded_day = procMsg_Session2.rounded_day
     and procMsg_Session.rounded_time <= procMsg_Session2.rounded_time
    GROUP BY procMsg_Session.rounded_time, procMsg_Session.rounded_day, procMsg_Session.alert_count, procMsg_Session.unix_time
    ORDER BY procMsg_Session.rounded_time

     

     

    The join is on the day, and the time less than the time in the first derived output.  Then you are summing the alert_count off the joined result, grouping by the columns in the first result.  Very nasty code, but it should do what you want.

     

    The other way, would be to create a table variable with an identity seed, and insert the inner query with predefined sort order into the table variable.  Then select off this table with the same join as doing the derived table:

     

    Code Snippet

    DECLARE @Table TABLE
    (RowID int identity primary key,
     rounded_time datetime,
     rounded_day datetime,
     alert_count int,
     unix_time bigint)
     
    INSERT INTO @Table (rounded_time, rounded_day, alert_count, unix_time)
    SELECT rounded_time, rounded_day, count(0) as alert_count, DATEDIFF(second,'1970-01-01 00:00:00',rounded_time)+18000 as unix_time
    FROM
    (
     SELECT
      dateadd(minute,(datediff(minute,0,initiate_time)/(5))*5+5,0) as rounded_time,
      dateadd(dd,(datediff(dd,0,initiate_time)),0) as rounded_day
     FROM
      [procMsg_Session] WITH (nolock)
     WHERE
      initiate_time >= dateadd(mm,0,'2008-01-01 00:00')
      AND initiate_time < dateadd (mm, 0 + 1, '2008-01-01 00:00')
     ) [procMsg_Session]
    GROUP BY rounded_time,rounded_day
    ORDER BY rounded_time

     

    --Output result:
    SELECT t1.rounded_time, t1.rounded_day, t1.alert_count, SUM(t2.alert_count), t1.unix_time
    FROM @Table t1
    JOIN @Table t2 on t1.RowID > = t2.RowID

     

     

    A bit cleaner to read and will probably work faster, but it might not fit your need.
    Friday, September 5, 2008 3:38 PM
  • Jonathan,

    Thank you so much for your code! Very clear and idiot proof Smile

    The second solution, as you've guessed, does not meet my needs - i need to query the db from perl code, and only have read rights on the tables.

    Your fist solution was purrfect though! One minor thing - it was actually summing alert counts in the opposite order. At 00:05 data in [daily] was the biggest (sum of all counts for as day), and the smallest at 00:00 the next day (sum of just one row). All i needed to do is to reverse

    and procMsg_Session.rounded_time <= procMsg_Session2.rounded_time

    to

    and procMsg_Session.rounded_time >= procMsg_Session2.rounded_time

    and magic happened! Big Smile

    Again, thank you very much!

    I actually recently noticed an issue with this query - if there were no alerts for a certain 5 minute interval there would be no row with 0 alerts. Instead, you will see the rounded time jump to the next 5 min interval:

    00:00
    00:05
    00:10
    [skip]
    00:20

    Is there any way i could make the query show each 5 minute interval, and if there was no alerts for it it would show 0 in [alert_count] and the same value in [daily] as the previous row?

    Hope to hear your answer soon.
    Friday, September 5, 2008 9:04 PM
  • Using a table variable like in my example doesn't require any write access to the database.  Table variables are memory resident tables that exist in memory only for the duration of the batch being executed.

     

    As for your last question, again, something that CTE's are very well suited for, and not something that you can do in SQL 2000 without creating a Numbers table or, alternately, but definately not recommended, a table variable for each execution that would be a performance hinderance.

     

    You can see how to use a numbers table on the following article:

     

    Create and Use A Numbers Table

     

    You would then use the numbers table to generate a list of times between the range you need with code similar to:

     

    Code Snippet

    select dateadd(ss, n*5, @startdate)

    from numbers

    where dateadd(ss, n*5, @startdate) between @startdate and @enddate

     

     

    You can build the deadspace rows from this resultset.

     

     

    Friday, September 5, 2008 10:42 PM
  • This is great! Thank you so much for your help!
    Monday, September 8, 2008 3:42 PM
  • Thanks Jonathan!

    Was able to use part of the above to fix a report problem I was having, namely pulling data and grouping by 30 min. intervals.
    Again, excellent work, may God bless you!

    Wednesday, August 5, 2009 6:57 PM