Complex Query - III

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Preet Kanwaljit Singh (Singh_at_discussions.microsoft.com)
Date: 06/29/04 

Date: Tue, 29 Jun 2004 02:08:01 -0700

This is my complete query:
-------------------------------

SELECT b.fld_agent_nm, count(*) AS 'Offer',
SUM(CASE WHEN a.fld_call_status = 'Ansd' THEN 1 ELSE 0 END) AS 'Answd',
SUM(CASE WHEN a.fld_call_status = 'Abnd' THEN 1 ELSE 0 END) AS 'Abndd',
(avg(a.fld_talk_time)/60) AS 'AvgTalk', avg(a.fld_ring_time) AS 'AvgRing',
((sum(a.fld_talk_time) + sum(a.fld_wrap_time))/3600) as 'Login'
SUM(CASE WHEN a.fld_ring_time < 10 THEN 1 ELSE 0 END) AS 'R10',
SUM(CASE WHEN a.fld_ring_time between 10 and 20 THEN 1 ELSE 0 END) AS 'R20',
SUM(CASE WHEN a.fld_ring_time between 20 and 30 THEN 1 ELSE 0 END) AS 'R30',
SUM(CASE WHEN a.fld_ring_time > 30 THEN 1 ELSE 0 END) AS 'R40'
FROM tbl_agents_callwise_performance a inner join tbl_agent_master b on
a.fld_agent_no = b.fld_agent_no
where a.fld_agent_no between 1000 and 1050 and
a.fld_date_time between getdate()-1 and getdate()
group by b.fld_agent_nm

-----------------------
My problem is here:
-----------------------
((sum(a.fld_talk_time) + sum(a.fld_wrap_time))/3600) as 'Login'

-----------------------
suggest a suitable solution, thanks in advance.

Relevant Pages

  • Re: correlated subquery in the crosstab
    ... FROM (tblStudent INNER JOIN (tblClass INNER JOIN ... values from the outer query and I have made an alias for it. ... I have students, courses, exam groups containing exams of courses, ... that the crosstab query in access has much more capablities than ...
    (microsoft.public.access.queries)
  • Re: correlated subquery in the crosstab
    ... I solved my problem using stored queries to act as subqueries. ... FROM (tblStudent INNER JOIN (tblClass INNER JOIN ... values from the outer query and I have made an alias for it. ... that the crosstab query in access has much more capablities than ...
    (microsoft.public.access.queries)
  • Re: correlated subquery in the crosstab
    ... The first query ... TRANSFORM FirstAS FirstOfscore ... FROM tblStudent INNER JOIN (((tblEduYear INNER JOIN tblExamGrp ON ... that the crosstab query in access has much more capablities than ...
    (microsoft.public.access.queries)
  • Re: Matching records for an update query
    ... then the update query would look something like ... UPDATE RegisteredMembers INNER JOIN BusinessChanges ...
    (microsoft.public.access.queries)
  • Re: Ranking query
    ... I expect that using a named query (as opposed to its SQL) will be OK. ... INNER JOIN qryRepairs AS I2 ...
    (microsoft.public.access.queries)