MySQL Data Flow - Running Total - Specific Period?
I am currently working on a running total, which worked out fine. The only thing I can´t figure out is to calculate a running total per specific period.
My table should look like that:
As you can see our season starts on the first of May and ends on the last of April in the following year.
My current code for YTD
1) (table: ytd_figures)
SELECT a1.created_at_trans, a1.platform,(SELECT SUM(a2.total)
FROM proc_group a2
WHERE a2.platform = a1.platform AND a2.created_at_trans <= a1.created_at_trans)
FROM proc_group a1
ORDER BY a1.created_at_trans, a1.platform;
2) (table: proc_group_ytd)
SELECT n.*, n1.YTD_total
From proc_group n LEFT JOIN ytd_figures n1 ON n.created_at_trans = n1.created_at_trans
Could someone please show me where I need to include a statement for determining the time period?
Addional question: Is there any chance to include a window function, because they seem to be more efficient.