Hi all,
I’m working on an addon for a CRM that will pull related tax charge information into a sales report but the related information table may have several entries (multiple taxes) for the same invoice number and may need to be hit several times to give all of the info. Furthermore I’m making this as dynamic as possible so you may set up just one tax ever or you may have several so the sub-query (if that’s the right term) will need to be dynamic as well. Here’s a simplified example of what I want to do:
SELECT invoice_number, transaction_type, amount FROM tbl_sales;
This returns
-------------------------------------------------
| invoice_number | transaction_type | amount|
-------------------------------------------------
|000700234 | Donation | 10 |
|000700265 | Membership | 50 |
|000700253 | Pledge | 25 |
|000700275 | Event Registration| 15 |
|000700226 | Donation | 20 |
-------------------------------------------------
The above query just returned the total results. In order to find out how much tax we charged we need to LEFT JOIN on the tbl_tax_invoicing table and pull out the pre_tax value as well as the total_tax charged. I’ll change the query so it looks like the following. Note that because there may be several taxes applicable for a particular transaction_type, I am using SUM(tbl_tax_invoicing.tax_charged) in my query.
SELECT
tbl_sales.invoice_number AS invoice_number,
tbl_sales.transaction_type AS transaction_type,
tbl_sales.amount AS amount,
tbl_tax_invoicing.pre_tax AS pre_tax,
SUM(tbl_tax_invoicing.tax_charged) AS tax_charged
FROM tbl_sales
LEFT JOIN tbl_tax_invoicing
ON tbl_sales.invoice_number = tbl_tax_invoicing.invoice_number;
The Results are:
------------------------------------------------------------------------
| invoice_number | transaction_type | amount| pre_tax| total_tax|
------------------------------------------------------------------------
|000700234 | Donation | 10 | 09 | 01 |
|000700265 | Membership | 50 | 45 | 05 |
|000700253 | Pledge | 25 | 23 | 02 |
|000700275 | Event Registration| 15 | 14 | 01 |
|000700226 | Donation | 20 | 19 | 01 |
------------------------------------------------------------------------
SO that’s all fine and well but now I want to add a couple of dynamic columns to the results because (hypothetically) there are two tax types that apply to Memberships and Event Signups and a single tax for Donations. I can get the tax types out of my tbl_tax_type table and the tax_type_id (tax_type.id) is logged in my tbl_tax_invoicing table so it seems to me that inside my query, I need a sub query for each iteration of the main query (based on invoice number) that selects the results for tax_1 and tax_2 (which are dynamic and could be one or many taxes in tbl_tax_type) and if there are results they need to be inserted into the main query so that the results are:
----------------------------------------------------------------------------------------
| invoice_number | transaction_type | amount| pre_tax| total_tax| tax_1| tax_2|
----------------------------------------------------------------------------------------
|000700234 | Donation | 10 | 09 | 01 | 01 | NULL |
|000700265 | Membership | 50 | 45 | 05 | 02 | 03 |
|000700253 | Pledge | 25 | 23 | 02 | 02 | NULL |
|000700275 | Event Registration| 15 | 13 | 02 | 01 | 01 |
|000700226 | Donation | 20 | 19 | 01 | 01 | NULL |
----------------------------------------------------------------------------------------
Ok, so the tax rates may not make sense but this is all just hypothetical but somehow I’d like to figure out how within SQL queries to create the dynamic sub-query and it could add one or many tax_n’s to the main query depending on the number of taxes that are in the tbl_tax_type table.
Any pointers or ideas?
I hope my ASCII art came through all right for the results sets… It too forever to do them
Thanks,
Andrew