Group total rows into a single row in dynamic pivot query

Question

I have a working dynamic pivot query but am having a bit of trouble getting my Total row to display as a single row. Instead, it is giving me a Total row for each technician and a total row for my total column and invoiced amount column The invoiced column is also showing an incorrect total whereas the total column is correct.

Here is a sample of the result im currently getting. I've left off the Chargeable Utilzation as that is all correct by the looks of it.

callID	StartDate	custMrName	Tech1	Tech2	Tech3	Tech4	Tech5	Total	Invoiced	Chargeable Utilization
		Total		1.6
		Total
		Total	2.2
		Total			1.46
		Total				4.55
		Total					2.77
		Total						12.58	28
123	1.12.23	Cust1	0.65		0.34			0.99	1
234	1.12.23	Cust2		1.10				1.10	2
456	1.12.23	Cust3			1.12			1.12	2
567	1.12.23	Cust4	0.67					0.67	1
678	1.12.23	Cust5		0.50			1.64	2.14	2
789	1.12.23	Cust6				4.55		4.55	5
890	1.12.23	Cust7	0.88				1.13	2.01	2

And what Im trying to get is this.

callID	StartDate	custMrName	Tech1	Tech2	Tech3	Tech4	Tech5	Total	Invoiced	Chargeable Utilization
123	1.12.23	Cust1	0.65		0.34			0.99	1
234	1.12.23	Cust2		1.10				1.10	2
456	1.12.23	Cust3			1.12			1.12	2
567	1.12.23	Cust4	0.67					0.67	1
678	1.12.23	Cust5		0.50			1.64	2.14	2
789	1.12.23	Cust6				4.55		4.55	5
890	1.12.23	Cust7	0.88				1.13	2.01	2
		Total	2.2	1.6	1.46	4.55	2.77	12.58	15

Here is the code for the query, it is quite long so ive removed a couple sections related to calculating the technician times and invoiced amount.

DECLARE @FRDATE DATE = /* SELECT FROM OINV T0 WHERE T0.DocDate >= */  [%0];
DECLARE @TODATE DATE = /* SELECT FROM OINV T0 WHERE T0.DocDate <= */  [%1];
DECLARE @columns NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);

