CODEWITHSUNDEEP
sqlsql-servert-sqlazure-sql-database
Andrew Richards
Andrew Richards

Reputation: 325

Year to date running total column based on values within a row

I have a query in a database using SQL Azure, which returns a set of sales figures, month by month, for a set of offices and staff within those offices, as in the image below: enter image description here The query which returns this is called vwOfficeAndNegSalesTotals and looks like this:

SELECT OfficeID, 
    DATENAME(Month,PipelineDate) + ' ' + DATENAME(Year,PipelineDate) as [PipelineMonth],
    OfficeName, 
    Negotiator, 
    Status,
    SUM(Fee) as FeeValue
FROM vwBankingProjections
GROUP BY OfficeID,
    DATENAME(Month,PipelineDate) + ' ' + DATENAME(Year,PipelineDate),
    OfficeName, 
    Negotiator, 
    Status, 
    DATEPART(Month,PipelineDate),
    DATEPART(YEAR,PipelineDate)

I need to add another column, which provides a running total of FeeValue for the year to date for the Negotiator in that row, with the year "to date" meaning up to and including the month of that row. Additionally, it should only total the FeeValue where the status is Payment received.

So, it would return something like this:

Figures including YTD values

As you can tell from the structure of vwOfficeAndNegSalesTotals, the query upon which it is based (vwBankingProjections) does include actual dates, rather than just the text Month + Year - these dates are in the PipelineDate column.

My first attempt got me a total for the year which viewing a row for May 2016 (at time of writing) would be the year to date, but it needs to only show the total for the year to the date mentioned in that row (so if viewing a row for April 2016, would be the total from Jan - April 2016). In case it's of any help, here's what I came up with for that:

SELECT OfficeID, 
    DATENAME(Year,PipelineDate) as [PipelineYear],
    OfficeName, 
    Negotiator, 
    SUM(Fee) as YTDFee
FROM vwBankingProjections
WHERE Status IN ('Payment Received')
GROUP BY OfficeID, 
    DATENAME(Year,PipelineDate),
    OfficeName, 
    Negotiator

If anyone can help, I'd be grateful, as this is a bit beyond my skill-set.

Thanks a lot

Andrew

Upvotes: 2

Views: 1027

Answers (2)

RichardCL
RichardCL

Reputation: 1482

You can use SUM() OVER().

Create and populate table.

CREATE TABLE dbo.Sales
(
    OfficeID int NOT NULL,
    PipelineMonth date NOT NULL
        CHECK (DAY(PipelineMonth) = 1),
    OfficeName nvarchar(25) NOT NULL,
    Negotiator nvarchar(25) NOT NULL,
    [Status] nvarchar(25) NOT NULL,
    FeeValue int NOT NULL,
    FeeValueReceived AS IIF([Status] = N'Payment received', FeeValue, 0)
);
GO

INSERT INTO dbo.Sales (OfficeID, PipelineMonth, OfficeName, Negotiator, [Status], FeeValue)
    VALUES
    (1, '2016-01-01', N'London', N'Fred', N'Payment received', 5000),
    (1, '2016-01-01', N'London', N'Fred', N'Completed', 4800),
    (1, '2016-01-01', N'London', N'Kate', N'Payment received', 5980),
    (1, '2016-01-01', N'London', N'Kate', N'Completed', 7000),
    (1, '2016-01-01', N'London', N'Bob', N'Payment received', 9250),
    (2, '2016-01-01', N'Birmingham', N'Jo', N'Payment received', 7870),
    (2, '2016-01-01', N'Birmingham', N'Kathryn', N'Payment received', 3690),
    (2, '2016-01-01', N'Birmingham', N'Kathryn', N'Completed', 8545),
    (1, '2016-02-01', N'London', N'Fred', N'Payment received', 6500),
    (1, '2016-02-01', N'London', N'George', N'Completed', 2575),
    (1, '2016-02-01', N'London', N'George', N'Payment received', 7500),
    (1, '2016-02-01', N'London', N'Kate', N'Payment received', 8393),
    (1, '2016-02-01', N'London', N'Bob', N'Payment received', 6125);

Then make a SELECT statement with SUM() OVER().

SELECT OfficeID, PipelineMonth, OfficeName, Negotiator, [Status], FeeValue,
        SUM(FeeValueReceived) OVER (PARTITION BY OfficeID, Negotiator, YEAR(PipelineMonth) ORDER BY PipelineMonth, [Status] DESC, FeeValue DESC) AS 'YTD'
    FROM dbo.Sales
    ORDER BY PipelineMonth, OfficeID, Negotiator, [Status] DESC, FeeValue DESC

enter image description here

See Books Online > OVER Clause (Transact-SQL): https://msdn.microsoft.com/en-us/library/ms189461.aspx

Upvotes: 1

Andy
Andy

Reputation: 56

RichardCL has a solid approach. If you need a true RunningTotal, see the following examples in https://devjef.wordpress.com/2012/10/20/calculating-running-totals/

Upvotes: 0

Related Questions