Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2015
    Posts
    3

    Unanswered: Query help with SQL reports

    Ok so a little background. I took over for another DBA who is no longer here. He created some Custom Reports for a phone system using MySQL. He created the reports using Sql Server reporting services which I have never used. So here's my issue. He has one report setup, seems to work fine and gets what the department needs. It's pulling call stats and things for the current month and gets averages and totals for them to view. This query that he made calls the MySQL database through an ODBC connection that is setup in the SSRS server. I really don't know what to make of the query as I'm pretty new to all of this honestly. I was wondering if there is just something I can edit in the following query that will pull the previous month's data and not this current month? Let me know what you all think.

    Code:
    Select * into #wrap from openquery(SHORETEL_CCIR,'SELECT  e.g_event_id,wc.w_name,e.event_id,ep.agent_id,
    a.a_name,et.event_name,g.g_name,e.event_time,rc.rc_name,month(e.event_time) as ''Month'',YEAR(e.event_time) as ''Year'', field_name
    FROM agent a
    LEFT JOIN event_parties ep
    on a.agent_id = ep.agent_id
    LEFT JOIN events e ON ep.g_event_id=e.g_event_id
    LEFT JOIN release_codes rc ON e.release_id=rc.rc_id
    LEFT JOIN event_types et ON e.event_id=et.event_id
    LEFT JOIN wu_code wc ON wc.w_number=e.w_number
    LEFT JOIN ivr_apps ia ON ia.ivr_app_id=e.ivr_app_id
    LEFT JOIN cause_code cc ON cc.cause_id=e.cause_id
    LEFT JOIN dial_lists dl ON dl.dl_id=e.dl_id
    LEFT JOIN event_call_profile ecp ON ecp.g_event_id=e.g_event_id
    LEFT JOIN cp_fields cf ON cf.field_id = ecp.field_id
    LEFT JOIN ccs_hdr ch ON ch.ccs_id=e.ccs_id
    LEFT JOIN services s ON s.srv_id=e.srv_id
    LEFT JOIN irn i ON i.irn_id=e.irn_id
    LEFT JOIN event_groups eg ON eg.g_event_id=e.g_event_id
    LEFT JOIN grp g ON g.group_id=eg.group_id
    where year(event_time) =year(now()) 
    
      ' ) 
    			delete from #wrap where a_name not in (select distinct a_name from #wrap where g_name in ('Cust Serv','CS Overflow', 'Claims', 'Rep', 'LTC') and event_name like '%Agent%' AND a_name not like 'Ann Jegerlehner' )
    
      Select month, count(event_name)  as 'Answer' into #Answer from #wrap  
    where g_name !='NULL' AND field_name = 'Alternative Call ID'  
    AND event_name = 'agent answer'
    group by month
    
    
      select month,CONVERT(char(8), DATEADD(second, AverageWrap,'0:00:00'),108) as AverageWrap, AverageWrap as AveWSec into #avgWrap  from (Select w.month,
      SUM(
      datediff(second,W.event_time,w2.event_time))/COUNT(w.a_name) as AverageWrap 
      		
      FROM #Wrap W
      	cross apply
    		(
    			Select top 1 *
    			from #Wrap W3
    			where w3.agent_id = w.agent_id 
    			and w3.event_time < w.event_time
    			and (w3.event_id = 4 OR w3.event_id = 2)
    			order by w3.event_time desc	
    
    		) w3 
    	cross apply
    		(
    			Select top 1 *
    			from #Wrap W2
    			where w.agent_id = w2.agent_id 
    			and w2.event_time > w.event_time
    			and w2.event_id = 14
    		) w2
    
    	where w.event_id = 34 
    	 group by w.month)w
    
    	
     select month, CONVERT(char(8), DATEADD(second, AverageHold,'0:00:00'),108) as AverageHold, AverageHold as AvgHSec into #avgHold from (Select w.month,
      SUM(
      datediff(second,W.event_time,w2.event_time))/COUNT(w.a_name) as AverageHold 
      		
      FROM #Wrap W
    	cross apply
    		(
    			Select top 1 *
    			from #Wrap W2
    			where w.agent_id = w2.agent_id 
    			and w2.event_time > w.event_time
    			and (w2.event_id != 19)
    			order by w2.event_time 
    		) w2
    
    	where w.event_id = 19 
    	 group by w.month)x
    
     select month, CONVERT(char(8), DATEADD(second, AverageTreat,'0:00:00'),108) as AverageTreat, AverageTreat as AvgTSec into #avgTreat from (Select w.month,
      SUM(
      datediff(second,W.event_time,w2.event_time))/COUNT(w.a_name) as AverageTreat 
      		
      FROM #Wrap W
    	cross apply
    		(
    			Select top 1 *
    			from #Wrap W2
    			where w.agent_id = w2.agent_id 
    			and w2.event_time > w.event_time
    			and (w2.event_id =14)
    			order by w2.event_time 
    		) w2
    
    
    	where w.event_id = 4 and w.field_name = 'Alternative Call ID'
    	 group by w.month)x
    
    select month, 
    case when TotalTreat> (24*60*60) 
        	then 
        		cast(TotalTreat/86400 as varchar(50))+':'+
      Convert(VarChar, DateAdd(S, TotalTreat, 0), 108) 
        else
        		convert(varchar(8), dateadd(second, TotalTreat, '0:00:00'), 108) 
        end as TotalTreat, TotalTreat/3600.00 as TotalTHrs into #totalTreat from (Select w.month,
      SUM(
      datediff(second,W.event_time,w2.event_time)) as TotalTreat 
      		
      FROM #Wrap W
    	cross apply
    		(
    			Select top 1 *
    			from #Wrap W2
    			where w.agent_id = w2.agent_id 
    			and w2.event_time > w.event_time
    			and (w2.event_id =14)
    			order by w2.event_time 
    		) w2
    
    
    	where w.event_id = 4 and w.field_name = 'Alternative Call ID'
    	 group by w.month)x
    
     select month, CONVERT(char(8), DATEADD(second, AverageTalk,'0:00:00'),108) as AverageTalk, AverageTalk as AvgTalkSec into #avgTalk from (Select w.month,
      SUM(
      datediff(second,W.event_time,w2.event_time))/COUNT(w.a_name) as AverageTalk 
      		
      FROM #Wrap W
    	cross apply
    		(
    			Select top 1 *
    			from #Wrap W2
    			where w.agent_id = w2.agent_id 
    			and w2.event_time > w.event_time
    			and (w2.event_id =34)
    			order by w2.event_time 
    		) w2
    
    
    	where w.event_id = 4 and w.field_name = 'Alternative Call ID'
    	 group by w.month)x
    
      Select month, CONVERT(char(8), DATEADD(second, AverageRelease,'0:00:00'),108) as AverageRelease, AverageRelease as AvgReleaseSec into #avgRelease from (Select w.month,
      sum(datediff(second,W.event_time,w2.event_time))/COUNT(w.a_name) as AverageRelease		
    FROM #Wrap W 
    	cross apply
    		(
    			Select top 1 *
    			from #Wrap W2
    			where w.agent_id = w2.agent_id 
    			and w2.event_time > w.event_time
    			and (w2.event_id = 33 or w2.event_id = 32 or w2.event_id = 30)
    		Order by w2.event_time
    		) w2
    
    	where w.event_id = 32 
    	Group By w.month)x
    
      Select month, 
      case when TotalRelease> (24*60*60) 
        	then 
        		cast(TotalRelease/86400 as varchar(50))+':'+
      Convert(VarChar, DateAdd(S, TotalRelease, 0), 108) 
        else
        		convert(varchar(8), dateadd(second, TotalRelease, '0:00:00'), 108) 
        end as TotalRelease, TotalRelease/3600.00 as TotalReleaseHrs into #totalRelease	 from (Select w.month,
      sum(datediff(second,W.event_time,w2.event_time)) as TotalRelease	
    FROM #Wrap W 
    	cross apply
    		(
    			Select top 1 *
    			from #Wrap W2
    			where w.agent_id = w2.agent_id 
    			and w2.event_time > w.event_time
    			and (w2.event_id = 33 or w2.event_id = 32 or w2.event_id = 30)
    		Order by w2.event_time
    		) w2
    
    	where w.event_id = 32 
    	Group By w.month)x
    
       select month, CONVERT(char(8), DATEADD(second, AverageRing,'0:00:00'),108) as AverageRing, AverageRing as AvgRingSec, totalringsec, Count  into #ring from (Select w.month,
      SUM(
      iif(datediff(second,W.event_time,w2.event_time) > 12,12,
      datediff(second,W.event_time,w2.event_time)))/COUNT(w.a_name) as AverageRing,
       SUM(
      iif(datediff(second,W.event_time,w2.event_time) > 12,12,
      datediff(second,W.event_time,w2.event_time))) as totalringsec ,
      count(w.a_name) as Count
      		
      FROM #Wrap W
    	cross apply
    		(
    			Select top 1 *
    			from #Wrap W2
    			where w.agent_id = w2.agent_id 
    			and w2.event_time >= w.event_time
    			and (w2.event_id =4)
    			and w2.field_name = 'Alternative Call ID'
    			order by w2.event_time 
    		) w2
    
    
    	where w.event_id = 5 and w.field_name = 'Alternative Call ID'
    	 group by w.month)x
    
    select w.month,a.answer as Answered,w.AverageWrap,w.AveWSec,  isNULL(h.AverageHold,'00:00:00')as AverageHold, isNULL(h.AvgHSec,0) as AvgHSec, 
    isNULL(t.AverageTreat,'00:00:00')as AverageTreat, isNULL(t.AvgTSec,0)as AvgTSec,tt.totalTreat, tt.totalTHrs,
    CONVERT(varchar, DATEADD(s, t.AvgTSec-w.AveWSec, 0), 108)  as AverageTalk, tk.AvgTalkSec,ar.AverageRelease, ar.AvgReleaseSec, tr.TotalRelease, tr.TotalReleaseHrs,
    r.AverageRing, r.AvgRingSec
    
    FROM
    #avgWrap w
    LEFT JOIN 
    #avgHold h ON w.month=h.month
    Left Join 
    #avgTreat t ON w.month=t.month
    Left Join 
    #Answer a ON w.month=a.month
    Left Join 
    #avgTalk tk ON w.month=tk.month
    Left Join
    #totalTreat tt ON w.month=tt.month
    Left Join
    #avgRelease ar ON w.month = ar.month
    left Join 
    #totalRelease tr ON w.month = tr.month
    left join
    #ring r ON w.month = r.month
    --where w.a_name in (@agent)
       drop table #wrap
    	drop table #avgHold
    	drop table #avgWrap
    	drop table #avgTreat
    	drop table #answer
    	drop table #avgTalk
    	drop table #totalTreat
    	drop table #avgRelease
    	drop table #totalRelease
    	drop table #ring

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Replace this
    Code:
    ...
    where year(event_time) =year(now())
    ...
    With
    Code:
    ...
    WHERE  event_time <  Cast(DATE_FORMAT(Now(), '%Y-%m-01') As datetime)
    AND    event_time >= DATE_ADD(Cast(DATE_FORMAT(Now(), '%Y-%m-01') As date) INTERVAL -1 MONTH)
    ...
    Note that this is MySQL syntax.

    Now() returns the current date. Consider Now() = 2015-01-08. This yields:
    Code:
    WHERE  event_time <  '2015-01-01'
    AND    event_time >= '2014-12-01'
    It does this by:
    - Formatting the date as a string, taking the year and month portion of the current date and using "01" as the day.
    - Casting this back to a datetime value
    - This gives us the first of the current month.
    - To get the first of the previous month, we subtract one month using the DATE_ADD function.
    Last edited by gvee; 01-08-15 at 06:15.
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2015
    Posts
    3
    Hey thanks so much for the reply. I tried both your solutions, and it's griping about the < sign in the first expression though saying it's incorrect syntax? It's saying invalid start name character. Any ideas? I bolded where it is saying that message.

    WHERE event_time < Cast(DATE_FORMAT(Now(), '%Y-%m-01') As datetime)
    AND event_time >= DATE_ADD(Cast(DATE_FORMAT(Now(), '%Y-%m-01') As date) INTERVAL -1 MONTH
    Last edited by jcosley; 01-08-15 at 10:42.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Try this:
    Code:
    WHERE event_time < Cast(DATE_FORMAT(Now(), ''%Y-%m-01'') As datetime)
    AND event_time >= DATE_ADD(Cast(DATE_FORMAT(Now(), ''%Y-%m-01'') As date) INTERVAL -1 MONTH
    Basically I've doubled up the apostrophes to escape them in the OpenQuery().
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2015
    Posts
    3
    Just an update, this was the solution I was looking for.

    Code:
    where event_time BETWEEN DATE_FORMAT(NOW() - INTERVAL 1 MONTH, ''%Y-%m-01 00:00:00'') 
    AND DATE_FORMAT(LAST_DAY(NOW() - INTERVAL 1 MONTH), ''%Y-%m-%d 23:59:59'')

Posting Permissions

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