-Invoice_Table-
Customer Job_No Sale_Amt
12345678 123456 123.45
87654321 246800 645.23
-Vendor_Table-
Customer Job_No Vendor Vendor_Cost
12345678 123456 ABCDEF 10.50
12345678 123456 UVWXYZ 20.92
87654321 246800 LMNOPQ 12.45
And I have a query similar to the following (but a LOT more complex):
SELECT
Inv.Customer,
Inv.Job_No,
Ven.Vendor,
Ven.Cendor_Cost,
Inv.Sale_Amt
FROM
Invoice_Table Inv
LEFT OUTER JOIN Vendor_Table Ven
ON Inv.Customer = Ven.Customer
AND Inv.Job_No = Ven.Job_No
This query produces output similar to the following:
Customer Job_No Vendor Vendor_Cost Sale_Amt
12345678 123456 ABCDEF 10.50 123.45
12345678 123456 UVWXYZ 20.92 123.45
87654321 246800 LMNOPQ 12.45 645.23
Customer Job_No Vendor Vendor_Cost Sale_Amt
12345678 123456 ABCDEF 10.50 123.45
12345678 123456 UVWXYZ 20.92 0 (or NULL)
87654321 246800 LMNOPQ 12.45 645.23
ASKER
ASKER
ASKER
ASKER
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
TRUSTED BY
ASKER
The report is supposed to show the lines like the sample data above. Basically the Customer, Job_No, Vendor and Vendor_Cost. Although it's prefered to have the Sales_Amt as part of the line, it's confusing in this particular context. The Sales_Amt is, ultimately, being summed as a grand total. In Crystal I can "hide" the duplicate Sales_Amt. However they do still exist and are added into the grand totals.
When the report was originally created, we only used one Vendor per job. In that case, everything was hunky-dory. However, this month it was decided to parcel out work to multiple vendors, which created the problem I have today.