Pivot total for column and row not showing correct value

Question

honestly, for several days, i am trying to learn about pivot table behavior. rightnow, i am able to display sum of row and column in pivot table. Here is the code that i am trying to set

DECLARE @cols            AS NVARCHAR(MAX)
DECLARE @colswithNoNulls AS NVARCHAR(MAX)
DECLARE @query           AS NVARCHAR(MAX)
DECLARE @tanggal_awal       DATE
DECLARE @tanggal_akhir      DATE
DECLARE @print              NVARCHAR(MAX)
DECLARE @querycount      AS NVARCHAR(MAX)

CREATE TABLE #datatable  
(
    product_id int,
    product_date date,
    product_ammount int
)


SET @tanggal_awal   = convert(DATE,'02-01-2017')
SET @tanggal_akhir  = convert(DATE,DATEADD(dd,-1,(DATEADD(mm,1,@tanggal_awal))))

--SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))

INSERT INTO #datatable (product_id,product_date,product_ammount) VALUES 
            (1,GETDATE(),100),
            (1,GETDATE(),900),
            (2,DATEADD(DD,-1,GETDATE()),400),
            (3,DATEADD(DD,4,GETDATE()),300),
            (1,DATEADD(DD,4,GETDATE()),200),
            (2,DATEADD(DD,2,GETDATE()),700),
            (4,DATEADD(DD,-3,GETDATE()),1000),
            (4,DATEADD(MM,1,GETDATE()),200),
            (4,GETDATE(),750)

;WITH CTE (datelist,maxdate) AS 
(
    SELECT CONVERT(INT,(MIN(DATEPART(day,@tanggal_awal)))) datelist, CONVERT(INT,MAX(DATEPART(day,product_date))) maxdate
    FROM #datatable
    UNION ALL
    SELECT CONVERT(INT,(DATEPART(day,datelist))), CONVERT(INT,(DATEPART(day,@tanggal_akhir)))
    FROM cte
    WHERE datelist < maxdate
) SELECT c.datelist 
    INTO #temp
    FROM cte c
    ORDER BY c.datelist
    OPTION (maxrecursion 0)

SELECT @cols =  STUFF((SELECT ',' + QUOTENAME(CONVERT(int, datelist)) 
                FROM #temp
                GROUP BY datelist
                ORDER BY CONVERT(int, datelist)
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
                ,1,1,''
                ) 


SELECT @colswithNoNulls =   STUFF((SELECT ',ISNULL(' + QUOTENAME(CONVERT(int, datelist))  +',''0'') '+ QUOTENAME(CONVERT(int, datelist)) 
                            FROM #temp
                            GROUP BY datelist
                            ORDER BY CONVERT(int, datelist)
                            FOR XML PATH(''), TYPE
                            ).value('.', 'NVARCHAR(MAX)') 
                            ,1,1,'')

SET @query = 
          'SELECT product_id, '+ @colswithNoNulls+', Total FROM 
             (
                select 
                ISNULL((CAST(b.product_id as nvarchar(30))), ''Total'') product_id, 
                coalesce(b.product_ammount,0) as product_ammount, 
                DATEPART(dd,(convert(CHAR(10), product_date, 120))) PivotDate, 
                SUM(product_ammount) over (partition by b.product_id) as Total
                FROM #datatable b
                WHERE product_date between @tanggal_awal and @tanggal_akhir
                GROUP BY product_ammount,product_date,product_id
                WITH ROllup
            ) x
            pivot 
            (
                sum(product_ammount)
                for PivotDate in (' +@cols+ ')
            ) p
            ORDER BY CASE when (product_id = ''Total'') then 1 else 0 end, product_id'          

EXECUTE sp_executesql @query ,N'@tanggal_awal DATE, @tanggal_akhir DATE', @tanggal_awal,@tanggal_akhir

IF(OBJECT_ID('tempdb.dbo.#temp','U') IS NOT NULL)
    BEGIN
        TRUNCATE TABLE #temp
        TRUNCATE TABLE #datatable
        DROP TABLE #temp
        DROP TABLE #datatable
    END
ELSE
    BEGIN
        SELECT '#temp is not created in this script' AS MESSAGE
    END

as you can see , the result is show on the display. However, the total value at the very right bottom is strange since it is like doubled up exact total value like in this picture: How to resolve this issue btw? since it was bit confusing for me. thank you for your help :)

Answer 1

Generally, I am not fully aware for RollUp functionality. From your PIVOT query. I have found some of the empty rows is coming up (basically subtotal rows from "With Rollup" option), so I have modified the Group by statement a little bit to achieve the expected result.

                select 
            ISNULL((CAST(b.product_id as nvarchar(30))), 'Total') product_id, 
            coalesce(b.product_ammount,0) as product_ammount, 
            DATEPART(dd,(convert(CHAR(10), product_date, 120))) PivotDate, 
            SUM(product_ammount) over (partition by b.product_id) as Total
            FROM #datatable b
            WHERE product_date between @tanggal_awal and @tanggal_akhir
            GROUP BY product_ammount,product_date,ROllup(product_id)

Kindly replace this query in PIVOT, then you will get the desired output.

Note: Sorry I am not fully aware of RollUp functionality, so I'm unable to give the right explanation.