-1

I have 2 tables .. one is named as "claim"

+----------+----------+--------+------------+
| claim_id | store_id | amount | created_by |
+----------+----------+--------+------------+
|        2 |        2 | 926.00 |          2 |
|        3 |        2 | 746.50 |          5 |
|        4 |        2 | 989.26 |          5 |
|        5 |        2 | 766.00 |          6 |
|        6 |        2 | 403.00 |          6 |
|        7 |        2 | 130.00 |          6 |
+----------+----------+--------+------------+

the other one "claim_progress"

+----------+------+------+---------------------+
| claim_id | type | paid | created             |
+----------+------+------+---------------------+
|        2 | S    |    0 | 2019-09-12 20:37:26 |
|        3 | S    |    0 | 2019-09-12 21:52:32 |
|        4 | S    |    0 | 2019-09-12 22:33:16 |
|        5 | S    |    0 | 2019-09-12 22:53:58 |
|        6 | S    |    0 | 2019-09-12 22:58:55 |
|        7 | S    |    0 | 2019-09-12 23:01:40 |
|        5 | A    |    0 | 2019-09-21 04:02:58 |
|        6 | A    |    0 | 2019-09-21 04:03:02 |
|        5 | PP   |  150 | 2019-09-21 04:03:10 |
|        5 | PP   |   45 | 2019-09-21 04:03:22 |
+----------+------+------+---------------------+

sure they have more columns to each but I simplify it down to such. claim_progress is the table to keep track of payment status of the claim table from type S for submission then A for approved then PP as progress payment and lastly P for paid. the column "created_by" is the user id that file for the claim.

What I'm trying to do here to get a one liner that produce 2 columns of the total of amount and the remaining unpaid about claim by "created_by". By that I need to get the latest progress of each claim identifying it to be either type "A" or "PP".

The total approved claim by "created_by" 6 should be 1169 and total paid would be 195. BTW here's what I've got so far but I wonder if it's redundant or even wrong?

SELECT SUM(c.amount),cp3.paid FROM claim c
JOIN (SELECT claim_id,MAX(created) as maxprogress FROM claim_progress GROUP BY claim_id) cp ON (c.id=cp.claim_id)
JOIN claim_progress cp2 ON (cp.maxprogress=cp2.created)
JOIN (SELECT claim_id,SUM(paid) AS paid FROM claim_progress GROUP BY claim_id) cp3 ON (cp3.claim_id=cp.claim_id)
WHERE cp2.type IN ('A','PP') AND c.created_by='6'

Hopefully the DB masters in here could shed some light. Thank you.

EDIT:

I'm expecting the answer to be like this

+----------------+------+
| total_approved | paid |
+----------------+------+
|           1169 |  195 |
+----------------+------+
Jeebsion
  • 343
  • 2
  • 3
  • 10
  • 1
    Edit your question and *show* what the result set should look like. How do two numbers in a column called "paid" get added together to become "unpaid"? – Gordon Linoff Sep 20 '19 at 20:39
  • I do not see how the state duplicate is really going to help the OP. There are multiple things that need to be resolved here. – Gordon Linoff Sep 20 '19 at 20:44
  • I have edited the question to include my expected output – Jeebsion Sep 20 '19 at 20:46
  • Sorry Gordon .. I've edited the unpaid to be paid ... the expected figure of 195 is paid .. sorry again Gordon – Jeebsion Sep 20 '19 at 21:07
  • Can you explain 1169? If I add the three created_by = 6 I get 766 + 403 + 130 = 1299 – kjmerf Sep 20 '19 at 22:03
  • hi kjmerf .. only claim with latest type of A or PP in claim_progress to summed. created_by 6 has only 2 approved claims – Jeebsion Sep 20 '19 at 22:40

1 Answers1

1

I feel like I should be able to compress this down by another join, but I just can't figure it out, and it's time to power down for the week. You're incredibly close to optimal here. This is a little bit more streamlined, but is doing essentially the same thing you were:

select sum(c.amount) Total_approved, sum(Progress.paid) paid from claim c
join (select claim_id, sum(paid) paid, max(created) created from claim_progress where type = 'A' group by Claim_id, type) Approved on c.claim_id = approved.claim_id
Left join (select claim_id, sum(paid) paid, max(created) created from claim_progress where type = 'PP' group by Claim_id, type) Progress on c.claim_id = progress.claim_id
Where c.created_by = 6

Hope this helps!

Combee Bowlin
  • 106
  • 5
  • that one works .. kinda sleepy though I gotta consume more time in understanding how your statement works! thanx combee! – Jeebsion Sep 20 '19 at 22:51