I want to select weekly and monthly records from MySQL database without specifying a start and end date, but by using the first date a record was entered into the database and calculating the sum, 7 days afterwards.
What I want to achieve is every 7 days the cost field in my application must reset to 0 and with each day calculating, until it gets to the 7th day and the cycle starts again.
I have tried the DATE_ADD and DATE_SUB function and both don't work to my expectation.
I have researched and tried many different solutions but they all don't seem to work There is sample of my queries I have tried.
Solution 1
select expense_name, expense_date, cost from expenses
where week(expense_date)
Solution 2
select expense_name, expense_date, cost from expenses
where yearweek(expense_date)
Sample Data
Name Date Cost
Food1 2018-02-23 14
Food2 2018-02-23 8
Food3 2018-02-27 10
Food4 2018-02-27 10
Food5 2018-02-27 50
Food6 2018-03-1 2.6
Food7 2018-03-8 18
Thanks
Am traveling atm but I think this will kinda help you.
Not sure what column name you use for saving the creation date but for now I use “created_at”.
WHERE created_at BETWEEN created_ar AND DATE_ADD( created_add, INTERVAL +1 MONTH)