Ninja's_RGR'us (4/8/2010)
Chris Morris-439714 (4/8/2010)
Backtracking a little...Orders (OrderID)
OrderLines (OrderLineID)
Shipping (ShippingID)
ShippingLines (ShippingLineID)
Invoices (InvoiceID)
InvoiceLines (InvoiceLineID)
Which tables hold fk's of which other tables?
ShippingHeader and InvoiceHeader has FK to OrderID
1 order can have n shippings
You cannot have more than 1 order in a single shipping
1 shipping can have 1 or less invoice... 1 shipping, 2 shipping or more or even all shippings can be merged into a single invoice.
I already filtered what I can from document dates before I get to that stage (an invoice of may 1st cannot be linked to a shipment of may 15th. The invoice needs to be same day of the shipping or later).
Nothing to link Shipping to Invoice except matching row for row using document + rowid (where 100% of the rows match in the details of 1 to many documents... 1 invoice to n shippings), sku, and Sum(qty) of all shippings to the invoice lines
Thanks. It's still horrible!
FYI the number of combinations is 2(number of rows)-1, i.e. for 6 rows, that's 63 combinations.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden