Hey all,
I've got a bit of a problem with a mysql query which I am trying to fix and avoid having to do it the long way.
Basically I have a table called tblclientinvoices which has four fields that i am using;
invoiceamount;
acis_cost;
paidtodate;
paidinfull;
Basically i am trying to grab all the invoices from the table and do the following calculation on them. Here is the logic, hope it is easy to follow 🙂 this is not my code below, more an explanation.
if paidinfull='True', sum(paidtodate-acis_cost);
else, sum(invoiceamount-acis_cost)...
Here is my sql;
select sum(tblclientinvoices.invoiceamount) as tot_bus, if(tblclientinvoices.paidinfull='True', sum(paidtodate-acis_cost), sum(invoiceamount-acis_cost)) as tot_prof, tblclientinvoices.clientid, tblclients.clientname, tblclients.state, tblclients.MarketingFlag, count(*) as the_count from tblclientinvoices left join tblclients on tblclients.clientid=tblclientinvoices.clientid where 1=1 and confirmedflag='True' and (writtenoff='False' or writtenoff='' or writtenoff IS NULL) and tblclients.marketingflag='True' group by clientid order by the_count
Basically what is happening is the query is rocking along but as soon as it hits a paidinfull that is 'True', from then on every calculation uses (paidtodate-acis_cost) instead of checking every invoice first....giving me incorrect results.
Is there away to fix this or will I have to write a more complex function?
Thanks in advance
