Montly total, year to date running total, full running total

Transact-SQL
1

Hi!

I've been using SQL for a few months, but am completely stumped by how to create a running total. From some research I think I need an "over partition by", but I don't really understand how it works & then it's complicated by the fact I need a monthly total, a year to date running total & a full running total results on the same row.

Example format of result-set I'm looking for attached.

Wanted%20resultset
Wanted resultset.PNG1066×327 7.82 KB

I got as far as below for a monthly total, but don't know how to get the Year to Date running total & Full running total on the same row. Should I be using a case when?

SELECT DISTINCT

[JOBNUMBER]
,[TASKNAME]
,MONTH([ENTRYDATE]) AS 'Month'
,YEAR([ENTRYDATE]) as 'Year'
,SUM([COSTPRICEREG]) OVER (PARTITION BY [JOBNUMBER], [[TASKNAME],MONTH([ENTRYDATE]),YEAR([ENTRYDATE]) ORDER BY YEAR([ENTRYDATE]), MONTH([ENTRYDATE])) AS Monthly Total

FROM [RambollDW].[Maconomy].[vw_JOBENTRY]

If there was no cost booked in the month, I don't need the row to appear FYI.

Also, I only want 2018 rows to display, but obviously want pre-2018 data to be taken into consideration in the "full running total" result.

Also I'm trying to create it to run as efficiently as possible as there's going to be a lot of projects & a lot of tasks..

Hope that makes sense, thank you very much for any help you can offer a newbie!

2 

 DECLARE @t table(ID int IDENTITY, i int);
 INSERT @t (i)
 VALUES
      (3)    
    , (81)    
    , (-22)
    , (5)
    , (17)
    , (62)
    , (-4)
    , (7);
SELECT
     t.i
   , Sum(t.i) OVER (ORDER BY t.ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) RuningTotal
FROM @t t;

image

1 Like
3

Hi Jotorre,
Thanks for getting back to me & corrected me on how to do a successful running total!
Could you give any input into how I can then use that new syntax to create a running total which is only for a monthly/year to date figure?
How would I include that element into the query?
Thanks again for any help you can give.

4

If you post sample data in consumable format (create table with inserts), what you've tried, and desired output, I may have time to work on it.