I have three tables:
tblInvoices: fldInvoiceID, fldInvouceDate, fldInvoiceCustomer
tblInvoiceItems: fldInvoiceID, fldItemID, fldItemQty, fldItemProdID, fldVatID
tblVatRates: fldVatRateID, fldVatRate
with the following records:
1, 0; 2, 6; 3, 12; 4, 21;
Now I want to print at the bottom of my invoice a list of total vat-amounts per fldVatRate.
Example:
0 % – 12.00 €
6 % – 25.65 €
12 % – 0.00 €
21 % – 5.19 €
I've try a lot of t-sql statements but never get the real thing. The last t-sql give me the desired outpu BUT the 12% is not mentionde while it is 0. How can I get the wanted output ?
The sql untel now:
SELECT [HJ-Admin].tblVatRates.fldVatID, [HJ-Admin].tblInvoiceDetail.fldItemQty,
[HJ-Admin].tblInvoiceDetail.fldItemUnitPrice, [HJ-Admin].tblInvoiceDetail.fldLineTotal,
[HJ-Admin].tblVatRates.fldVatRate, [HJ-Admin].tblInvoiceDetail.fldLineTotal * [HJ-Admin].tblVatRates.fldVatRate / 100 AS fldVatAmount,
[HJ-Admin].tblInvoiceDetail.fldVatRateID
FROM [HJ-Admin].tblVatRates LEFT OUTER JOIN
[HJ-Admin].tblInvoiceDetail ON [HJ-Admin].tblVatRates.fldVatID = [HJ-Admin].tblInvoiceDetail.fldVatRateID
Result:
As you can see the Vat-rate 6% (ID = 2) is mentioned twice while it shoulde be once with the total of the two records (= 1.725000). The NULL-values are just fine to see the amount is NULL.
Best Answer
It should be fairly simple then:
or with a total rows as well:
Or with the grouping that you are after (only an overall total row, no totals per grouping level):
With InvoiceID:
Try to put the InvoiceID into the where, will be more performant.
If you want to also include all non matching rows from the tblVatRate table: