0

I have two tables:

table "documents"

id | date        | money
1  | 20.01.2020  | 100                    (this is a bill)
2  | 25.01.2020  | 25                     (it is a payment)
3  | 30.01.2020  | 75                     (it is a payment)
4  | 12.12.2020  | 12                     (this is a bill)

P.S. date is in unix timestamp format if that's relevant.

table "payments"

parent_id | child_id | money
1         | 2        | 25   
1         | 3        | 75

In "payments" table are records that link payments together by document id.

I want to make a query where I can filter documents to certain date. For example:

If date is 22.01.2020. The result should be:

id | money
1  | 0 
4  | 0

In both rows money is zero because before 22.01.2020 there is no records in "payments" table.

If date is 25.01.2020. The result should be:

id | money
1  | 25       (because there is a record in "payments" table that dates before 25.01.2020) 
4  | 0

If date is 25.01.2020. The result should be:

id | money
4  | 0

id 1 is no longer shown because the sum of both registered payments 25 + 75 is 100 which is total amount of id 1 bill.


Hopefully it is clear so far.

If information below is not clear, can please write query for the above example.


Additional conditions:

  • One bill might be paid in multiple payments (example above).
  • One payment might cover more than one bill.
  • Document is shown only if it is not paid or partially paid to the supplied date.

I tried varions queries, but something doesn't work for each one of them.

Please help.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.