Try to avoid UNION in SQL Server Query

Question

I have a query that gathers different metrics for a call center:

    SELECT      CONCAT (DEPARTMENT_DESC, 'Week', datepart(wk, ROW_DATE)) As Dept_Date,
            datepart(wk, ROW_DATE) as weeknum,
            DEPARTMENT_DESC AS DEPT,
            SUM([CALLS_OFFERED_ACTUALS]) As LCW_Calls_Offered,
            MAX(LCW) AS LCW,
            (SUM(ANSTIME) / SUM(CALLS_ANSWERED_ACTUALS)) AS ASA,
            SUM(HANDLED_TIME) / SUM(CALLS_ANSWERED_ACTUALS) AS AHT,
            SUM(HANDLED_TIME) as handletime,
            SUM(CALLS_OFFERED_FCST) AS Call_Target,
            SUM(CALLS_ANSWERED_ACTUALS) as call_answered,
            CAST(1.000*(SUM(TRANSFERS)+SUM(CONFERENCE)) / SUM(CALLS_ANSWERED_ACTUALS) AS DECIMAL(19,3)) AS Transf_Rate,
            CAST(1.000*(SUM(CALLS_ABD_ACTUALS)) / SUM([CALLS_OFFERED_ACTUALS]) AS DECIMAL(19,3)) AS Abandon_Rate,
            1-CAST(1.000*(SUM(LCW60)) / SUM(INT_CNT) AS DECIMAL(19,3)) AS LCW_INT,
            CAST(1.000*(SUM(ONLINETIME)-SUM(AVAILTIME)-SUM(ALARMTIME))/SUM(ONLINETIME)  AS DECIMAL(19,3)) AS Occupancy,
            CAST(1.000*(SUM(PRODTIME)) / SUM(ATWORKTIME) AS DECIMAL(19,3)) AS Productivity,
            CAST(1.000*(SUM(AVAILTIME)) / SUM(ATWORKTIME) AS DECIMAL(19,3)) AS Availab,
            CAST(1.000*(SUM(OVERTIME)) / SUM(WORKFORCETIME) AS DECIMAL(19,3)) AS Overtime,
            CAST(1.000*(SUM(UNPROD_TIME_UNPLANNED)) / SUM(WORKFORCETIME) AS DECIMAL(19,3)) AS Unplanned_Shrink,
            CAST(1.000*(SUM(UNPROD_TIME_PLANNED)) / SUM(WORKFORCETIME) AS DECIMAL(19,3)) AS Planned_Shrink,
            CAST(1.000*(SUM(CALLS_ANSWERED_INT1)+SUM(CALLS_ANSWERED_INT2)) / SUM(CALLS_OFFERED_ACTUALS) AS DECIMAL(19,3)) AS SL_Within_20,
            CAST(1.000*(SUM(CALLS_ANSWERED_INT1)+SUM(CALLS_ANSWERED_INT2)+SUM(CALLS_ANSWERED_INT3)+SUM(CALLS_ANSWERED_INT4)+SUM(CALLS_ANSWERED_INT5)+SUM(CALLS_ANSWERED_INT6)) / SUM(CALLS_OFFERED_ACTUALS) AS DECIMAL(19,3)) AS Calls_Within_60,
            1-CAST(1.000*(SUM(LCW300)) / SUM(INT_CNT) AS DECIMAL(19,3)) AS LCW_INT1,
            1-CAST(1.000*(SUM(LCW120)) / SUM(INT_CNT) AS DECIMAL(19,3)) AS LCW_INT2
      FROM [GEMDB].[dbo].[v_calls_LCW_Splitday]
  where datepart(wk, ROW_DATE) >= (datepart(wk, GETDATE()) - 6) AND datepart(year, ROW_DATE) = 2017
  GROUP BY datepart(wk, ROW_DATE), DEPARTMENT_DESC --((CALLS_ANSWERED_INT1 + CALLS_ANSWERED_INT2) / [CALLS_OFFERED_ACTUALS]) 
  ORDER BY datepart(wk, ROW_DATE

)