SELECT @columns = STUFF((SELECT DISTINCT ',' + QUOTENAME(CONCAT(firstName, ' ', lastName)) [Technician]
                         FROM SCL6
                                  INNER JOIN OHEM ON SCL6.Technician = OHEM.empID
                                  INNER JOIN HEM6 ON HEM6.[empID] = OHEM.[empID]
                                  INNER JOIN HTM1 ON HTM1.[empID] = OHEM.[empID]
                         WHERE HEM6.roleID = -2 AND HTM1.teamID IN (1, 2) AND OHEM.Active = 'Y'
                         FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
                         , 1, 1, '');

DECLARE @techTime NVARCHAR(MAX);
SET @techTime = ' /* section of code for calculating tech time';

DECLARE @invoiceQuantity NVARCHAR(MAX);
SET @invoiceQuantity = ' /* section of code for calculating amount invoiced ';

SELECT @sql = '
;WITH CTE AS (
    SELECT T0.callID,
           T4.StartDate,
           T0.custMrName,
           CONCAT(OHEM.firstName, '' '', OHEM.lastName) AS Tech,
           ' + @techTime + ' AS Dur,
           B.Invoiced
    FROM OSCL T0
             INNER JOIN SCL6 T4 ON T0.callID = T4.SrcvCallID
             INNER JOIN OHEM ON T4.Technician = OHEM.empID
             INNER JOIN HEM6 ON HEM6.[empID] = OHEM.[empID]
             INNER JOIN HTM1 ON HTM1.[empID] = OHEM.[empID]
             ' + @invoiceQuantity + '
    WHERE T4.[Close] = ''Y'' AND
          T4.Technician <> 104 AND
          HEM6.roleID = -2 AND
          HTM1.teamID IN (1, 2) AND
          T0.customer <> ''C003435'' AND
          T4.StartDate BETWEEN @FRDATE AND @TODATE
    GROUP BY T0.callID, T4.StartDate, OHEM.firstName, OHEM.lastName, T4.Technician, T4.StartTime, T4.EndDate, T4.ChkInTime, T4.ChkOutTime, T4.EndTime, T4.ChkInDate, T4.ChkOutDate, B.Invoiced, T0.custMrName
    UNION ALL
    SELECT T0.callID,
           T4.StartDate,
           T0.custMrName,
           ''Total'' AS ''Total'',
           ' + @techTime + ' AS Dur,
           B.Invoiced
    FROM OSCL T0
             INNER JOIN SCL6 T4 ON T0.callID = T4.SrcvCallID
             INNER JOIN OHEM ON T4.Technician = OHEM.empID
             INNER JOIN HEM6 ON HEM6.[empID] = OHEM.[empID]
             INNER JOIN HTM1 ON HTM1.[empID] = OHEM.[empID]
             ' + @invoiceQuantity + '
          WHERE T4.[Close] = ''Y'' AND
          T4.Technician <> 104 AND
          HEM6.roleID = -2 AND
          HTM1.teamID IN (1, 2) AND
          T0.customer <> ''C003435'' AND
          T4.StartDate BETWEEN @FRDATE AND @TODATE
    GROUP BY T0.callID, T4.StartDate, T4.Technician, OHEM.firstName, OHEM.lastName, T4.StartTime, T4.EndDate, T4.ChkInTime, T4.ChkOutTime, T4.EndTime, T4.ChkInDate, T4.ChkOutDate, B.Invoiced, T0.custMrName
)

SELECT p.callID,
       p.StartDate,
       p.custMrName,
       ' + @columns + ',
       Total,
       Invoiced,
CASE WHEN ISNULL(Invoiced, 0) = 0 THEN NULL ELSE CAST(CAST(ROUND(Invoiced / NULLIF(Total, 0) * 100,0) AS INT) AS VARCHAR) + ''%'' END AS [Chargeable Utilization]
FROM (
SELECT callID, StartDate, custMrName, Tech, Dur, Invoiced FROM CTE
UNION ALL
SELECT 
        NULL AS callID, 
        NULL AS StartDate, 
        ''Total'' AS custMrName, 
        Tech,
        SUM(Dur) AS Dur, 
        SUM(Invoiced) AS Invoiced 
    FROM CTE
    GROUP BY Tech
) AS t
         PIVOT (SUM(Dur) FOR Tech IN (' + @columns + ',Total)) p
ORDER BY p.StartDate ASC';

EXEC sp_executesql @sql, N'@FRDATE DATE, @TODATE DATE', @FRDATE, @TODATE

Answer 1

I move here from the comments. Test something like this

with CTE as (...)

,CTE_a as (SELECT p.callID,
       p.StartDate,
       p.custMrName,
       ' + @columns + ',
       Total

FROM (
SELECT callID, StartDate, custMrName, Tech, Dur FROM CTE
UNION ALL
SELECT 
        NULL AS callID, 
        NULL AS StartDate, 
        ''Total'' AS custMrName, 
        Tech,
        SUM(Dur) AS Dur--, 
        --SUM(Invoiced) AS Invoiced 
    FROM CTE
    GROUP BY Tech
) AS t
         PIVOT (SUM(Dur) FOR Tech IN (' + @columns + ',Total)) p
ORDER BY p.StartDate ASC)

,CTE_b as (SELECT callID, StartDate, custMrName, sum(Invoiced) as Invoiced FROM CTE group by callID, StartDate, custMrName
UNION ALL
SELECT 
        NULL AS callID, 
        NULL AS StartDate, 
        ''Total'' AS custMrName, 
        SUM(Invoiced) AS Invoiced 
    FROM CTE
    )

select a.*,b.invoiced
,CASE WHEN ISNULL(b.Invoiced, 0) = 0 THEN NULL ELSE CAST(CAST(ROUND(b.Invoiced / NULLIF(a.Total, 0) * 100,0) AS INT) AS VARCHAR) + ''%'' END AS [Chargeable Utilization]
    from CTE_a a
    inner join CTE_b b
    on isnull(a.callID,0)=isnull(b.callID,0)
    and isnull(a.StartDate,'99991231') =isnull(b.StartDate,'99991231')
    and a.custMrName=b.custMrName

in the final part pay attention when using CallID and StartDate in the join. In the "total" records you have some nulls to manage: if CallId is a positive integer I think that isnull(..,0) is a good solution; about StartDate I suggest '99991231', but the important thing is to choose a "fake" value, so outside of the actual range of values you can observe on StartDate. Otherwise I think that without these isnulls the join on total records is not going to work.