none
T-SQL query, average of daily time periods over a date range

    Question

  • Hi,

    I'm building a report in Crystal Reports using a SQL command against a T-SQL 2005 telephony database.

    I need to be able to run the query across a given datetime range, 6 months for example and bring back a 5 day display (Mon-Fri) with a group for every 15 minute interval in each day.

    The group figures need to contain an average of the amount of calls presented for each 15 minute interval on any day across the whole datetime range, so for example the Monday 10:00 - 10:15 figure would be an average of all calls presented in every 10:00-10:15 range on each of the Mondays that fall within the datetime range.

    I've got a query built now that gives total presented figures grouped by these intervals across one week but I can't figure out how to do this average function across a range.

    Does anyone have any idea how I'd go about accomplishing this? I'm pretty new to SQL but keen to learn so any pointers on functions to research etc would be very much appreciated.

    Thanks alot in advance, Andy.

    Query for detail view across one week included below for table/fields etc...

     

    SELECT

    count(DISTINCT Calls.SessionID) as Presented, min(Calls.startDateTime) as DateTime

    FROM
    Calls
    INNER JOIN QueueDetail
    ON Calls.sessionID =  Queues.sessionID
    AND Calls.sessionSeqNum =  QueueDetail.sessionSeqNum
    AND Calls.nodeID =  QueueDetail.nodeID
    AND Calls.profileID =  QueueDetail.profileID
    INNER JOIN SQueue
    ON QueueDetaill.targetID =  SQueue.recordID
    WHERE
    CONVERT(varchar(10), Calls.startDateTime, 101) = {?Sample Start Date}
    and
    QueueDetail.targetType = 0
    GROUP BY
    year(startDateTime),
    month(startDateTime),
    day(startDateTime),
    datepart(hh,startDateTime),
    datepart(mi,startDateTime)/{?Sample Period}

    Tuesday, September 21, 2010 10:01 AM

