Home Forums SQL Server 2008 T-SQL (SS2K8) Recursive cross join to get all available combinaisons RE: Recursive cross join to get all available combinaisons

  • 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.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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