0

I have a table of job payments, and when matching to transactions need to match up payments on the same day for the same customer that equal a single transaction for that day. I have been able to match, but only return the aggregated total, not each row.

I understand a join will be required but haven't been able to get it right.

DB:

create table jobs(
  jobid int(7),
  datein date,
  total numeric(5,2),
  chargeto int(5)
);
create table payments(
  payid int(7),
  paymentamount numeric(5,2),
  paymentjobno int(5),
  paymentdate date,
  paymenttype int(1)
);

insert into jobs (jobid, total) values (1000, 100, 4);
insert into jobs (jobid, total) values (1001, 50, 4);
insert into jobs (jobid, total) values (1002, 25, 4);
insert into jobs (jobid, total) values (1003, 220, 6);
insert into jobs (jobid, total) values (1004, 200, 7);

insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (1, 100, 1000, 01/10/2019, 1);
insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (2, 50, 1001, 01/10/2019, 1);
insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (3, 25, 1002, 01/10/2019, 1);
insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (4, 220, 1003, 01/10/2019, 1);
insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (5, 200, 1004, 01/10/2019, 1);

Code I have:

$groupmatches = $con->query("
SELECT j1.*, j2.*
FROM jobs AS j1
JOIN (SELECT jobs.jobid, payments.paymentamount, payments.paymentjobno, COUNT(*) AS count
  FROM (jobs LEFT JOIN payments ON payments.paymentjobno = jobs.jobid)
  WHERE payments.paymentdate = '$dt' AND (payments.paymenttype = 1 OR payments.paymenttype = 2 OR payments.paymenttype = 4)
  GROUP BY jobs.chargeto
  HAVING SUM(payments.paymentamount) = '$at' AND COUNT(*) > 1) AS j2
ON j1.jobid = j2.paymentjobno;
");

It is matching and returning a single line with a count value of how many lines added up to a match, but I can't return each of those lines. What am I doing wrong?

Dave
  • 3
  • 2

2 Answers2

0

I think I've grasped your problem - which I solved like this (see the fiddle here):

Your data isn't quite correct in your sample above. I modified it slightly thus:

jobs table:

insert into jobs values (1000, '2019-10-01', 100, 4);
insert into jobs values (1001, '2019-10-01', 50, 4);
insert into jobs values (1002, '2019-10-01', 25, 4);
insert into jobs values (1003, '2019-10-01', 220, 6);
insert into jobs values (1004, '2019-10-01', 200, 7);

The datein and paymentjobdate are the same - same day payments from your question: - need to match up payments on the same day for the same customer that equal a single transaction for that day.

payments table:

insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (1, 25, 1000, '2019-10-01', 1);
insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (1, 25, 1000, '2019-10-01', 1);
insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (1, 25, 1000, '2019-10-01', 1);
insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (1, 25, 1000, '2019-10-01', 1);

Job 1000 - note that there are 4 separate payments of 25 = jobs.total is 100, so the SUM matches.

insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (2, 50, 1001, '2019-10-01', 1);

Job 1001, one single payment which matches the total.

insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (3, 10, 1002, '2019-10-01', 1);

Job 1002 - total is 25, but payment is only 10 - not a match.

insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (4, 220, 1003, '2019-10-01', 1);

Single matching payment for jobid 1003.

insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (5, 100, 1004, '2019-10-01', 1);

Finally, job 1004 - total is 200, payment is only 100 - no match for SUM.

Then, I performed the following SQL (1st step):

SELECT p.paymentjobno AS p_jobid, SUM(p.paymentamount) AS the_sum
FROM payments p
-- WHERE paymenttype IN (1, 2, 4) -- vary the WHERE clause as necessary
-- AND p.paymentdate = '2019-10-01' -- add as many predicates as you like!
GROUP BY p.paymentjobno;

Result:

p_jobid the_sum
1000    100.00
1001    50.00
1002    10.00
1003    220.00
1004    100.00

Now I have both the jobid and the SUM, so now can join to payments on both the jobid and that SUM using this as a subquery. The SQL:

SELECT j.*, p.*  -- choose your fields as required!
FROM jobs j 
JOIN
(
  SELECT p.paymentjobno AS p_jobid, SUM(p.paymentamount) AS the_sum
  FROM payments p
  -- WHERE paymenttype IN (1, 2, 4) -- vary the WHERE clause as necessary
  -- AND p.paymentdate = '2019-10-01' -- add as many predicates as you like!
  GROUP BY p.paymentjobno;    
) AS t1
  ON j.jobid = t1.p_jobid 
AND j.total = t1.the_sum
JOIN payments p
ON j.jobid = p.paymentjobno;

Result:

jobid   datein  total   chargeto    payid   paymentamount   paymentjobno    paymentdate paymenttype
1000    2019-10-01  100.00  4   1   25.00   1000    2019-10-01  1
1000    2019-10-01  100.00  4   1   25.00   1000    2019-10-01  1
1000    2019-10-01  100.00  4   1   25.00   1000    2019-10-01  1
1000    2019-10-01  100.00  4   1   25.00   1000    2019-10-01  1
1001    2019-10-01  50.00   4   2   50.00   1001    2019-10-01  1
1003    2019-10-01  220.00  6   4   220.00  1003    2019-10-01  1


Which is, if I've understood you correctly, what you were looking for. You have a match on the total for jobid 1000's 4 payments, and the single payments of the total for jobids 1001 and 1003 but not on 1002 and 1004 which haven't been fully paid on that day!

If this doesn't correspond to your request, please let me know and I will try and fix it. May I recommend that you use underscores, i.e. python_case_for_variables to separate your variables (table and column names) into legible parts and use UPPER CASE for SQL - it makes things much easier to read - if you don't like that, search for "SQL coding standards" and find one that appeals and then stick to it! May I also suggest that you use singular names for tables - a table is a set of things!

Finally, always include a version number with your question - with MySQL especially, it can make a huge difference! Also, what could greatly help would be a working fiddle - it's things like this that get you quality answers quickly! Help us to help you. My profile has a few articles on how to ask questions here - you might want to take a look? Finally, as I said in comments - welcome to the forum! :-)

EDIT in the light of OP's comment and fiddle:

What you want is GROUP_CONCAT! Fiddle here.

SQL:

SELECT 
      j.jobid, 
      GROUP_CONCAT(p.paymentamount SEPARATOR ', ') AS payment_hist
FROM jobs j 
JOIN 
(
  SELECT p.paymentjobno AS p_jobid, SUM(p.paymentamount) AS the_sum
  FROM payments p GROUP BY p.paymentjobno
) AS t1 ON 
  j.jobid = t1.p_jobid AND j.total = t1.the_sum 
JOIN payments p ON
  j.jobid = p.paymentjobno 
GROUP BY j.jobid;

Result:

jobid.  payment_hist
1000    25.00, 25.00, 25.00, 25.00
1001    50.00
1003    220.00

I would advise strongly that you avoid like the plague anything to do with comma separated lists when working in SQL. They are contrary to the relational model and breach first normal form. They are also tricky to deal with despite the existence of constructs like GROUP_CONCAT, STRING_AGG and LIST_AGG. If you are going to be doing a lot of this kind of work and you have the possibility, I would change to PostgreSQL - it's much more powerful for this sort of work.

One "fly in the ointment" though!

I hope the above anwers your query but I'm not sure that it corresponds to your own fiddle. For example, you have 

insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (1, 100, 1000, '2019-10-01', 1);

and for jobid 1000, you have 

insert into jobs values (1000, '2019-10-01', 100, 4);

However, the payment above is the one and only payment corresponding to the full amount for jobid 1000, but in your result set, you have 

jobid     payment_hist
1000,     100, 175

The first payment is the 100, but I'm at a loss to understand where the 175 comes from - it's not from other payments and all of your jobids appear to have been paid in full in a single payment, so no partial payments which is what I thought your question was about?

Vérace
  • 30,923
  • 9
  • 73
  • 85
0

I already (tried) to answer this question here, but I'm not deleting it so that my (admittedly sometimes garbled) thought processes are apparent and to remind me always to read the question!

So, using the data in the OP's fiddle here (a comment to my first answer), I first ran the SQL below - my own fiddle for this (final) answer is here. It works in MySQL versions 5.6, 5.7 and 8:

SELECT j.chargeto AS ct, SUM(p.paymentamount) AS p_total
FROM jobs j
JOIN payments p
  ON j.jobid = p.paymentjobno
-- WHERE j.chargeto IN (1, 2, 4)  -- you have AND (payments.paymenttype = 1 OR payments.paymenttype = 2 OR payments.paymenttype = 4)
-- AND <add predicate here...> -- add as many as you like
GROUP BY j.chargeto

Result:

ct  p_total
4   175.00
6   220.00
7   200.00

Then, I had to join this with the payments for each payment per charge type.

Desired result - only want ct = 4 

1000, 100, 175
1001, 50, 175
1002, 25, 175

ct = 4 comes from:

AND (payments.paymenttype = 1 OR payments.paymenttype = 2 OR payments.paymenttype = 4)

I changed this to IN (1, 2, 4) - more readable.

SELECT j.jobid, p.paymentamount, t1.p_total,
CONCAT(j.jobid, ', ', p.paymentamount, ', ', t1.p_total) AS csv  -- I advise against this!
FROM jobs j
JOIN
(
  SELECT j.chargeto AS ct, SUM(p.paymentamount) AS p_total
  FROM jobs j
  JOIN payments p
    ON j.jobid = p.paymentjobno
  WHERE j.chargeto IN (1, 2, 4)
  -- AND <add predicate here...> -- add as many as you like
  GROUP BY j.chargeto
) AS t1
ON j.chargeto = t1.ct
JOIN payments p
  ON j.jobid = p.paymentjobno

Result:

jobid   paymentamount   p_total     csv
1000           100.00    175.00     1000, 100.00, 175.00
1001            50.00    175.00     1001, 50.00, 175.00
1002            25.00    175.00     1002, 25.00, 175.00

The desired result is the csv column, but as far as I'm concerned the desirable result is in the first three separate fields - any type of .csv field is IMHO a bad idea when using SQL - see my previous answer (the bits about GROUP_CONCAT, STRING_AGG & LIST_AGG).

Vérace
  • 30,923
  • 9
  • 73
  • 85