The results are grouped by week number & by Department (Billing, Technical Support, Retention, etc.)

We have decided to create a new Sub-Department called Customer Service, which is comprised of Billing & Technical Support together.

I found a way to do this, by doing a UNION to another query that only selects data for Billing & Technical Support.

This runs fine, however the query went from taking 20 seconds to more than 3 minutes to complete:

/****** Script for SelectTopNRows command from SSMS  ******/


SELECT      CONCAT ('CS', 'Week', datepart(wk, ROW_DATE)) As Dept_Date,
            datepart(wk, ROW_DATE) as weeknum,
            CONCAT('CS','DEPT') AS DEPT,
            SUM([CALLS_OFFERED_ACTUALS]) As LCW_Calls_Offered,
            MAX(LCW) AS LCW,
            (SUM(ANSTIME) / SUM(CALLS_ANSWERED_ACTUALS)) AS ASA,
            SUM(HANDLED_TIME) / SUM(CALLS_ANSWERED_ACTUALS) AS AHT,
            SUM(HANDLED_TIME) as handletime,
            SUM(CALLS_OFFERED_FCST) AS Call_Target,
            SUM(CALLS_ANSWERED_ACTUALS) as call_answered,
            CAST(1.000*(SUM(TRANSFERS)+SUM(CONFERENCE)) / SUM(CALLS_ANSWERED_ACTUALS) AS DECIMAL(19,3)) AS Transf_Rate,
            CAST(1.000*(SUM(CALLS_ABD_ACTUALS)) / SUM([CALLS_OFFERED_ACTUALS]) AS DECIMAL(19,3)) AS Abandon_Rate,
            1-CAST(1.000*(SUM(LCW60)) / SUM(INT_CNT) AS DECIMAL(19,3)) AS LCW_INT,
            CAST(1.000*(SUM(ONLINETIME)-SUM(AVAILTIME)-SUM(ALARMTIME))/SUM(ONLINETIME)  AS DECIMAL(19,3)) AS Occupancy,
            CAST(1.000*(SUM(PRODTIME)) / SUM(ATWORKTIME) AS DECIMAL(19,3)) AS Productivity,
            CAST(1.000*(SUM(AVAILTIME)) / SUM(ATWORKTIME) AS DECIMAL(19,3)) AS Availab,
            CAST(1.000*(SUM(OVERTIME)) / SUM(WORKFORCETIME) AS DECIMAL(19,3)) AS Overtime,
            CAST(1.000*(SUM(UNPROD_TIME_UNPLANNED)) / SUM(WORKFORCETIME) AS DECIMAL(19,3)) AS Unplanned_Shrink,
            CAST(1.000*(SUM(UNPROD_TIME_PLANNED)) / SUM(WORKFORCETIME) AS DECIMAL(19,3)) AS Planned_Shrink,
            CAST(1.000*(SUM(CALLS_ANSWERED_INT1)+SUM(CALLS_ANSWERED_INT2)) / SUM(CALLS_OFFERED_ACTUALS) AS DECIMAL(19,3)) AS SL_Within_20,
            CAST(1.000*(SUM(CALLS_ANSWERED_INT1)+SUM(CALLS_ANSWERED_INT2)+SUM(CALLS_ANSWERED_INT3)+SUM(CALLS_ANSWERED_INT4)+SUM(CALLS_ANSWERED_INT5)+SUM(CALLS_ANSWERED_INT6)) / SUM(CALLS_OFFERED_ACTUALS) AS DECIMAL(19,3)) AS Calls_Within_60,
            1-CAST(1.000*(SUM(LCW300)) / SUM(INT_CNT) AS DECIMAL(19,3)) AS LCW_INT1,
            1-CAST(1.000*(SUM(LCW120)) / SUM(INT_CNT) AS DECIMAL(19,3)) AS LCW_INT2
        INTO #TEMP_CS   
    FROM [GEMDB].[dbo].[v_calls_LCW_Splitday]
  where datepart(wk, ROW_DATE) >= (datepart(wk, GETDATE()) - 6) AND datepart(year, ROW_DATE) = 2017
  AND DEPARTMENT_DESC IN ('BILLING', 'TechOps')
   GROUP BY datepart(wk, ROW_DATE) --((CALLS_ANSWERED_INT1 + CALLS_ANSWERED_INT2) / [CALLS_OFFERED_ACTUALS]) 
  ORDER BY datepart(wk, ROW_DATE)


  SELECT        CONCAT (DEPARTMENT_DESC, 'Week', datepart(wk, ROW_DATE)) As Dept_Date,
            datepart(wk, ROW_DATE) as weeknum,
            DEPARTMENT_DESC AS DEPT,
            SUM([CALLS_OFFERED_ACTUALS]) As LCW_Calls_Offered,
            MAX(LCW) AS LCW,
            (SUM(ANSTIME) / SUM(CALLS_ANSWERED_ACTUALS)) AS ASA,
            SUM(HANDLED_TIME) / SUM(CALLS_ANSWERED_ACTUALS) AS AHT,
            SUM(HANDLED_TIME) as handletime,
            SUM(CALLS_OFFERED_FCST) AS Call_Target,
            SUM(CALLS_ANSWERED_ACTUALS) as call_answered,
            CAST(1.000*(SUM(TRANSFERS)+SUM(CONFERENCE)) / SUM(CALLS_ANSWERED_ACTUALS) AS DECIMAL(19,3)) AS Transf_Rate,
            CAST(1.000*(SUM(CALLS_ABD_ACTUALS)) / SUM([CALLS_OFFERED_ACTUALS]) AS DECIMAL(19,3)) AS Abandon_Rate,
            1-CAST(1.000*(SUM(LCW60)) / SUM(INT_CNT) AS DECIMAL(19,3)) AS LCW_INT,
            CAST(1.000*(SUM(ONLINETIME)-SUM(AVAILTIME)-SUM(ALARMTIME))/SUM(ONLINETIME)  AS DECIMAL(19,3)) AS Occupancy,
            CAST(1.000*(SUM(PRODTIME)) / SUM(ATWORKTIME) AS DECIMAL(19,3)) AS Productivity,
            CAST(1.000*(SUM(AVAILTIME)) / SUM(ATWORKTIME) AS DECIMAL(19,3)) AS Availab,
            CAST(1.000*(SUM(OVERTIME)) / SUM(WORKFORCETIME) AS DECIMAL(19,3)) AS Overtime,
            CAST(1.000*(SUM(UNPROD_TIME_UNPLANNED)) / SUM(WORKFORCETIME) AS DECIMAL(19,3)) AS Unplanned_Shrink,
            CAST(1.000*(SUM(UNPROD_TIME_PLANNED)) / SUM(WORKFORCETIME) AS DECIMAL(19,3)) AS Planned_Shrink,
            CAST(1.000*(SUM(CALLS_ANSWERED_INT1)+SUM(CALLS_ANSWERED_INT2)) / SUM(CALLS_OFFERED_ACTUALS) AS DECIMAL(19,3)) AS SL_Within_20,
            CAST(1.000*(SUM(CALLS_ANSWERED_INT1)+SUM(CALLS_ANSWERED_INT2)+SUM(CALLS_ANSWERED_INT3)+SUM(CALLS_ANSWERED_INT4)+SUM(CALLS_ANSWERED_INT5)+SUM(CALLS_ANSWERED_INT6)) / SUM(CALLS_OFFERED_ACTUALS) AS DECIMAL(19,3)) AS Calls_Within_60,
            1-CAST(1.000*(SUM(LCW300)) / SUM(INT_CNT) AS DECIMAL(19,3)) AS LCW_INT1,
            1-CAST(1.000*(SUM(LCW120)) / SUM(INT_CNT) AS DECIMAL(19,3)) AS LCW_INT2
            INTO #TEMP_ALL
   FROM [GEMDB].[dbo].[v_calls_LCW_Splitday]
  where datepart(wk, ROW_DATE) >= (datepart(wk, GETDATE()) - 6) AND datepart(year, ROW_DATE) = 2017
   GROUP BY datepart(wk, ROW_DATE), DEPARTMENT_DESC --((CALLS_ANSWERED_INT1 + CALLS_ANSWERED_INT2) / [CALLS_OFFERED_ACTUALS]) 
     ORDER BY datepart(wk, ROW_DATE)


  SELECT *
  FROM #TEMP_ALL
  UNION
  SELECT *
  FROM #TEMP_CS

  DROP TABLE #TEMP_ALL
  DROP TABLE #TEMP_CS

