How to get running total for each quarter and instead of duplicate value to have 0. Using SQL Server 2012

user_6043544 0
Viewed 911

I need to calculate running total premium for each quarter. But my data is broken down by date.

So I need the first row display value based on year and quarter, but if following rows have same Year and Quarter, then value should be 0.

This is what it looks like now:

enter image description here

But I need it like this:

enter image description here

Query with dummy data:

declare @TempTable1 table (ID int, Date date, PolicyNumber varchar(100), Premium money)

insert into @TempTable1 
values (1, '2018-01-01', 'Policy1', 100),
       (2, '2018-02-08', 'Policy2', 200),
       (3, '2018-04-15', 'Policy3', 300),
       (4, '2018-05-31', 'Policy4', 150),
       (5, '2018-07-10', 'Policy5', 250),
       (6, '2018-11-23', 'Policy6', 350),
       (7, '2018-12-05', 'Policy7', 330),
       (8, '2019-01-09', 'Policy8', 140),
       (9, '2019-06-18', 'Policy9', 225),
       (10, '2019-06-28', 'Policy10', 145)

SELECT  
    ID, 
    Date, 
    PolicyNumber,    
    YEAR(date) AS Year, 
    DATEPART(qq, date) AS Quarter,
    Premium,
    SUM(premium) OVER (ORDER BY YEAR(date), DATEPART(qq, date)) AS RunningTotal
FROM
    @TempTable1

Is it possible to achieve that in same SELECT statement?

Thank you

sql-server-2012 tsql sql-server
2 Answers

try this. It creates a row_number for each year and quarter partition, and only allows a calculated value for row = 1

SELECT ID,
        Date,
        policynumber, 
        dq2.year,
        dq2.Quarter,
        premium,
        case when rn=1 then runningtotal else 0 end 'runningtotal' 
from
    (
        select *,row_number() OVER (partition by year,quarter order by policynumber) as RN FROM
        (
        select  ID, 
                Date, 
                PolicyNumber,    
                Year(date) as 'Year', 
                DATEPART(qq,date) as [Quarter],
                Premium,
                sum(premium) over (order by Year(date),DATEPART(qq,date)) as RunningTotal

        from @TempTable1
        ) DQ
    ) DQ2
user_64090000

All you need to do is add a check to see if the Year/Quarter is changing or not via the lag() window function.

select  ID, 
        Date, 
        PolicyNumber,    
        Year(date) as Year, 
        DATEPART(qq,date) as Quarter,
        Premium,
        case when lag(DATEPART(qq,date), 1, null) over (order by Year(date),DATEPART(qq,date)) - DATEPART(qq,date) = 0 then 0 else
        sum(premium) over (order by Year(date),DATEPART(qq,date)) end as RunningTotal
from @TempTable1

I would suggest wrapping the initial calculated columns in a subquery to cut down on the number of datepart() calcs involved, but either solution will work.

Subquery Option:

select a.ID
, a.Date
, a.PolicyNumber
, a.Year
, a.Quarter
, a.Premium
, case when lag(a.Quarter, 1, null) over (order by a.Year,a.Quarter) - a.Quarter = 0 then 0 else
            sum(a.premium) over (order by a.Year,a.Quarter) end as RunningTotal
from (
    select  ID, 
            Date, 
            PolicyNumber,    
            Year(date) as Year, 
            DATEPART(qq,date) as Quarter,
            Premium
    from @TempTable1
    ) as a
user_9603620
Related