I need help
I have two different queries one Invoice and one Payment. I
want to be able to join the queries together and get one report. I need the
report to show me the client and what company the invoice was bill to and whatever
payment for that client from any of the companies we have. Here is a sample of the querie
I have written for invoice and payment.
SELECT "Invoices"."ClientID",
"Invoices"."CompanyID",
"Invoices"."InvoiceNo",
"Invoices"."DateCreated",
"InvoiceDetails"."Amount", "Invoices"."Type"
FROM
"PHCS"."dbo"."Invoices"
"Invoices" INNER JOIN
"PHCS"."dbo"."InvoiceDetails"
"InvoiceDetails" ON
"Invoices"."InvoiceNo"="InvoiceDetails"."InvoiceNo"
WHERE
("Invoices"."ClientID"='ABCD02' OR
"Invoices"."ClientID"='ABSWC01' OR
"Invoices"."ClientID"='ACAUP01' OR
"Invoices"."ClientID"='ACDP01' OR
"Invoices"."ClientID"='ACDPC01' OR
"Invoices"."ClientID"='ACHLP01' OR
"Invoices"."ClientID"='ACSAM01' OR "Invoices"."ClientID"='ADAPT01')
ORDER BY
"Invoices"."CompanyID",
"Invoices"."InvoiceNo"
SELECT "Payments"."ClientID",
"Payments"."CompanyID",
"PaymentDetails"."PaymentNo",
"PaymentDetails"."DateCreated",
"Payments"."Type", "PaymentDetails"."Amount",
"PaymentDetails"."InvoiceNo",
"Payments"."Reference"
FROM
"PHCS"."dbo"."Payments" "Payments"
INNER JOIN "PHCS"."dbo"."PaymentDetails"
"PaymentDetails" ON
"Payments"."PaymentNo"="PaymentDetails"."PaymentNo"
WHERE ("Payments"."ClientID"='A
PHILL01' OR "Payments"."ClientID"='ABBRA01' OR
"Payments"."ClientID"='ABCD02' OR "Payments"."ClientID"='ABSWC01'
OR "Payments"."ClientID"='ACAUP01')
ORDER BY "Payments"."CompanyID",
"PaymentDetails"."PaymentNo"
This is how I would like the report to look to group the Inv# together from Payment and Invoice
Client Company1 Inv# Date
Amt Paymt# Paymt
type Inv# Date Amt
ABCD02 ABC 100 1/1/11 250.00
ABCD02 EFG 2356
CH 100 2/10/11 250.00
Any help I would appreciate it.
Thanks