Is there a more efficient way to accomplish this? We cannot afford to wait 3 minutes + for this query to complete.

Thanks!

Um... why do you need #temp_all and #temp_CS and just put `union` inbetween? Why not get rid of the temp tables? and do you want a `union` or `union all`? Union does a distinct and takes time due to a sort, a union all doesn't suffer from the distinct and sort. — xQbert, Aug 03 '17 at 17:38
Unless you are trying to filter out duplicates in the two resultsets you're combining, you should use UNION ALL since it is faster than UNION. [Read about that here](https://stackoverflow.com/questions/49925/what-is-the-difference-between-union-and-union-all) — Tom O., Aug 03 '17 at 17:44
It most certainly seems to me that the title is totally misleading. It should read: Introduction of UNION in query caused performance slowdown — Eli, Aug 03 '17 at 17:44
Eli not really since my goal by asking a question is to know if there is way to avoid UNION in this scenario. — Samuel Rayanne, Aug 03 '17 at 17:49
Both queries are hitting the same table... [v_calls_LCW_Splitday] is this a view? querying against a view can be slow... 2nd I don't see what really separates out the data since the where clauses are identical the only variances is a group by on dept... — xQbert, Aug 03 '17 at 17:54
It took a while as I needed to refactor the SQL so I could read it and understand the question better; but I believe using a simple case statement where ever department_Desc is used in your oriignal query would solve the issue... though it does use hardcoding which i try and avoid. `case when DEPARTMENT_DESC in IN ('BILLING', 'TechOps') then 'Customer Service' else DEPARTMENT_DESC end` Now if you want a subtotal by the combination... a `group by grouping sets()` may work... in conjunction with a case. — xQbert, Aug 03 '17 at 18:13
But maybe I still don't understand the question. you want a subtotal by the combination... a `group by grouping sets()` may work... in conjunction with a case. and still keep the individual totals. then I think group by grouping sets may be what you're after. — xQbert, Aug 03 '17 at 18:17

xQbert · Accepted Answer · 2017-08-03T18:13:54.857

Not sure why you need the temp tables at all
Not sure why you need to use union vs union all
To avoid the union, why not use a case statement: case when DEPARTMENT_DESC in IN ('BILLING', 'TechOps') then 'Customer Service' else DEPARTMENT_DESC end .

Keeping the union but using union all and avoiding the temp tables.

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT CONCAT ('CS', 'Week', datepart(wk, ROW_DATE)) As Dept_Date
     , datepart(wk, ROW_DATE) as weeknum
     , CONCAT('CS','DEPT') AS DEPT
     , SUM([CALLS_OFFERED_ACTUALS]) As LCW_Calls_Offered
     , MAX(LCW) AS LCW
     , SUM(ANSTIME) / SUM(CALLS_ANSWERED_ACTUALS) AS ASA
     , SUM(HANDLED_TIME) / SUM(CALLS_ANSWERED_ACTUALS) AS AHT
     , SUM(HANDLED_TIME) as handletime
     , SUM(CALLS_OFFERED_FCST) AS Call_Target
     , SUM(CALLS_ANSWERED_ACTUALS) as call_answered
     , CAST(1.000*(SUM(TRANSFERS)+SUM(CONFERENCE)) / SUM(CALLS_ANSWERED_ACTUALS) AS DECIMAL(19,3)) AS Transf_Rate
     , CAST(1.000*(SUM(CALLS_ABD_ACTUALS)) / SUM([CALLS_OFFERED_ACTUALS]) AS DECIMAL(19,3)) AS Abandon_Rate
     , 1-CAST(1.000*(SUM(LCW60)) / SUM(INT_CNT) AS DECIMAL(19,3)) AS LCW_INT
     , CAST(1.000*(SUM(ONLINETIME)-SUM(AVAILTIME)-
     , SUM(ALARMTIME))/SUM(ONLINETIME)  AS DECIMAL(19,3)) AS Occupancy
     , CAST(1.000*(SUM(PRODTIME)) / SUM(ATWORKTIME) AS DECIMAL(19,3)) AS Productivity
     , CAST(1.000*(SUM(AVAILTIME)) / SUM(ATWORKTIME) AS DECIMAL(19,3)) AS Availab
     , CAST(1.000*(SUM(OVERTIME)) / SUM(WORKFORCETIME) AS DECIMAL(19,3)) AS Overtime
     , CAST(1.000*(SUM(UNPROD_TIME_UNPLANNED)) / SUM(WORKFORCETIME) AS DECIMAL(19,3)) AS Unplanned_Shrink
     , CAST(1.000*(SUM(UNPROD_TIME_PLANNED)) / SUM(WORKFORCETIME) AS DECIMAL(19,3)) AS Planned_Shrink
     , CAST(1.000*(SUM(CALLS_ANSWERED_INT1)+SUM(CALLS_ANSWERED_INT2)) / SUM(CALLS_OFFERED_ACTUALS) AS DECIMAL(19,3)) AS SL_Within_20
     , CAST(1.000*(SUM(CALLS_ANSWERED_INT1)+SUM(CALLS_ANSWERED_INT2)+SUM(CALLS_ANSWERED_INT3)+SUM(CALLS_ANSWERED_INT4)+SUM(CALLS_ANSWERED_INT5)+SUM(CALLS_ANSWERED_INT6)) / SUM(CALLS_OFFERED_ACTUALS) AS DECIMAL(19,3)) AS Calls_Within_60
     , 1-CAST(1.000*(SUM(LCW300)) / SUM(INT_CNT) AS DECIMAL(19,3)) AS LCW_INT1
     , 1-CAST(1.000*(SUM(LCW120)) / SUM(INT_CNT) AS DECIMAL(19,3)) AS LCW_INT2
