Page 1 of 2 12 LastLast
Results 1 to 15 of 23

Thread: Complex Query

  1. #1
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364

    Unanswered: Complex Query

    Hi Guys

    Hope you can help with this - it's certainly got me scratching my head.

    I'm Querying a Call Centre Database

    I have a Table of Call Data with a Start Time & End Time of the Call.

    (CallID,Started,Ended)
    Code:
    10942086 2007-04-01 00:01:09.000 2007-04-01 00:11:31.000
    1003855355 2007-04-01 00:01:24.000 2007-04-01 00:01:24.000
    10942071 2007-04-01 00:01:25.000 2007-04-01 00:02:43.000
    10942271 2007-04-01 00:02:57.000 2007-04-01 00:05:01.000
    10942283 2007-04-01 00:05:54.000 2007-04-01 00:06:50.000
    10942079 2007-04-01 00:07:15.000 2007-04-01 00:07:46.000
    10942287 2007-04-01 00:07:30.000 2007-04-01 00:08:12.000
    10942289 2007-04-01 00:07:49.000 2007-04-01 00:08:33.000
    I'm trying to produce Stats that tell me how many Calls were live in any one given minute.

    Ultimately I will be producing a Line Graph of No of Calls Connected grouped by Minute.

    I've gone as far as creating a temp table with every minute in a month with the following query maybe to join to but not sure if this will help me.
    Code:
     
    WHILE(@cnt <= 43200)
    BEGIN
    SELECT @MaxDate =DATEADD(mi,1,MAX(DTBlock))FROM AprilMinutes
    INSERTINTO AprilMinutes VALUES(@MaxDate,NULL)
    SET @cnt = @Cnt +1 
    END

    Which produces a nifty little table with
    Code:
    01/04/2007 00:09:00
    01/04/2007 00:10:00
    01/04/2007 00:11:00
    01/04/2007 00:12:00
    01/04/2007 00:13:00
    01/04/2007 00:14:00
    01/04/2007 00:15:00
    01/04/2007 00:16:00
    If one individual Call Spans 2 minutes I'll count it as 1 in the first minute & 1 in the second minute.

    Overall I'm trying analyze how many telephone lines we need

    Any Help much, much appreciated

    Thanks

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select AprilMinutes.datetimecolumn 
         , count(CallData.StartTime) as active_calls
      from AprilMinutes 
    left outer
      join CallData
        on AprilMinutes.datetimecolumn 
           between CallData.StartTime 
               and CallData.EndTime
    group
        by AprilMinutes.datetimecolumn
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Tehe

    Thanks r937 - Though art a veritable GURU of SQL'ness

    My meager Server is crunching the TOP 100 as we speak (creak, grind)

    However

    What will happen if a call arrives at eg. 00:05:10 and Ends at 00:05:55

    methinks it will not get counted ?

    please tell me i'm wrong
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by GWilliy
    please tell me i'm wrong
    nope, thou art correct

    here's an idea (too busy to test it meself): in the query round the start time down to the nearest previous minute, and round the end time up to the next minute, then instead of BETWEEN, use >= and <
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Whooooaa

    Close but no Bannana's

    The suggestion produces

    Code:
    (DateTimeBlock,CountOf)
    2007-04-01 00:00:00.000 2
    2007-04-01 00:01:00.000 4
    2007-04-01 00:02:00.000 5
    2007-04-01 00:03:00.000 3
    2007-04-01 00:04:00.000 7
    2007-04-01 00:05:00.000 8
    2007-04-01 00:06:00.000 8
    2007-04-01 00:07:00.000 8
    2007-04-01 00:08:00.000 8
    2007-04-01 00:09:00.000 7
    From
    Code:
    
    (CallID,Start,End,FormatedStart,FormatedEnd)
    10942086 2007-04-01 00:01:09.000 2007-04-01 00:11:31.000 2007-04-01 00:01:00.000 2007-04-01 00:12:00.000
    10038553 2007-04-01 00:01:24.000 2007-04-01 00:01:24.000 2007-04-01 00:01:00.000 2007-04-01 00:02:00.000
    10942071 2007-04-01 00:01:25.000 2007-04-01 00:02:43.000 2007-04-01 00:01:00.000 2007-04-01 00:03:00.000
    10942271 2007-04-01 00:02:57.000 2007-04-01 00:05:01.000 2007-04-01 00:02:00.000 2007-04-01 00:05:00.000
    10942283 2007-04-01 00:05:54.000 2007-04-01 00:06:50.000 2007-04-01 00:05:00.000 2007-04-01 00:07:00.000
    10942079 2007-04-01 00:07:15.000 2007-04-01 00:07:46.000 2007-04-01 00:07:00.000 2007-04-01 00:08:00.000
    10942287 2007-04-01 00:07:30.000 2007-04-01 00:08:12.000 2007-04-01 00:07:00.000 2007-04-01 00:09:00.000
    10942289 2007-04-01 00:07:49.000 2007-04-01 00:08:33.000 2007-04-01 00:07:00.000 2007-04-01 00:09:00.000
    10942090 2007-04-01 00:09:52.000 2007-04-01 00:13:40.000 2007-04-01 00:09:00.000 2007-04-01 00:14:00.000
    10942094 2007-04-01 00:15:18.000 2007-04-01 00:15:45.000 2007-04-01 00:15:00.000 2007-04-01 00:16:00.000
    10942306 2007-04-01 00:15:37.000 2007-04-01 00:16:26.000 2007-04-01 00:15:00.000 2007-04-01 00:17:00.000
    10942104 2007-04-01 00:17:51.000 2007-04-01 00:18:29.000 2007-04-01 00:17:00.000 2007-04-01 00:19:00.000
    I Estimate it should produce
    Code:
    2007-04-01 00:00:00.000	0
    2007-04-01 00:01:00.000	3
    2007-04-01 00:02:00.000	3
    2007-04-01 00:03:00.000	2
    2007-04-01 00:04:00.000	2
    2007-04-01 00:05:00.000	3
    Do you think it will help if I post some code to create Test Tables n Data

    I had hoped maybe someone familiar with a Call Centre / telephone type operation would have done this kind of thing.

    I'm not sure why your suggestion does'nt work - I'll continue studying it.



    Thanks
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  6. #6
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Oh NO !!

    Everyone seems to have given up

    Surely there's a SQL God out there who could make a constructive suggestion.

    Come on Chaps OR I'll break out the CURSORS - tehe.

    Only Joking.
    Code:
    SELECT CASE WHEN @GoodSugestions >= 1
    THEN
      'Thankyou'
    ELSE
     'Struggle On'
    END
    Thanks to r937, you've brought me very close.

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    can you show the query you developed based on my suggestion in post #4
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Here's the RAW code exactly as used

    Code:
    
    select top 10 AprilMinutes.DTBlock 
    ,count(callActionSummary.NotiTime)as active_calls
    from AprilMinutes 
    left outer
    join callActionSummary
    on AprilMinutes.DTBlock >=LEFT(dbo.FormatDate(NotiTime,'yyyy-mm-dd hh:mm:ss.Ms'),16)+':00.000'
    and AprilMinutes.DTBlock <DATEADD(mi,1,LEFT(dbo.FormatDate(DATEADD(ss,ssopconn,NotiTime),'yyyy-mm-dd hh:mm:ss.Ms'),16)+':00')
    group
    by AprilMinutes.DTBlock 
    orderby AprilMinutes.DTBlock
    
    Do you think possibly the Date manipulation / dbo.FormatDate is affecting it ?

    ssopconn is Int (operator connected seconds)
    NotiTime is Datetime (Time Call arrived)
    dbo.FormatDate returns nVarChar (custom UDF) hopefuly implicit conversion to date ??

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by GWilliy
    Do you think possibly the Date manipulation / dbo.FormatDate is affecting it ?
    it's a possibility

    actually, now that i think about it, you would only have to round the start time down to the current minute, to have DTBlock >= pick it up

    then if ssopconn doesn't extend the time into the next minute, the call will still be counted in that minute


    anyhow, there is a much better way to round down (would've mentioned it yesterday but i was too busy to look it up)

    SELECT DATEADD(<datepart>, DATEDIFF(<datepart>, <refdatetime>, <datetime>), <refdatetime>)

    which simply adds back the same number of whole dateparts to the reference datetime as there are between the reference datetime and the given datetime

    the excess just disappears

    example:

    SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) returns midnight of first day of current month (try it )

    reference date of 0 equals the sql server zero datetime, whatever that may be, jan 1 1900 or whatever

    it doesn't actually matter what the exact zero datetime is, because it subtracts back out

    expression for your situation:

    DATEADD(mi, DATEDIFF(mi, '2000-01-01', NotiTime), '2000-01-01')

    reference date is chosen closer to expected values, although zero still works

    this expression rounds NotiTime down to current minute and is a lot more efficient than string manipulation

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    select a.datetimecolumn, count(b.StartTime)
    from AprilMinutes a left join CallData b
    on a.datetimecolumn between convert(varchar(17),b.StartTime ,113)
    and b.EndTime --dateadd(ms,59997, convert(varchar(17),b.EndTime,113) )
    group by a.datetimecolumn
    Last edited by pdreyer; 05-24-07 at 08:18. Reason: No need to up EndTime

  11. #11
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Sorry Chaps

    Not had time to test these yet but will do tomorrow

    Someone decided to change the CodePages on the Production SyBase servers from 850 to 1252 last night & hoped it would'nt have much of an effect - - lol

    I'll keep you posted re results of your suggestions

    thanks

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  12. #12
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Quote Originally Posted by r937
    expression for your situation:
    DATEADD(mi, DATEDIFF(mi, '2000-01-01', NotiTime), '2000-01-01')
    is a lot more efficient than string manipulation
    Revised query
    Code:
    select a.DTBlock, count(b.StartTime)
    from AprilMinutes a 
    left join CallData b 
      on a.DTBlock between dateadd(mi,datediff(mi,0,b.StartTime),0)
                       and b.EndTime 
    group by a.DTBlock

  13. #13
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    WOW

    r937 & pdreyer - Though art Veritable GODS of SQL ness

    Worked a Treat & triple fast too.

    r937 had it with the Left Outer and pdreyer swooped in with the
    Code:
    between dateadd(mi,datediff(mi,0,b.StartTime),0) and b.EndTime
    I'm humbled in your presence - Grovel, Snivel

    Many Thanks to the both of you - tis muchly appreciated

    Grant
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  14. #14
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Just to Round things Off

    This is what I did with the Code

    Code:
    
    ALTERPROCEDURE LineUtilisation @FromDate DateTime, @ToDate DateTime
    AS
    SETNOCOUNTON
    DECLARE @TimeBlock TABLE(DTBlock DateTime) 
    DECLARE @CallData TABLE(Call_Def Int,StartTime DateTime,EndTime DateTime) 
    DECLARE @MinReq Int
     
    INSERTINTO @TimeBlock SELECT @FromDate
    SET @MinReq =DATEDIFF(mi,@FromDate,@ToDate)
    WHILE(@MinReq > 1)
    BEGIN
    INSERTINTO @TimeBlock 
    SELECTDATEADD(mi,1,MAX(DTBlock))FROM @TimeBlock 
    SET @MinReq = @MinReq - 1
    END
    INSERTINTO @CallData
    SELECT
    Call_Def,
    NotiTime StartTime,
    DATEADD(ss,SSTotConn,NotiTime) EndTime 
    FROM CallActionSummary
    WHERE NotiTime between @FromDate AND @ToDate
    ANDRTRIM(LTRIM(CallCode ))NOTIN('','B','F','4','Q','H','O','P','16')-- Background
    AND ssOpConn > 0
    AND ReasonRef <> 5
    ORDERBY 2
    SELECT a.DTBlock,COUNT(b.StartTime) CallQty
    FROM @TimeBlock a 
    LEFTJOIN @CallData b 
    ON a.DTBlock BETWEENDATEADD(mi,DATEDIFF(mi,0,b.StartTime),0)
    AND b.EndTime 
    GROUPBY a.DTBlock
    ORDERBY 1
    
    Will Probably end up tidying it up - poss with some indexing but it works fast enough at the Mo.

    PS Mars Bar for anyone who can tell me how to LOSE THE WHILE LOOP !!

    GW
    Last edited by GWilliy; 05-29-07 at 16:26.
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by GWilliy
    Will Probably end up tidying it up
    some indentation wouldn't hurt, man
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •