CODEWITHSUNDEEP
sqlt-sqlunion
thatstevedude
thatstevedude

Reputation: 195

Avoid writing multiple union statements

I have to grab a list of records which displays the total number of calls received by week for the last 8 weeks for each call type. Is there a better way of writing this in MSSQL? What terms or references should I look up?

I want to avoid having muliple unions that have different LIKES in the WHERE clause. 

SELECT 
    'Agent did not speak to Business Owner' AS QCDescription, 
    CONVERT(float ,SUM(CASE WHEN c.VerifiedDate BETWEEN @LastWeekStart AND @LastWeekEnd   THEN 1 ELSE 0 END)) [LastWeek]
FROM 
    dbo.[Call] c JOIN
    dbo.[User] u on c.UserID = u.UserID
WHERE 
    c.IsPassedQC = 0 
    AND (c.QCDate IS NOT NULL AND c.VerifiedDate > '11/4/2013')
    AND (c.CallCenterID = 0 OR c.CallCenterID = u.CallCenterID)
    AND  c.QCReason LIKE '%Agent did not speak to Business Owner%' --dbo.QCResason.Description 
UNION 
    SELECT 
    'Agent explained Radius' AS QCDescription, 
    CONVERT(float ,SUM(CASE WHEN c.VerifiedDate BETWEEN @LastWeekStart AND @LastWeekEnd   THEN 1 ELSE 0 END)) [LastWeek]
FROM 
    dbo.[Call] c 
    JOIN dbo.[User] u on c.UserID = u.UserID 
WHERE 
    c.IsPassedQC = 0 
    AND (c.QCDate IS NOT NULL AND c.VerifiedDate > '11/4/2013')
    AND (c.CallCenterID = 0 OR c.CallCenterID = u.CallCenterID)
    AND  c.QCReason LIKE '%Agent explained Radius%' --dbo.QCReason.Description

S

dbo.Call
CallID bigint
VerifiedDate datetime,
QCDate datetime
QCResason varchar(500),
QCUserID

dbo.QCReason
QCReasonID int,
Reason varchar(100),
Description(varchar(200)

Upvotes: 1

Views: 598

Answers (2)

Philip Kelley
Philip Kelley

Reputation: 40319

I'm slapping this together and have no way of testing it (syntax or viability), but something like this could work:

SELECT
    case
      when c.QCReason LIKE '%Agent explained Radius%' then 'Agent explained Radius'
      when c.QCReason LIKE '%Agent did not speak to Business Owner%' then 'Agent did not speak to Business Owner'
      else 'xxx'
    end  QCDescription,
    CONVERT(float ,SUM(CASE WHEN c.VerifiedDate BETWEEN @LastWeekStart AND @LastWeekEnd   THEN 1 ELSE 0 END)) [LastWeek]
FROM 
    dbo.[Call] c JOIN
    dbo.[User] u on c.UserID = u.UserID
WHERE 
    c.IsPassedQC = 0 
    AND (c.QCDate IS NOT NULL AND c.VerifiedDate > '11/4/2013')
    AND (c.CallCenterID = 0 OR c.CallCenterID = u.CallCenterID)
GROUP BY
    case
      when c.QCReason LIKE '%Agent explained Radius%' then 'Agent explained Radius'
      when c.QCReason LIKE '%Agent did not speak to Business Owner%' then 'Agent did not speak to Business Owner'
      else 'xxx'
    end
HAVING
    case
      when c.QCReason LIKE '%Agent explained Radius%' then 'Agent explained Radius'
      when c.QCReason LIKE '%Agent did not speak to Business Owner%' then 'Agent did not speak to Business Owner'
      else 'xxx'
    end <> 'xxx'

The having clause makes me nervous. If that fails, you'd need a fancy where clause to filter on c.QCReason. Another issue, if there are no such rows, you won't get a 0, you just won't get a row for that value.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

If you can handle multiple columns, rather than multiple rows, then you can use conditional aggregation:

SELECT SUM(CASE WHEN c.VerifiedDate BETWEEN @LastWeekStart AND @LastWeekEnd AND
                     c.QCReason LIKE '%Agent did not speak to Business Owner%'
                THEN 1 ELSE 0 END) as DidNotSpeaktoBusinessOwner,
       SUM(CASE WHEN c.VerifiedDate BETWEEN @LastWeekStart AND @LastWeekEnd AND
                     c.QCReason LIKE '%Agent explained Radius%'
                THEN 1 ELSE 0 END) as AgentExplainedRadius  
FROM dbo.[Call] c JOIN
     dbo.[User] u
     on c.UserID = u.UserID
WHERE c.IsPassedQC = 0 AND
      (c.QCDate IS NOT NULL AND c.VerifiedDate > '2013-11-04')
      (c.CallCenterID = 0 OR c.CallCenterID = u.CallCenterID)

EDIT:

Here is a way you can get the results on separate rows. Note that this will probably not perform as well as the previous query:

with patterns as (
      select 'Agent did not speak to Business Owner' as name,
             '%Agent did not speak to Business Owner%' as pattern union all
      select 'Agent explained Radius', '%Agent explained Radius%'
     )
SELECT patterns.name,
       SUM(CASE WHEN c.VerifiedDate BETWEEN @LastWeekStart AND @LastWeekEnd AND
                     c.QCReason LIKE patterns.pattern
                THEN 1 ELSE 0 END)
FROM patterns cross join
     dbo.[Call] c JOIN
     dbo.[User] u
     on c.UserID = u.UserID
WHERE c.IsPassedQC = 0 AND
      (c.QCDate IS NOT NULL AND c.VerifiedDate > '2013-11-04')
      (c.CallCenterID = 0 OR c.CallCenterID = u.CallCenterID)
GROUP BY  patterns.name;

Upvotes: 2

Related Questions