Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2008
    Posts
    42

    Unanswered: Multiple date ranges in one query

    Good evening!

    I am using SQL 2000.

    I am trying to create a select statement where I sum the same field several times based on date range.

    My situation....

    I am looking at call center data. I would like to create a query where I sum the number of calls handled at the agent level for multiple date ranges.

    For example johny took 15 calls in week 1, 20 calls in week 2, and 30 calls in week 3. I tried using the following subquery, but it didn't work out too good. My results have the same value for sum(nch) on every record.

    Any help would be greatly appreciated.

    Code:
    select 
    pid, 
    swid, 
    	(select	
    	sum(nch) as nch
    	from agent
    	where netdate between '12/01/08' and '12/07/08') as week_1,
    	
    
    	(select 
    	sum(nch) as nch
    	from agent
    	where netdate between '12/08/08' and '12/14/08') as week_2,
    
    	(select 
    	sum(nch) as nch
    	from agent
    	where netdate between '12/15/08' and '12/22/08') as week_3
    
    from agent
    
    group by pid, swid

  2. #2
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    u will have to relate the inner select with the outer one ...

    Code:
    select 
    pid, 
    swid, 
    	(select	
    	sum(nch) as nch
    	from agent
    	where netdate between '12/01/08' and '12/07/08' 
                 AND  swid=Outer.swid and pid=Outer.pid) 
                 as week_1,
    
    .....
    .....	
    
    from agent as Outer
    group by pid, swid

    there r other simpler ways like

    Code:
    select pid, swid , sum(nch), datepart(wk,netdate)
    from agent
    group by pid, swid, datepart(wk,netdate)

  3. #3
    Join Date
    Nov 2008
    Posts
    42
    Thanks, your way is much easier!

    I have another question. If I have a date field in each record, would I be able to select week 1 after netdate, week 2 after netdate....

    For example Johnie has a netdate of 12/01 and jane has a netdate of 12/07. I would want to select weeks 1 and 2 after 12/01 for Johnie and weeks 1 and 2 after 12/07 for jane.


    Thanks again
    Drew

Posting Permissions

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