Search code examples
sql-servert-sqlpivotdynamic-pivot

How to replace NULL with 0's in dynamic pivot and calculate cumulative in SQL 2012

The output of my dynamic pivot looks like this:

enter image description here

I am assuming if I get 0's instead of NULL's then EffectiveMMYY will be grouped and won't have duplicates.

So how can I replace NULL's with 0's?

Without cumulative total output should look like this:

enter image description here

With Cumulative total output should look like this:

enter image description here

link to dbfidle

https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=b2e9d0a6fc58705b8f310babbe3f158e

This is sample query:

IF OBJECT_ID('tempdb..##DynPivot') IS NOT NULL DROP TABLE ##DynPivot
DECLARE @Table1 TABLE (ID int, PolicyNumber varchar(50),EffectiveDate date, LossDate date)
INSERT INTO @Table1
(
    ID,
    PolicyNumber,
    EffectiveDate,
    LossDate
)
VALUES
            ( 1, 'Pol1', '2017-02-01',  '2017-02-05'),  
            ( 2, 'Pol2', '2017-03-04',   '2017-03-18'), 
            ( 3, 'Pol3', '2017-03-11', '2017-03-22'),
            ( 4, 'Pol4', '2017-03-11',  NULL),  
            ( 5, 'Pol5', '2017-03-15', '2017-04-22'),
            ( 6, 'Pol6', '2017-04-18', '2017-04-19'),   
            ( 7, 'Pol7', '2017-04-22', '2017-04-29'),
            ( 8, 'Pol8', '2017-04-15', '2017-04-18'),   
            ( 9, 'Pol9', '2017-12-15', '2018-01-23'),
            ( 10, 'Pol10', '2017-02-13', '2017-03-11'),
            ( 11, 'Pol11', '2017-02-18', '2017-03-22'),
            ( 12, 'Pol12', '2017-02-17', '2018-01-05'),
            ( 13, 'Pol13', '2017-05-28', '2017-05-30')

SELECT  
        FORMAT(EffectiveDate, 'MMM') + '-'+ FORMAT(EffectiveDate, 'yy') AS  'EffectiveMMYY',
        YEAR(EffectiveDate) AS EffYear, MONTH(EffectiveDate) AS EffMonth,
        YEAR(LossDate) AS LossYear, MONTH(LossDate) AS LossMonth,

        FORMAT(LossDate, 'MMM') + '-'+ FORMAT(LossDate, 'yy') AS  'LossMMYY',
        count(PolicyNumber) AS PolicyCout
into ##DynPivot
FROM @Table1 

WHERE LossDate IS NOT NULL
GROUP BY FORMAT(EffectiveDate, 'MMM') + '-'+ FORMAT(EffectiveDate, 'yy'),
         FORMAT(LossDate, 'MMM') + '-'+ FORMAT(LossDate, 'yy'),
         YEAR(EffectiveDate),
         MONTH(EffectiveDate),
         YEAR(LossDate), MONTH(LossDate)
ORDER BY FORMAT(EffectiveDate, 'MMM') + '-'+ FORMAT(EffectiveDate, 'yy')

Declare @SQL varchar(max) = Stuff((Select  ',' + QuoteName(LossMMYY) From ##DynPivot group by LossMMYY Order by min(LossYear), min(LossMonth) For XML Path('')),1,1,'') 

select @SQL = '
select  EffectiveMMYY,' + @SQL + '
from ##DynPivot
pivot ( sum(PolicyCout) for LossMMYY IN (' + @SQL + ') ) p
order by EffYear,EffMonth
'Exec(@SQL);
Solution

  • As I commented, if you use a Cross Tab (which I personally think is far better than the PIVOT operator for several reasons) this issue doesn't exist:

    CREATE TABLE Table1 (ID int, PolicyNumber varchar(50),EffectiveDate date, LossDate date);
INSERT INTO Table1
(
    ID,
    PolicyNumber,
    EffectiveDate,
    LossDate
)
VALUES
            ( 1, 'Pol1', '2017-02-01',  '2017-02-05'),  
            ( 2, 'Pol2', '2017-03-04',   '2017-03-18'), 
            ( 3, 'Pol3', '2017-03-11', '2017-03-22'),
            ( 4, 'Pol4', '2017-03-11',  NULL),  
            ( 5, 'Pol5', '2017-03-15', '2017-04-22'),
            ( 6, 'Pol6', '2017-04-18', '2017-04-19'),   
            ( 7, 'Pol7', '2017-04-22', '2017-04-29'),
            ( 8, 'Pol8', '2017-04-15', '2017-04-18'),   
            ( 9, 'Pol9', '2017-12-15', '2018-01-23'),
            ( 10, 'Pol10', '2017-02-13', '2017-03-11'),
            ( 11, 'Pol11', '2017-02-18', '2017-03-22'),
            ( 12, 'Pol12', '2017-02-17', '2018-01-05'),
            ( 13, 'Pol13', '2017-05-28', '2017-05-30');
DECLARE @SQL nvarchar(MAX);

SET @SQL = N'SELECT ISNULL(STUFF(CONVERT(varchar(9),DATEFROMPARTS(YEAR(EffectiveDate), MONTH(EffectiveDate), 1),6),1,3,''''),''Total'') AS EffectiveMonth,' + NCHAR(13) + NCHAR(10) +
           STUFF((SELECT N',' + NCHAR(13) + NCHAR(10) + 
                         N'       COUNT(CASE WHEN DATEFROMPARTS(YEAR(LossDate), MONTH(LossDate), 1) = ' + QUOTENAME(CONVERT(varchar(8),V.LossDate,112),'''') + N'THEN 1 END) AS ' + QUOTENAME(STUFF(CONVERT(varchar(9),V.LossDate,6),1,3,''))
                  FROM Table1 T1
                       CROSS APPLY(VALUES(DATEFROMPARTS(YEAR(LossDate), MONTH(LossDate), 1)))V(LossDate)
                  GROUP BY V.LossDate
                  ORDER BY V.LossDate
                  FOR XML PATH(N''),TYPE).value('.','nvarchar(MAX)'),1,3,N'') + N',' + NCHAR(13) + NCHAR(10) +                  
           N'       COUNT(*) AS Total' + NCHAR(13) + NCHAR(10) +
           N'FROM Table1'  + NCHAR(13) + NCHAR(10) +
           N'GROUP BY DATEFROMPARTS(YEAR(EffectiveDate), MONTH(EffectiveDate), 1) WITH ROLLUP;';

PRINT @SQL;

EXEC sp_executesql @SQL;
GO
DROP TABLE Table1;

    DB<>fiddle