John Davidson

Message:

I want to store restaurant's monthly revenue (income and expenses), and later show it on a line graph for every year. I have come to this workaround I would store in year variable for example 2020-01-01 and then it should be adding values to all the following row for every month. Would it even work this way? Or should I use one date type and add months to it?

---

My Answer:
You can store the monthly revenue data in a MySQL table with a date column and separate columns for income and expenses. Here is an example table structure:


CREATE TABLE monthly_revenue (
id INT AUTO_INCREMENT PRIMARY KEY,
date DATE,
income DECIMAL(10, 2),
expenses DECIMAL(10, 2)
);


You can then insert monthly revenue data into this table like this:


INSERT INTO monthly_revenue (date, income, expenses) VALUES ('2020-01-01', 10000.00, 5000.00);
INSERT INTO monthly_revenue (date, income, expenses) VALUES ('2020-02-01', 12000.00, 6000.00);
INSERT INTO monthly_revenue (date, income, expenses) VALUES ('2020-03-01', 15000.00, 7000.00);


To show the data on a line graph for every year, you can use a query to retrieve the data for a specific year and then use a charting library like Chart.js to display the data on a line graph.

If you want to add values to all the following rows for every month, you can use a query to calculate the cumulative values for each month. Here is an example query to calculate the cumulative income and expenses for each month:


SELECT
date,
income,
expenses,
(SELECT SUM(income) FROM monthly_revenue AS mr2 WHERE YEAR(mr2.date) = YEAR(mr.date) AND MONTH(mr2.date) <= MONTH(mr.date)) AS cumulative_income,
(SELECT SUM(expenses) FROM monthly_revenue AS mr2 WHERE YEAR(mr2.date) = YEAR(mr.date) AND MONTH(mr2.date) <= MONTH(mr.date)) AS cumulative_expenses
FROM monthly_revenue AS mr
WHERE YEAR(date) = 2020
ORDER BY date;


This query will give you the cumulative income and expenses for each month in the year 2020. You can then use this data to create a line graph showing the monthly revenue trends for the year.