Help building a query

[thenajsays]

I have a number of tables in the database that i need to query at once and pull multiple results... the tables are as follows:

clients, including columns such as id, name, consultantId and fee

users, including an id with will correspond with clients.consultantId, name

payments, including the columns, paydate, amount, clientid which corresponds to clients.id, a boolean field indicating whether the scheduled payment was fulfilled.

 

What I need is a query that will look in the database at the clients, compare the sum of all fulfilled payments associated with that client and if the sum equals the fee associated with that client. if it does, i need to look up the date of the last payment and see when the following friday from that date is and pull the consultant data associated with that client from the users database

[trq]

And your code?

[thenajsays]

i havent written it yet, im stumped on the query at the moment. Im not worried about the code, i can handle that, i just need to build the query

[AyKay47]

what does your query look like atm?

[thenajsays]

i dont really have that either... im a bit stumped... i suppose it would look something like this:

SELECT clients.id, clients.name, clients.amount, clients.consultantId, users.id as uid, users.name, payments.paydate, SUM(payments.amount) as paysum, payments.payed FROM clients, users, payments WHERE payments.payed = 1 AND clients.amount = paysum AND  clients.consultantId = users.id

 

but again... not exactly what im looking for

[thenajsays]

ok, i also tried this:

SELECT clients.id, clients.name, clients.amount, clients.consultantId, users.id as uid, users.name, payments.paydate, SUM(payments.amount) as paysum, payments.payed FROM clients, users, payments WHERE payments.payed = 1 AND clients.amount = paysum AND clients.consultantId = users.id GROUP BY payments.clientid ORDER BY payments.paydate DESC

didnt work... said invalid use of the GROUP BY function

[Muddy_Funster]

From what I can work out you are looking for something like this:

SELECT clients.name, clients.amount, users.name, MAX(payments.paydate) as lastPay, SUM(payments.amount) as paysum
FROM users 
LEFT JOIN clients ON
  (clients.consultantid = users.id)
LEFT JOIN payments ON
  (clients.id = payments.clientid)
WHERE payments.payed = 1
GROUP BY clients.id
ORDER BY lastPay

You will probably need to tweek the joins and fields selected at best but it should be enough to get you going.

[thenajsays]

thanks, ill try this out today! any guidance on calculating the date of the following friday?

[thenajsays]

ok, this seems like a step in the right direction... here is the actual query i used (note that some fields are different as i simplified things previously when trying to explain:

$query = "SELECT clients.firstname as clfn, clients.lastname as clln, clients.fee, clients.type, CONCAT(users.firstname, ' ', users.lastname) as conname, MAX(payments.pdate) as findate, SUM(payments.amount) as paysum, leadsource.source

FROM users

LEFT JOIN clients ON

  (clients.consultant = users.id)

LEFT JOIN payments ON

  (clients.id = payments.clientid)

LEFT JOIN leadsource ON

  (clients.leadid = leadsource.id)

WHERE payments.cashed = 1 AND SUM(payments.amount) = clients.fee AND MONTH(payments.pdate) = $month AND YEAR(payments.pdate) = $year

GROUP BY clients.id

ORDER BY findate";

when i added in the clause "SUM(payments.amount) = clients.fee" it spat out an invalid use of the group by function... but without it, it pulls anyone with a payment made in that month

[kickstart]

Hi

 

Looks generally OK but would expect your GROUP BY field to be one you have selected (although really you should have all the non aggregate fields in the GROUP BY clause - some flavours of SQL insist on it).

 

Biggy I suspect is the missing spaces around the GROUP BY clause

 

All the best

 

Keith

[thenajsays]

thanks for all your help gentleman, ive got it working!

[thenajsays]

ok, it seems it's not working all the way... in testing it worked fine, but put into production it failed miserably... here is my php code to build the query:

$query = "SELECT clients.firstname as clfn, clients.lastname as clln, clients.fee, clients.type, CONCAT(users.firstname, ' ', users.lastname) as conname,

DATE_ADD(CURRENT_DATE,INTERVAL mod(6-DAYOFWEEK(MAX(payments.pdate))+6,7) DAY)as findate, SUM(payments.amount) as paysum, leadsource.source

FROM users

LEFT JOIN clients ON

  (clients.consultant = users.id)

LEFT JOIN payments ON

  (clients.id = payments.clientid)

LEFT JOIN leadsource ON

  (clients.leadid = leadsource.id)

WHERE payments.cashed = 1 AND clients.type != 'Loan Modification'";

if(isset($_GET['consultant'])&&$_GET['consultant']<>'') $query .= " AND clients.consultant = ".$_GET['consultant'];

$query .= " GROUP BY clients.id";

if(!isset($_GET['from'])&&!isset($_GET['to'])&&!isset($_GET['exact'])) $query .= " HAVING SUM(payments.amount) = clients.fee AND MONTH(DATE_ADD(CURRENT_DATE,INTERVAL mod(6-DAYOFWEEK(MAX(payments.pdate))+6,7) DAY)) = $month AND YEAR(DATE_ADD(CURRENT_DATE,INTERVAL mod(6-DAYOFWEEK(MAX(payments.pdate))+6,7) DAY)) = $year";

else {

if(isset($_GET['from'])&&isset($_GET['to'])){

$from = explode("/",$_GET['from']);

$from = $from[2].'-'.$from[0].'-'.$from[1];

$to = explode("/",$_GET['to']);

$to = $to[2].'-'.$to[0].'-'.$to[1];

$query .= " HAVING SUM(payments.amount) = clients.fee AND DATE_ADD(CURRENT_DATE,INTERVAL mod(6-DAYOFWEEK(MAX(payments.pdate))+6,7) DAY) >= '$from' AND DATE_ADD(CURRENT_DATE,INTERVAL mod(6-DAYOFWEEK(MAX(payments.pdate))+6,7) DAY) <= '$to'";

}

if(isset($_GET['exact'])){

$exact = explode("/",$_GET['exact']);

$exact = $exact[2].'-'.$exact[0].'-'.$exact[1];

$query .= " HAVING SUM(payments.amount) = clients.fee AND DATE_ADD(CURRENT_DATE,INTERVAL mod(6-DAYOFWEEK(MAX(payments.pdate))+6,7) DAY) = '$exact'";

}

}

$query .= " ORDER BY findate";

 

for some reason, this is taking everything in the database and assuming that the month in the date is THIS month... and i cant for the life of me fathom why