FROM [GEMDB].[dbo].[v_calls_LCW_Splitday]
WHERE  datepart(wk, ROW_DATE) >= (datepart(wk, GETDATE()) - 6) 
  AND datepart(year, ROW_DATE) = 2017
  AND DEPARTMENT_DESC IN ('BILLING', 'TechOps')
GROUP BY datepart(wk, ROW_DATE) --((CALLS_ANSWERED_INT1 + CALLS_ANSWERED_INT2) / [CALLS_OFFERED_ACTUALS]) 
ORDER BY datepart(wk, ROW_DATE)

UNION ALL

SELECT CONCAT (DEPARTMENT_DESC, 'Week', datepart(wk, ROW_DATE)) As Dept_Date
     , datepart(wk, ROW_DATE) as weeknum
     , DEPARTMENT_DESC AS DEPT
     , SUM([CALLS_OFFERED_ACTUALS]) As LCW_Calls_Offered
     , MAX(LCW) AS LCW
     , SUM(ANSTIME) / SUM(CALLS_ANSWERED_ACTUALS) AS ASA
     , SUM(HANDLED_TIME) / SUM(CALLS_ANSWERED_ACTUALS) AS AHT
     , SUM(HANDLED_TIME) as handletime
     , SUM(CALLS_OFFERED_FCST) AS Call_Target
     , SUM(CALLS_ANSWERED_ACTUALS) as call_answered
     , CAST(1.000*(SUM(TRANSFERS)+SUM(CONFERENCE)) / SUM(CALLS_ANSWERED_ACTUALS) AS DECIMAL(19,3)) AS Transf_Rate
     , CAST(1.000*(SUM(CALLS_ABD_ACTUALS)) / SUM([CALLS_OFFERED_ACTUALS]) AS DECIMAL(19,3)) AS Abandon_Rate
     , 1-CAST(1.000*(SUM(LCW60)) / SUM(INT_CNT) AS DECIMAL(19,3)) AS LCW_INT
     , CAST(1.000*(SUM(ONLINETIME)-SUM(AVAILTIME)-SUM(ALARMTIME))/SUM(ONLINETIME)  AS DECIMAL(19,3)) AS Occupancy
     , CAST(1.000*(SUM(PRODTIME)) / SUM(ATWORKTIME) AS DECIMAL(19,3)) AS Productivity
     , CAST(1.000*(SUM(AVAILTIME)) / SUM(ATWORKTIME) AS DECIMAL(19,3)) AS Availab
     , CAST(1.000*(SUM(OVERTIME)) / SUM(WORKFORCETIME) AS DECIMAL(19,3)) AS Overtime
     , CAST(1.000*(SUM(UNPROD_TIME_UNPLANNED)) / SUM(WORKFORCETIME) AS DECIMAL(19,3)) AS Unplanned_Shrink
     , CAST(1.000*(SUM(UNPROD_TIME_PLANNED)) / SUM(WORKFORCETIME) AS DECIMAL(19,3)) AS Planned_Shrink
     , CAST(1.000*(SUM(CALLS_ANSWERED_INT1)+SUM(CALLS_ANSWERED_INT2)) / SUM(CALLS_OFFERED_ACTUALS) AS DECIMAL(19,3)) AS SL_Within_20
     , CAST(1.000*(SUM(CALLS_ANSWERED_INT1)+SUM(CALLS_ANSWERED_INT2)+SUM(CALLS_ANSWERED_INT3)+SUM(CALLS_ANSWERED_INT4)+SUM(CALLS_ANSWERED_INT5)+SUM(CALLS_ANSWERED_INT6)) / SUM(CALLS_OFFERED_ACTUALS) AS DECIMAL(19,3)) AS Calls_Within_60
     , 1-CAST(1.000*(SUM(LCW300)) / SUM(INT_CNT) AS DECIMAL(19,3)) AS LCW_INT1
     , 1-CAST(1.000*(SUM(LCW120)) / SUM(INT_CNT) AS DECIMAL(19,3)) AS LCW_INT2

