Back to Samples

Source tables

invoiceheader
invoicenum | invoicedate | invoiceamount
-----------+-------------+--------------
00551198   | 1/1/2014    | $150.5
00551199   | 1/2/2014    | $10
invoicedetail
invoicenum | trackingno         | detailamount
-----------+--------------------+-------------
00551198   | 1Z2F12346861507266 | $50
00551198   | 1Z2F12346861507267 | $80
00551198   | 1Z2F12346861507268 | $20.5
00551199   | 1Z2F12346861503423 | $10.5
invoicecharges
invoicenum | trackingno         | chargetype | chargeamount
-----------+--------------------+------------+-------------
00551198   | 1Z2F12346861507266 | FRT        | $40
00551198   | 1Z2F12346861507266 | FUE        | $11
00551198   | 1Z2F12346861507267 | FRT        | $65
00551198   | 1Z2F12346861507267 | FUE        | $17
00551198   | 1Z2F12346861507268 | FRT        | $10
00551198   | 1Z2F12346861507268 | FUE        | $5
00551198   | 1Z2F12346861507268 | HAZ        | $5.5
00551199   | 1Z2F12346861503423 | FRT        | $8
00551199   | 1Z2F12346861503423 | FUE        | $2.5

Report 1

For specified time period print all invoices
for each show invoicenum, invoice date and invoice amount
Print a total line: # of invoices and total amount for the period

invoice #invoice dateinvoice amount
005511982014-01-01150.50
005511992014-01-0210.00

Total # of invoices: 2, total amount: 160.50

Report 2

For specified time period print all invoices where the invoice amount
does not match the sum of all detailamount column values for this invoice
for each show invoicenum, invoice date and invoice amount,
detailamount total and the discrepancy amount

invoice #invoice dateinvoice amountdetail amount totaldiscrepancy
005511982014-01-01150.50150.500.00
005511992014-01-0210.0010.50-0.50

Report 3

For specified time period print all tracking numbers where the detailamount does not match
the sum of all chargemount column values for this invoice and trackingno
for each show invoicenum, invoice date, trackingno, detailamount, chargeamount
total and the discrepancy amount

invoice #invoice datetracking #detail amount totalcharges amount totaldiscrepancy
005511982014-01-011Z2F1234686150726650.0051.00-1.00
005511982014-01-011Z2F1234686150726780.0082.00-2.00