Monthly running total, year to date running total, full running total on same row

Question

Post reply

Monthly running total, year to date running total, full running total on same row

sophie.harper

SSC Journeyman

Points: 82
More actions
June 18, 2018 at 4:12 pm

#369988

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 resultset.PNG1066x327 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!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Joe Torre SSChampion Points: 10248 More actions · Answer 1

Please post consumable data, create table XXX(col int, …); insert XXX values(…)

sgmunson SSC Guru Points: 110639 More actions · Answer 2

Understand that this is entirely a guess:
SELECT DISTINCT JOBNUMBER, TASKNAME, YEAR(ENTRYDATE) AS [Year], MONTH(ENTRYDATE) AS [Month], SUM(COSTPRICEREG) OVER (PARTITION BY JOBNUMBER, TASKNAME, YEAR(ENTRYDATE), MONTH(ENTRYDATE) ORDER BY YEAR(ENTRYDATE), MONTH(ENTRYDATE)) AS [Monthly Total], SUM(CASE WHEN YEAR(ENTRYDATE) = YEAR(GETDATE()) THEN COSTPRICEREG ELSE 0. END) OVER (PARTITION BY YEAR(ENTRYDATE) ORDER BY MONTH(ENTRYDATE)) AS [Yearly Total], SUM(COSTPRICEREG) OVER (ORDER BY YEAR(ENTRYDATE), MONTH(ENTRYDATE)) AS [Full Running Total] FROM RambollDW.Maconomy.vw_JOBENTRY;

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

sophie.harper SSC Journeyman Points: 82 More actions · Answer 3

SSC Guru I could absolutely kiss you! Thank you so much. I added into the partition by for the Year to date & full running total fields the JobNumber & Taskname fields (my original request didn't make it clear, sorry, that I needed them in those fields as well) & it's worked perfectly.

Joe Torre - thanks for replying as well! I know you also replied on another board with a request for an example dataset as well, I came online to post it, but SSC Guru got there first.

sophie.harper SSC Journeyman Points: 82 More actions · Answer 4

No wait! Haha, sorry to be a pain. I've just realised that there's currently rows appearing for 2017 as well, when the only resultset I really want is 2018.
If I add in a;

Where YEAR([ENTRYDATE]) = '2018'

Then the full running total doesn't take into considerations entries from the previous years in it's over calculation, so the running total = year to date total.

Is there a way I can just get the rows for 2018 to appear? The only way I can think of currently is to INTO all the results into a temporary table, then query that temporary table so only 2018 rows appear for the end user. However that seems inefficient.... is there a better way I'm missing?

Thank you!!

sgmunson SSC Guru Points: 110639 More actions · Answer 5

sophie.harper - Tuesday, June 19, 2018 12:32 PM
No wait! Haha, sorry to be a pain. I've just realised that there's currently rows appearing for 2017 as well, when the only resultset I really want is 2018.
If I add in a;
Where YEAR([ENTRYDATE]) = '2018'
Then the full running total doesn't take into considerations entries from the previous years in it's over calculation, so the running total = year to date total.
Is there a way I can just get the rows for 2018 to appear? The only way I can think of currently is to INTO all the results into a temporary table, then query that temporary table so only 2018 rows appear for the end user. However that seems inefficient.... is there a better way I'm missing?
Thank you!!

I'd say make that query a CTE, and then SELECT just the 2018 rows from it.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

sgmunson SSC Guru Points: 110639 More actions · Answer 6

sophie.harper - Tuesday, June 19, 2018 12:19 PM
SSC Guru I could absolutely kiss you! Thank you so much. I added into the partition by for the Year to date & full running total fields the JobNumber & Taskname fields (my original request didn't make it clear, sorry, that I needed them in those fields as well) & it's worked perfectly.
Joe Torre - thanks for replying as well! I know you also replied on another board with a request for an example dataset as well, I came online to post it, but SSC Guru got there first.

Thanks! and just an fyi, but SSC Guru is just a title of sorts that the forum provides for all users that meet certain criteria in points and visits, while my forum handle is sgminson and I'm Steve ... Especially to gals that want to kiss me ! <LOL>

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)