FROM [GEMDB].[dbo].[v_calls_LCW_Splitday]
WHERE datepart(wk, ROW_DATE) >= (datepart(wk, GETDATE()) - 6) 
  AND datepart(year, ROW_DATE) = 2017
GROUP BY datepart(wk, ROW_DATE)
       , DEPARTMENT_DESC --((CALLS_ANSWERED_INT1 + CALLS_ANSWERED_INT2) / [CALLS_OFFERED_ACTUALS]) 
ORDER BY datepart(wk, ROW_DATE)

and now w/o the union, you just need a case statement replacing department_Desc with a case

case when DEPARTMENT_DESC in IN ('BILLING', 'TechOps')  
                then 'Customer Service' 
                else DEPARTMENT_DESC end

So I took your 1st query and did just that.

SELECT CONCAT(case when DEPARTMENT_DESC in IN ('BILLING', 'TechOps')  
                   then 'Customer Service' 
                   else DEPARTMENT_DESC end
              , 'Week', datepart(wk, ROW_DATE)) As Dept_Date
     , datepart(wk, ROW_DATE) as weeknum
     , case when DEPARTMENT_DESC in IN ('BILLING', 'TechOps')  
            then 'Customer Service' 
            else DEPARTMENT_DESC end AS DEPT
     , SUM([CALLS_OFFERED_ACTUALS]) As LCW_Calls_Offered
     , MAX(LCW) AS LCW
     , SUM(ANSTIME) / SUM(CALLS_ANSWERED_ACTUALS) AS ASA
     , SUM(HANDLED_TIME) / SUM(CALLS_ANSWERED_ACTUALS) AS AHT
     , SUM(HANDLED_TIME) as handletime
     , SUM(CALLS_OFFERED_FCST) AS Call_Target
     , SUM(CALLS_ANSWERED_ACTUALS) as call_answered
     , CAST(1.000*(SUM(TRANSFERS)+SUM(CONFERENCE)) / SUM(CALLS_ANSWERED_ACTUALS) AS DECIMAL(19,3)) AS Transf_Rate
     , CAST(1.000*(SUM(CALLS_ABD_ACTUALS)) / SUM([CALLS_OFFERED_ACTUALS]) AS DECIMAL(19,3)) AS Abandon_Rate
     , 1-CAST(1.000*(SUM(LCW60)) / SUM(INT_CNT) AS DECIMAL(19,3)) AS LCW_INT
     , CAST(1.000*(SUM(ONLINETIME)-SUM(AVAILTIME)-SUM(ALARMTIME))/SUM(ONLINETIME)  AS DECIMAL(19,3)) AS Occupancy
     , CAST(1.000*(SUM(PRODTIME)) / SUM(ATWORKTIME) AS DECIMAL(19,3)) AS Productivity
     , CAST(1.000*(SUM(AVAILTIME)) / SUM(ATWORKTIME) AS DECIMAL(19,3)) AS Availab
     , CAST(1.000*(SUM(OVERTIME)) / SUM(WORKFORCETIME) AS DECIMAL(19,3)) AS Overtime
     , CAST(1.000*(SUM(UNPROD_TIME_UNPLANNED)) / SUM(WORKFORCETIME) AS DECIMAL(19,3)) AS Unplanned_Shrink
     , CAST(1.000*(SUM(UNPROD_TIME_PLANNED)) / SUM(WORKFORCETIME) AS DECIMAL(19,3)) AS Planned_Shrink
     , CAST(1.000*(SUM(CALLS_ANSWERED_INT1)+SUM(CALLS_ANSWERED_INT2)) / SUM(CALLS_OFFERED_ACTUALS) AS DECIMAL(19,3)) AS SL_Within_20
     , CAST(1.000*(SUM(CALLS_ANSWERED_INT1)+SUM(CALLS_ANSWERED_INT2)+SUM(CALLS_ANSWERED_INT3)+SUM(CALLS_ANSWERED_INT4)+SUM(CALLS_ANSWERED_INT5)+SUM(CALLS_ANSWERED_INT6)) / SUM(CALLS_OFFERED_ACTUALS) AS DECIMAL(19,3)) AS Calls_Within_60
     , 1-CAST(1.000*(SUM(LCW300)) / SUM(INT_CNT) AS DECIMAL(19,3)) AS LCW_INT1
     , 1-CAST(1.000*(SUM(LCW120)) / SUM(INT_CNT) AS DECIMAL(19,3)) AS LCW_INT2
FROM [GEMDB].[dbo].[v_calls_LCW_Splitday]
WHERE datepart(wk, ROW_DATE) >= (datepart(wk, GETDATE()) - 6) 
  AND datepart(year, ROW_DATE) = 2017
GROUP BY datepart(wk, ROW_DATE)
       , case when DEPARTMENT_DESC in IN ('BILLING', 'TechOps')  
              then 'Customer Service' 
              else DEPARTMENT_DESC end  --((CALLS_ANSWERED_INT1 + CALLS_ANSWERED_INT2) / [CALLS_OFFERED_ACTUALS]) 
ORDER BY datepart(wk, ROW_DATE

Thanks xQbert, although this is still slower than required, it went from 3minutes 30secs to around 2 minutes. — Samuel Rayanne, Aug 03 '17 at 18:06
I updated to include a verion w/o the union using a case. I don't like that we hardcoded the department_desc's though. — xQbert, Aug 03 '17 at 18:07

Try to avoid UNION in SQL Server Query

1 Answers1