#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Posts
    2
    Rep Power
    0

    Help with 15 minute count query


    I am a novice when it comes to SQL. I am basically able to build reports using the query builder but have been asked to do something more complex.

    Essentially I have a table with two columns, DateAdded and Name. The "Name" column contains a list of system names and "DateAdded" contains the date/time the system was updated.

    Name DateAdded
    CME 05/10/2010 11:44
    Routing Rules 05/10/2010 11:54
    WFM 05/10/2010 11:54
    Avaya 05/10/2010 12:04
    CME 05/10/2010 12:04
    Avaya 05/10/2010 12:04
    CME 05/10/2010 12:04
    Routing Rules 05/10/2010 12:04
    WFM 05/10/2010 12:04
    Avaya 05/10/2010 12:54



    I need to produce a stored procedure that returns the number of changes per 15 minute interval per system.


    CME Routing Rules WFM Avaya
    11:30 1
    11:45 1 1
    12:00 2 1 1 2
    12:15
    12:30
    12:45 1


    I have been given the following code to help. It seems to return the number of changes, but does not split it down by system. The person who gave me the code is not available to help me further - can anyone assist.


    ALTER procedure [dbo].[ChangesByIntervalGK]
    @in_date datetime
    as
    begin
    declare @timesteps table (interval varchar(5), howmany int)

    declare @num int
    select @num = 0
    while @num < 96
    begin
    insert into @timesteps values (convert(varchar(5), dateadd(minute, @num*15, 0),8), 0)

    select @num = @num + 1
    end

    select innerquery.interval, max(innerquery.howmany) howmany from
    (

    select * from @timesteps

    union

    select
    convert(varchar(5), dateadd(minute, datediff(minute,0,Addeddate) / 15 * 15, 0), 8) interval,
    count(*) howmany

    from ConfigurationItemLatest

    where Addeddate between @in_date and dateadd(day, 1, @in_date)
    group by convert(varchar(5), dateadd(minute, datediff(minute,0,Addeddate) / 15 * 15, 0), 8)

    ) innerquery

    group by innerquery.interval

    end
  2. #2
  3. manwich
    Devshed Novice (500 - 999 posts)

    Join Date
    Oct 2003
    Location
    Canadanistan
    Posts
    578
    Rep Power
    385
    Someone might want to move this over to the MSSQL forum, since that's what this is.

    You should be able to change the grouping by adding the 'name' column to both the SELECT and GROUP BY lines.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Posts
    2
    Rep Power
    0
    Originally Posted by fubes2000
    Someone might want to move this over to the MSSQL forum, since that's what this is.

    You should be able to change the grouping by adding the 'name' column to both the SELECT and GROUP BY lines.
    Thanks for trying to help. As a complete novice to SQL, I notice that there seems to be several selects and group bys.

    Any help with the code or suggestion as to where I should be making the changes would be appreciated.

    Apologies if this is posted in the wrong place, just desperate for help

IMN logo majestic logo threadwatch logo seochat tools logo