I have a table in postgres called workorders. In it are various headings. The ones I am interested in are labor, date_out and ident. This table ties up with wo_parts (workorder parts). In this table are the headings I am interested in, part and workorder. Both are integers. (part auto number) The final table is part2vendor and the headings are retail and cost. Right, basically what happens is.....I create a workorder (invoice). This calls a part from part2vendor. I enter it and invoice it off. In workorder a row is created and saved. It is given an ident. In wo_parts, the part i used is recorded as well as workorder number and qty used. What I want to do is create a report in php that pools all this info on one page. IE. if i choose dates 2009-10-01 to 2009-10-31 it will pull all workorders in this range and tell me the total labour sold and then the PROFIT (retail less cost) of the parts I sold, using these 3 tables. I hope i have explained as clear as possible. any questions please ask me. Thank you very much for your time.
1
-
If you'll post the CREATE statements for all the tables involved, it will be much easier to understand your system.cope360– cope3602009-10-28 21:48:07 +00:00Commented Oct 28, 2009 at 21:48
Add a comment
|
1 Answer
You will want to read up on SQL - keywords to look for include "aggregate", "SUM" and "GROUP BY".
You query will look something like (but this will certainly need correcting):
SELECT SUM(wo.labor) AS tot_labor, SUM(p2v.cost - p2v.retail) AS tot_profit FROM workorders AS wo JOIN wo_parts AS wp ON wo.ident=wp.ident [?] JOIN part2vendor AS p2v ON ...something... WHERE date_out BETWEEN '2009-10-01'::date AND '2009-10-31'::date;
