Situation: I need a custom interval between dates. The problem I face when I try to GROUP BY the year and the result I get amounts to by the given year. I need a custom interval per year from December 20th with time: 00:00:00 of previous year to December 19th with time: 23:59:59 of said year. Here is some of my data:
Table - History:
id | date | income | spent
--------------------------------------------
1 | 2019-12-21 17:15:00 | 600,00 | NULL
2 | 2019-12-23 12:55:00 | 183,00 | NULL
3 | 2019-12-30 20:05:00 | NULL | 25,00
4 | 2020-01-01 15:35:00 | NULL | 13,00
5 | 2020-01-01 20:25:00 | NULL | 500,50
6 | 2020-12-10 10:25:00 | NULL | 5,50
7 | 2021-05-22 12:45:00 | 1098,00 | NULL
8 | 2021-05-23 10:18:00 | NULL | 186,00
9 | 2021-11-25 12:32:00 | NULL | 10,00
10 | 2021-12-23 10:35:00 | NULL | 10,00
The expected result:
Year | Summary Income | Summary Spent | Difference
--------------------------------------------------
2020 | 783,00 | 544,00 | 239,50
2021 | 1098,00 | 196,00 | 902,00
2022 | 0,00 | 10,00 | -10,00
I have managed to get a result with the help of a loop within a procedure:
...
SET @Aa = (SELECT MIN(date) FROM History);
CREATE TEMPORARY TABLE Yr (Year VARCHAR(4), Income FLOAT(8,2), Spent FLOAT(8,2), differ FLOAT(8,2));
Yearly: LOOP
SET @Aa = @Aa 1;
SET @From = CONCAT((@Aa - 1), '-12-20 00:00:00');
SET @To = CONCAT(@Aa, '-12-19 23:59:59');
SET @Count = (SELECT SUM(income) FROM History WHERE date >= @From AND date <= @To);
SET @diff = (SELECT SUM(spent) FROM History WHERE date >= @From AND date <= @To);
INSERT INTO Yr (Year, Income, Spent, differ) VALUES (@Aa, @Count, @diff, (@Count - @diff));
IF (@Aa = (SELECT MAX(YEAR(date)) FROM History)) THEN LEAVE Yearly; END IF;
END LOOP;
SELECT * FROM Yr;
...
Question: I wonder if it's possible to get a custom interval for an annual summary with an condensed SQL query without using a loop?
CodePudding user response：
You can simply add 11 days to the date before applying the year function to get this grouping, e.g.
SELECT YEAR(DATE_ADD(date, INTERVAL 11 DAY)) AS Year,
SUM(income) AS income,
SUM(spent) AS Spent,
IFNULL(SUM(income),0) - IFNULL(SUM(spent),0) AS difference
FROM History
GROUP BY YEAR(DATE_ADD(date, INTERVAL 11 DAY));