Running total: ETL or MySql?

I want to create a running total that resets every year. I've seen posts for doing it with ETL and a window function and others for MySQL and I am lost on which way would be best.

The data table is similar to this :
Month Revenue Reimbursables
1/1/2018 100 5
2/1/2018 97 11
3/1/2018 102 8 etc.

In ETL, I calculate the Net Revenue (for the running total) and extract the year from the date. When it comes to the window function, what is the preceding and following? Where do I enter to reset when year changes? Thanks

Find more posts tagged with

Accepted answers

Godiepi

Hi , based on the 3 fields you provided , the following code will get you the same 3 fields plus 2 more fields for Revenue running total and Reimbursables running total. both reset every year and start the sum in running total. here it is the MySql code that I normally use

Select 
`Month`
,`Revenue`
,`Reimbursables`
,(Select sum(`Revenue`)
  		from `yourtable` as b 
  			where year(a.`Month`) = year(b.`Month`)
  			and a.`Month` >= b.`Month` ) as 'Running_Total_Revenue'
,(Select sum(`Reimbursables`)
  		from `yourtable` as b 
  			where year(a.`Month`) = year(b.`Month`)
  			and a.`Month` >= b.`Month` ) as  'Running_Total_Reimbursables'         

From `yourtable` as a

All comments

Godiepi

Select 
`Month`
,`Revenue`
,`Reimbursables`
,(Select sum(`Revenue`)
  		from `yourtable` as b 
  			where year(a.`Month`) = year(b.`Month`)
  			and a.`Month` >= b.`Month` ) as 'Running_Total_Revenue'
,(Select sum(`Reimbursables`)
  		from `yourtable` as b 
  			where year(a.`Month`) = year(b.`Month`)
  			and a.`Month` >= b.`Month` ) as  'Running_Total_Reimbursables'         

From `yourtable` as a

DHB_1

That's wonderful! Thank you.

Godiepi

Great, Let me know if you run into any issue. as far as what method is better between ETL and MySQL, it all comes down to which one you feel more comfortable with... Personally , I prefer MySQL while other team mates like ETL more.

DHB_1

Thanks again.