May 19, 2010 at 9:03 am
🙂
May 20, 2010 at 4:16 am
Hi,
I think there are 2 ways of dealing with this. You can either create a separate query and add this to a separate dataset and then show the tax section as a separate table in the report.
Or you can create a separate report for the tax breakdown and then pull this in to your report as subreport in the detail section linking on the invoice number.
The calculation can be done in a single statement it would need to be something like this:
select invoice_number,sum(invoice_amt) as Total_Inv_Amt from INVOICE_TABLE group by invoice_number
the above statement will return the total amount for the invoice
select invoice_number,tax_code,sum(invoice_amt) as Total_Tax_Amt from INVOICE_TABLE group by invoice_number,tax_code
the above statement will return the invoice amount for each of tax codes
We can now combine these together and the calculate the percentages
SELECT
invoice_num,
tax_code,
total_tax_amt/total_inv_amt as [Invoice Percentage]
FROM
(select invoice_number as Invoice_num,tax_code,sum(invoice_amt) as Total_Tax_Amt from INVOICE_TABLE group by invoice_number,tax_code) as Derived
JOIN
(select invoice_number as Invoice_num2,sum(invoice_amt) as Total_Inv_Amt from INVOICE_TABLE group by invoice_number) as Derived2
on derived.invoice_num = derived2.invoice_num2
The derived query is basically making a select query into a table.
Hope this helps, let me know how you get on:-)
May 21, 2010 at 10:18 am
🙂
May 24, 2010 at 4:36 am
Hi,
It looks like you need to do a left join rather than an equal join.
This will return all the data from the first table (derived query) and then only return the matching data in the second table (derived2 query).
Also I think you may need to change the invoice percentage calculation so that when either the tax amount or invoice amt is is null or zero it does not fall over.
first validate the query by checking the invoice amount and the tax amount then work on the calculation.
SELECT
det.order_id, vat.tax_code,
total_tax_amt,
total_inv_amt
--case when total_tax_amt = 0 then 0
--else
--total_tax_amt/total_inv_amt end as [Invoice Percentage]
FROM
(select det.order_id as order_id,vat.tax_code,sum(det.cur_amount) as [Total_Tax_Amt]
from afiinvdet det, afitrans vat
group by det.order_id, vat.tax_code) as Derived
LEFT JOIN
(select det.order_id as Invoice_num2,sum(det.cur_amount) as [Total_Inv_Amt]
from afiinvdet det, afitrans vat
group by det.order_id,vat.tax_code) as Derived2
on derived.order_id = derived2.invoice_num2
--the above statement aims to work out the % for each tax code
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply