Clash Royale CLAN TAG#URR8PPP

MySQL create and/or update child table rows from header parent row for standing orders

I am writing a system to manage income and expenses using MySQL but it needs to be able to handle standing orders i.e. incomes or expenses which repeat monthly, quarterly or yearly.

Also, ideally I would trigger a query to delete child rows if the associated header row is deleted.

So I have a table with a name, start and end date, payment frequency (M/A) and I need to output all the payment dates between start and end given the frequency (M)onthly or (A)nnual using a MySQL procedure.

My header table say sorders_head would be of the form

id, 1
name, Council Tax
payment, £300
start_date, 05/04/2018
end_date, 05/01/2019
frequency, M


My line table say sorders_line would need to create the following rows

id
sorders_head_id, key to header table id
payment_date
payment


The output of the sorders_line table should be as follows :-

1,1,05/04/2018,£300
2,1,05/05/2018,£300
3,1,05/06/2018,£300
etc. ending
10,1,05/01/2019,£300


I have been trying to find examples of a MySQL procedure which will process 2 dates in this way using MySQL, I will continue looking, any help would be much appreciated, thanks.

I have written the following procedure which does what I want except for being generic for frequency, , which I will work on now ..

BEGIN

DECLARE currDate DATE;
DECLARE endDate DATE;

SELECT *,
@currDate := min(DATE_FORMAT(sh.date_from, "%Y-%m-01")),
@endDate := max(DATE_FORMAT(sh.date_to, "%Y-%m-01"))
FROM
sorders_head sh
WHERE
code = 'PENS-TEST';

SET currDate = @currDate;
SET endDate = @endDate;



REPEAT

-- execute your queries for every frequency say MONTH
SELECT currDate;

-- ADD INSERT COMMANDS HERE
INSERT INTO sorders_line(code, name, type, paydate, amount, member_id, account_id, sorders_head_id)
SELECT code, name, type, `currDate`, amount, '1', '6', id
FROM sorders_head
WHERE
code = 'PENS-TEST';

-- ADD INSERT COMMANDS HERE




SET currDate = DATE_ADD(currDate,INTERVAL 1 MONTH);
UNTIL currDate > endDate



END REPEAT;

END


This successfully inserts rows into the sorders_line table

currDate
2012-09-20

currDate
2012-10-20

currDate
2012-11-20

currDate
2012-12-20

currDate
2013-01-20


etc. ......

This now works, I just need to work out the following :-

1/ the procedure should work for different frequency types e.g. (M or A)

2/ create a trigger to call the procedure for each INSERT of an sorders_head row
3/ use the current sorders_head.code value in the WHERE clause shown above

4/ integrate into my wpDataTables if possible.

Colin

ON DELETE CASCADE

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.