NiallFH Posted January 16, 2013 Share Posted January 16, 2013 (edited) Using MySQL 5.0 This seems a very complicated one. Basic table structure: val_contracts - contract_id, contract_ret (the % of retention the client will take) val_valuations - valuation_id, valuation_con (corresponds with contract_id), valuation_worksvalue, valuation_date This is a database of contracts. Each contract has valuations sent to customers each month, and within that contract is a % figure of retention (monies held by the customer) that they are expected to hold. For example: Value of Work: £20000 Less Retention (5%): £1000 Payment Requested: £19000 As a job or contract progresses, the value of work will increase and also thereby the retention figure will increase, so therefore, at any one time, to gauge the amount of retention outstanding, the MOST RECENT Valuation for each contract is the only one I'm interested in. What I want to do is to be able to show a total figure for ALL outstanding retention on ALL jobs/contracts. To do this, I need to write a query that will first find the most recent valuation (i.e. MAX(valuation_date)) and then somehow add together the value of work less retention (this % changes from client to client) for each job. However, I am completely at a loss as how to do this. Can anyone offer some assistance? Edited January 16, 2013 by NiallFH Quote Link to comment https://forums.phpfreaks.com/topic/273237-calculating-total-of-valuations-based-on-varying-percentage-against-a-sum/ Share on other sites More sharing options...
Jessica Posted January 16, 2013 Share Posted January 16, 2013 Can you write what you mean as a mathematical equation? Quote Link to comment https://forums.phpfreaks.com/topic/273237-calculating-total-of-valuations-based-on-varying-percentage-against-a-sum/#findComment-1406118 Share on other sites More sharing options...
NiallFH Posted January 16, 2013 Author Share Posted January 16, 2013 Can you write what you mean as a mathematical equation? I'll give it a go! At its most basic, it's something like: sum(Value of Works * Retention %) However that value of works figure needs to be taken from the most recent valuation for each contract, and I want the query to disregard anything older. I.e. Three contracts Contract 1: Valuation 1 (Sep 2009), Valuation 2 (Oct 2009), Valuation 3 (Nov 2009) Contract 2: Valuation 1 (Jul 2010), Valuation 2 (Aug 2010) Contract 3: Valuation 1 (Nov 2009), Valuation 2 (Dec 2009), Valuation 3 (Feb 2010), Valuation 4 (Mar 2010) The only ones I would want the value of works figure from are shown in bold: Contract 1: Valuation 1 (Sep 2009), Valuation 2 (Oct 2009), Valuation 3 (Nov 2009) Contract 2: Valuation 1 (Jul 2010), Valuation 2 (Aug 2010) Contract 3: Valuation 1 (Nov 2009), Valuation 2 (Dec 2009), Valuation 3 (Feb 2010), Valuation 4 (Mar 2010) Quote Link to comment https://forums.phpfreaks.com/topic/273237-calculating-total-of-valuations-based-on-varying-percentage-against-a-sum/#findComment-1406120 Share on other sites More sharing options...
Barand Posted January 16, 2013 Share Posted January 16, 2013 (edited) something like this (not tested) SELECT c.contract_id , SUM(valuation_worksvalue * contract_ret) as total FROM val_contracts c INNER JOIN val_valuations v ON c.contract_id = v.valuation_con INNER JOIN ( SELECT valuation_con, MAX(valuation_date) as latest FROM val_valuations GROUP BY valuation_con ) AS getlatest ON v.valuation_con = getlatest.valuation_con AND v.valuation_date = getlatest.latest GROUP BY c.contract_id Edited January 16, 2013 by Barand Quote Link to comment https://forums.phpfreaks.com/topic/273237-calculating-total-of-valuations-based-on-varying-percentage-against-a-sum/#findComment-1406193 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.