How can I sum all pivot generated columns (weekends) values in the end of report

Question

I have generated the following SQL Server pivot, and it gives me desire result. I want to add total of the all Technician jobs in a weekend columns at end of pivot (subtotal of weekend's jobs), where I'm finding it difficult.

SELECT *
FROM
(
    SELECT 
        ISNULL(FORMAT(( DATEADD(wk, DATEDIFF(wk, 0, DATEADD(dd,-1,J.JobDate)), 1) +5), 'dd/MM/yyyy'), 'Completed to Date') as Weekend
        ,U.Name + ' ' + U.Surname AS Technician
        ,(CASE WHEN U.Active  = 1 THEN 'Active' ELSE 'Inactive' END ) AS Active
        ,COUNT(CASE WHEN SFS.FormId IN (66,69,68,79,94,72) THEN 1 ELSE NULL END) AS Total

    FROM  Jobs J 
    LEFT Join dbo.USERS_LIST_ALL() U ON 
        U.id = J.UserId
    LEFT JOIN SimplyFormsSubmitted SFS ON 
        SFS.FormsSubmittedStatus = 'SUBMITTED' 
        AND SFS.JobId = J.id 
        AND SFS.DateSubmitted IS NOT NULL
    WHERE
        U.GroupId = 2 -- User Technician
        AND J.DepartmentId = 132 -- Department
        AND SFS.FormId IN (66,69,68,79,94,72) 
    GROUP BY 
        U.Active
        ,U.Name + ' ' + U.Surname
        ,ROLLUP(FORMAT(( DATEADD(wk, DATEDIFF(wk, 0, DATEADD(dd,-1,J.JobDate)), 1) +5), 'dd/MM/yyyy'))
    ) AS RefTable
pivot ( SUM(Total) for Weekend in (
[02/10/2016],
[09/10/2016],
[16/10/2016],
[23/10/2016],
[30/10/2016],
[06/11/2016],
[13/11/2016],
[20/11/2016],
[27/11/2016],
[04/12/2016],
[11/12/2016],
[18/12/2016],
[25/12/2016],
[01/01/2017],
[08/01/2017],
[15/01/2017],
[22/01/2017],
[29/01/2017],
[05/02/2017],
[12/02/2017],
[19/02/2017],
[26/02/2017],
[05/03/2017],
[12/03/2017],
[19/03/2017],
[Completed to Date]
)) as Answer 
ORDER BY Answer.Active ASC

Here is generated result of the above query How can I sum all the value of the weekend in the end of the table.

Dzmitry Paliakou · Accepted Answer · 2017-03-14T14:37:55.500

I believe you need something similar to

WITH your_query AS
(
    SELECT *
    FROM
    (
        SELECT 
            ISNULL(FORMAT(( DATEADD(wk, DATEDIFF(wk, 0, DATEADD(dd,-1,J.JobDate)), 1) +5), 'dd/MM/yyyy'), 'Completed to Date') as Weekend
            ,U.Name + ' ' + U.Surname AS Technician
            ,(CASE WHEN U.Active  = 1 THEN 'Active' ELSE 'Inactive' END ) AS Active
            ,COUNT(CASE WHEN SFS.FormId IN (66,69,68,79,94,72) THEN 1 ELSE NULL END) AS Total

        FROM  Jobs J 
        LEFT Join dbo.USERS_LIST_ALL() U ON 
            U.id = J.UserId
        LEFT JOIN SimplyFormsSubmitted SFS ON 
            SFS.FormsSubmittedStatus = 'SUBMITTED' 
            AND SFS.JobId = J.id 
            AND SFS.DateSubmitted IS NOT NULL
        WHERE
            U.GroupId = 2 -- User Technician
            AND J.DepartmentId = 132 -- Department
            AND SFS.FormId IN (66,69,68,79,94,72) 
        GROUP BY 
            U.Active
            ,U.Name + ' ' + U.Surname
            ,ROLLUP(FORMAT(( DATEADD(wk, DATEDIFF(wk, 0, DATEADD(dd,-1,J.JobDate)), 1) +5), 'dd/MM/yyyy'))
        ) AS RefTable
    pivot ( SUM(Total) for Weekend in (
    [02/10/2016],
    [09/10/2016],
    [16/10/2016],
    [23/10/2016],
    [30/10/2016],
    [06/11/2016],
    [13/11/2016],
    [20/11/2016],
    [27/11/2016],
    [04/12/2016],
    [11/12/2016],
    [18/12/2016],
    [25/12/2016],
    [01/01/2017],
    [08/01/2017],
    [15/01/2017],
    [22/01/2017],
    [29/01/2017],
    [05/02/2017],
    [12/02/2017],
    [19/02/2017],
    [26/02/2017],
    [05/03/2017],
    [12/03/2017],
    [19/03/2017],
    [Completed to Date]
    )) as Answer 
)
SELECT * FROM your_query
UNION ALL
SELECT 
    'TOTAL', 
    null,
    sum(isnull([02/10/2016],0)),
    sum(isnull([09/10/2016],0)),
    sum(isnull([16/10/2016],0)),
    sum(isnull([23/10/2016],0)),
    sum(isnull([30/10/2016],0)),
    sum(isnull([06/11/2016],0)),
    sum(isnull([13/11/2016],0)),
    sum(isnull([20/11/2016],0)),
    sum(isnull([27/11/2016],0)),
    sum(isnull([04/12/2016],0)),
    sum(isnull([11/12/2016],0)),
    sum(isnull([18/12/2016],0)),
    sum(isnull([25/12/2016],0)),
    sum(isnull([01/01/2017],0)),
    sum(isnull([08/01/2017],0)),
    sum(isnull([15/01/2017],0)),
    sum(isnull([22/01/2017],0)),
    sum(isnull([29/01/2017],0)),
    sum(isnull([05/02/2017],0)),
    sum(isnull([12/02/2017],0)),
    sum(isnull([19/02/2017],0)),
    sum(isnull([26/02/2017],0)),
    sum(isnull([05/03/2017],0)),
    sum(isnull([12/03/2017],0)),
    sum(isnull([19/03/2017],0)),
    sum(isnull([Completed to Date],0))
FROM your_query

UPD for comment #2

I'm not sure if it'll work out of the box, but you could modify the code for your needs.

WITH step1 AS
(
        SELECT 
            DATEADD(wk, DATEDIFF(wk, 0, DATEADD(dd,-1,J.JobDate)), 1)+5 as WeekendDate
            ,U.Name + ' ' + U.Surname AS Technician
            ,(CASE WHEN U.Active  = 1 THEN 'Active' ELSE 'Inactive' END ) AS Active
            ,COUNT(CASE WHEN SFS.FormId IN (66,69,68,79,94,72) THEN 1 ELSE NULL END) AS Total

        FROM  Jobs J 
        LEFT Join dbo.USERS_LIST_ALL() U ON 
            U.id = J.UserId
        LEFT JOIN SimplyFormsSubmitted SFS ON 
            SFS.FormsSubmittedStatus = 'SUBMITTED' 
            AND SFS.JobId = J.id 
            AND SFS.DateSubmitted IS NOT NULL
        WHERE
            U.GroupId = 2 -- User Technician
            AND J.DepartmentId = 132 -- Department
            AND SFS.FormId IN (66,69,68,79,94,72) 
        GROUP BY 
            U.Active
            ,U.Name + ' ' + U.Surname
            ,ROLLUP(DATEADD(wk, DATEDIFF(wk, 0, DATEADD(dd,-1,J.JobDate)), 1)+5)
), 
RefTable AS 
(
    SELECT 
    ISNULL(FORMAT(WeekendDate, 'dd/MM/yyyy'), 'Completed to Date') as Weekend
    ,Technician
    ,Active
    ,avg(Total) over (
        partition by Technician 
        order by WeekendDate desc 
        rows between current row and 3 following) as Avg4
    ,Total
    FROM step1
), 
your_query AS
(
    SELECT *
    FROM RefTable
    pivot ( SUM(Total) for Weekend in (
    [02/10/2016],
    [09/10/2016],
    [16/10/2016],
    [23/10/2016],
    [30/10/2016],
    [06/11/2016],
    [13/11/2016],
    [20/11/2016],
    [27/11/2016],
    [04/12/2016],
    [11/12/2016],
    [18/12/2016],
    [25/12/2016],
    [01/01/2017],
    [08/01/2017],
    [15/01/2017],
    [22/01/2017],
    [29/01/2017],
    [05/02/2017],
    [12/02/2017],
    [19/02/2017],
    [26/02/2017],
    [05/03/2017],
    [12/03/2017],
    [19/03/2017],
    [Completed to Date]
    )) as Answer 
)
SELECT * FROM your_query
UNION ALL
SELECT 
    'TOTAL', 
    null,
    null,
    sum(isnull([02/10/2016],0)),
    sum(isnull([09/10/2016],0)),
    sum(isnull([16/10/2016],0)),
    sum(isnull([23/10/2016],0)),
    sum(isnull([30/10/2016],0)),
    sum(isnull([06/11/2016],0)),
    sum(isnull([13/11/2016],0)),
    sum(isnull([20/11/2016],0)),
    sum(isnull([27/11/2016],0)),
    sum(isnull([04/12/2016],0)),
    sum(isnull([11/12/2016],0)),
    sum(isnull([18/12/2016],0)),
    sum(isnull([25/12/2016],0)),
    sum(isnull([01/01/2017],0)),
    sum(isnull([08/01/2017],0)),
    sum(isnull([15/01/2017],0)),
    sum(isnull([22/01/2017],0)),
    sum(isnull([29/01/2017],0)),
    sum(isnull([05/02/2017],0)),
    sum(isnull([12/02/2017],0)),
    sum(isnull([19/02/2017],0)),
    sum(isnull([26/02/2017],0)),
    sum(isnull([05/03/2017],0)),
    sum(isnull([12/03/2017],0)),
    sum(isnull([19/03/2017],0)),
    sum(isnull([Completed to Date],0))
FROM your_query

I also want last 4 weekends average completed jobs by technician before [Completed to Date] column in the above query. — Ahmed Bakhsh Memon, Mar 14 '17 at 13:49

How can I sum all pivot generated columns (weekends) values in the end of report

1 Answers1