SU2bkhH2

1CREATE TABLE `test_invoice` (
2  `id` INT NOT NULL,
3  `invoice_date` DATE NOT NULL,
4  `due_date` DATE NOT NULL,
5  `invoice_value` DECIMAL(10,2) NOT NULL,
6  PRIMARY KEY (`id`),
7  INDEX `ix_testinvoice_date` (`invoice_date` ASC));
8
9CREATE TABLE `test_payment` (
10  `id` INT NOT NULL,
11  `invoice_id` INT NOT NULL,
12  `payment_date` DATE NOT NULL,
13  `payment_value` DECIMAL(10,2) NOT NULL,
14  PRIMARY KEY (`id`),
15  INDEX `ix_testpayment_date` (`payment_date` ASC),
16  INDEX `ix_testpayment_testinvoice` (`invoice_id` ASC));
17	
18select
19sum(i.invoice_value - coalesce(pay.payment_value, 0))  as due_amount
20from test_invoice i
21  left join (
22    select p.invoice_id, sum(payment_value) as payment_value
23    from test_payment p
24        join test_invoice i on i.id = p.invoice_id
25    where 1=1
26        and i.invoice_date < '2017-12-31'
27        and p.payment_date < '2017-12-31'
28    group by p.invoice_id
29  ) pay on pay.invoice_id = i.id
30where i.invoice_date < '2017-12-31'
31	
32DELIMITER $$
33drop PROCEDURE if exists get_due_amount_at_date_test $$
34
35CREATE PROCEDURE get_due_amount_at_date_test (
36  in _year int,
37  in _month int
38)
39BEGIN
40
41declare _calculation_date date;
42set _calculation_date = date_sub(date_add(date(concat(_year, '-', _month, '-01')), interval 1 month), interval 1 day);
43
44select
45    sum(i.invoice_value - coalesce(pay.payment_value, 0))  as due_amount
46from test_invoice i
47    left join (
48        select p.invoice_id, sum(payment_value) as payment_value
49        from test_payment p
50            join test_invoice i on i.id = p.invoice_id
51        where 1=1
52            and i.invoice_date < _calculation_date
53            and p.payment_date < _calculation_date
54        group by p.invoice_id
55    ) pay on pay.invoice_id = i.id
56where 1=1
57    and i.invoice_date < _calculation_date
58;
59
60END$$
61DELIMITER ;
62	
63call get_due_amount_at_date_test (2017, 10);
64call get_due_amount_at_date_test (2017, 9);