All replies

  • I will divide this problem into 3 parts.

    1. Dividing based on weekday

                Datediff(Dd,0,startDateTime) % 7 will returns 0-6 from Monday-sunday. So, if this value is btn 0-4, we can take it as Weekday

    2. Dividing based on 15 Minutes interval.

               This expression will give you the 15th Minute Group the date belongs dateadd(minute, datediff(minute, 0, startDateTime) / 15 * 15, 0)

    3. Calculating Average.

               I really didn't udnerstand what you meant here.... Avg means are you trying to calculate no of calls, avg. no of calls/ avg. no of duration???

    There is an Avg() function is there..... If u are trying to calculate avg. no of calls on that day in btn that time, we can do like below.

    Suppose, the user queried for 6 months, In 6 months, we will have around 26 weeks, so 26 mondays, 26 tuesdays etc. so no of calls/26 will give the avg.

    Totally we can write a query like below.

    declare

     

    @StartDate datetime = '1/1/2010'

    declare

     

    @Enddate datetime = '6/30/2010'

    declare

     

    @NoOfWeeks int = datediff(dd,@StartDate,@EndDate)/7

    select

     

    case

     

    when t1.DayGroup = 0 then 'Monday'

     

    when t1.DayGroup = 1 then 'Tuesday'

     

    when t1.DayGroup = 2 then 'Wednesday'

     

    when t1.DayGroup = 3 then 'Thursday'

     

    when t1.DayGroup = 4 then 'Friday'

    end

     

    ,

     

    TimeGroup,COUNT(*)/@NoOfWeeks as Avgcalls

    from

     

    yourtable t

    cross

     

    apply (select DATEDIFF(dd,0,StartDatetime) % 7 as DayGroup,

    dateadd

     

    (minute, datediff(minute, 0, startDateTime) / 15 * 15, 0) as TimeGroup)t1

    where

     

    t1.DayGroup <= 4

    group

     

    by DayGroup,TimeGroup

     

    Tuesday, September 21, 2010 10:13 AM
  • In cases like this where you need certain time periods, it may be a good start to have a table with all the start/end times defined in them. After this, the joins and aggregations become a little easier. I do a lot of reporting of this sort and it's handy to have a lookup like this when it comes to writing reports in SSRS or Crystal Reports

    DBA, Datacruncher and Boardgamer
    Tuesday, September 21, 2010 10:13 AM
  • Thanks for the replys.

    ramireddy, by the average I mean if there were 7 mondays in a given date range for example then there would be 7 instances of the 10:00-10:15 period.

    So the average would be the a sum of each periods presented calls / 7.

    bb12321, I could probably get a lookup table created for this but as it's a customer db it would take some time and definitely wouldn't be in place in time for this requirement. I'll have a look though as there will probably be future requirements like this.

    I got a few errors from the code above so will work through them, see what I can get and post back later.

     

    Tuesday, September 21, 2010 10:35 AM
  • Did you notice you used the word "field" instead of "column" when they are totally different things? You don't have a set-oriented mindset yet. You split up a temporal scalar value into parts, just like you did in COBOL or BASIC 40 years ago, when they were strings. In fact, you CAST a temporal value into a string!  You also used DATETIME, a keyword, AS column name -- that is bad programming in any language. You have " Queues.session_id" but that table is not in the query. 

    SQL is a data base language, so using temporal parts computations is not the best way to go. Instead, build a reporting periods table once instead of computing things one row after another. Here is a skeleton. Since you did not post any DDL, I get to guess and make up stuff; that is part of the netiquette. I could not see why we needed all those other tables when all we wanted was the starting time in the Calls. Why do you use COUNT(DISTINCT) on the session id? Are they reused so they are not really identifiers?   

    CREATE TABLE ReportPeriods
    (rpt_per_start_datetime DATETIME NOT NULL PRIMARY KEY,
     rpt_per_start_time TIME NOT NULL ,
     rpt_per_end_datetime DATETIME NOT NULL,
     rpt_per_weekday INTEGER NOT NULL -- iso standards
       CHECK (rpt_per_weekday BETWEEN 1 AND 7),
     /* 1 for Monday to 7 for Sunday */
     ..); 

    The skeleton would look like this:

    WITH WeeklySessionCounts (pt_per_weekday, rpt_per_start_time, session_cnt)
    AS
    (SELECT RP.rpt_per_weekday, RP.rpt_per_start_time, COUNT(DISTINCT C.session_id)
      FROM ReportPeriods AS RP,
           Calls AS C
    /* insert other tables and predicates as  needed*/
     WHERE C.call_start_time 
          BETWEEN rpt_per_start_datetime AND rpt_per_end_datetime
    GROUP BY RP.rpt_per_weekday, RP.rpt_per_start_time0

    SELECT rpt_per_weekday, AVG( session_cnt) AS session_cnt_avg
      FROM WeeklySessionCounts;


    --CELKO-- Please post DDL, use ISO-11179 naming rules and format code so we can read it
    Tuesday, September 21, 2010 5:31 PM
  • No you're right Celko, although I've been reporting for a few years I'm pretty new to SQL and this is the first code related post I've made; with that in mind I have to ask, what's DDL?

    As for the other points, I'd copied in the query I used in the detail report,  but missed taking out the other tables (used for other values), and I've always used distinct counts before now out of habit more than anything but in this case the sessionID will always be unique.

     

    Wednesday, September 22, 2010 12:17 AM
  • Ah thanks Naom, my appologies then.

    DDL As below...

     

    USE [db_cra]
    GO
    /****** Object:  Table [dbo].[Calls]    Script Date: 09/22/2010 10:23:46 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[Calls](
        [sessionID] [decimal](18, 0) NOT NULL,
        [sessionSeqNum] [smallint] NOT NULL,
        [nodeID] [smallint] NOT NULL,
        [profileID] [int] NOT NULL,
        [contactType] [tinyint] NOT NULL,
        [contactDisposition] [tinyint] NOT NULL,
        [dispositionReason] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [originatorType] [tinyint] NOT NULL,
        [originatorID] [int] NULL,
        [originatorDN] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [destinationType] [tinyint] NULL,
        [destinationID] [int] NULL,
        [destinationDN] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [startDateTime] [datetime] NOT NULL,
        [endDateTime] [datetime] NOT NULL,
        [gmtOffset] [smallint] NOT NULL,
        [calledNumber] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [origCalledNumber] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [applicationTaskID] [decimal](18, 0) NULL,
        [applicationID] [int] NULL,
        [applicationName] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [connectTime] [smallint] NULL,
        [customVariable1] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [customVariable2] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [customVariable3] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [customVariable4] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [customVariable5] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [customVariable6] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [customVariable7] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [customVariable8] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [customVariable9] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [customVariable10] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [accountNumber] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [callerEnteredDigits] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [badCallTag] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [transfer] [bit] NULL,
        [redirect] [bit] NULL,
        [conference] [bit] NULL,
        [flowout] [bit] NULL,
        [metServiceLevel] [bit] NULL,
     CONSTRAINT [PK_Calls] PRIMARY KEY NONCLUSTERED
    (
        [sessionID] ASC,
        [sessionSeqNum] ASC,
        [nodeID] ASC,
        [profileID] ASC
    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]

    GO
    SET ANSI_PADDING OFF

    Wednesday, September 22, 2010 9:25 AM
  • Andy,

    The query i gave you will calculate the avg as you expected only...

    What hpnd to that one?? are you facing any probs?

    Wednesday, September 22, 2010 9:31 AM
  • Hi ramireddy,

    It failed on variable declaration but I've got it running now, thanks.

    Results aren't showing as many time periods during the days as there would have been traffic and most values are 0's or 1's.

    I'm having a look to see if I can fix it myself but am a bit snowed under with other work at the moment as well as this is a lower priority.

    I'll update back here when I have any progress.

    Thanks again all.

    Wednesday, September 22, 2010 10:02 AM