Preparing Invoices is Truly Fun
By Kalman Toth, M.Phil., M.Phil., MCDBA, MCITP

June 11, 2005

Businesses commonly accept purchase orders from each other. Upon receipt of the PO the product(s) is shipped and the customer receives an invoice to be paid. The SQL query below generates the invoicing data which can be fed into a form reporting program for actual invoice printing. The GROUP BY brings together all the details for a particular PO. The WHERE clause narrows down the output.

USE AdventureWorks;
SELECT
InvoiceNo = convert(char(8),getdate(),112)+right('000'+convert(varchar,ROW_NUMBER() over (ORDER BY ph.PurchaseOrderID)),4),
Vendor=v.Name,
POID=ph.PurchaseOrderID,
Items=count(*),
SubTotal=convert(numeric(10,2),sum(subtotal)),
Tax=convert(numeric(10,2),sum(TaxAmt)),
Freight=convert(numeric(10,2),sum(Freight)),
Total='$'+convert(varchar,sum(TotalDue),1)
FROM
Purchasing.Vendor v
INNER JOIN
Purchasing.PurchaseOrderHeader ph
ON v.VendorID=ph.VendorID
INNER JOIN Purchasing.PurchaseOrderDetail pd
ON ph.PurchaseOrderID = pd.PurchaseOrderID
INNER JOIN Production.Product p
ON pd.ProductID = p.ProductID
WHERE
v.Name like ('P%') and datepart(month,ph.OrderDate)=5
GROUP BY
v.Name,ph.PurchaseOrderID
ORDER BY
v.Name,ph.PurchaseOrderID;

Here is the output: