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.
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?
,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
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!