Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Recursive cross join to get all available combinaisons


Recursive cross join to get all available combinaisons

Author
Message
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8957 Visits: 19014
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
Exploring Recursive CTEs by Example Dwain Camps
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20931 Visits: 9671
Chris Morris-439714 (4/8/2010)
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.



Thanks for the words of compassion. The really horrible part is that in all those documents the prices don't even match (0.01$ errors on a few details lines). So we tell the client price A on the order. Then price B on the shipping slip (which is our invoice at the moment) and when he comes back on the web site to get the real invoice, he possibly gets a 3rd different price.

A big no-no when the bigger clients refuse to pay for even 0.01$ difference for the whole invoice.

And this is also where I need to come in to fix the problem because on the shipping "invoice" we don't have the real invoice # which is created after the delivery guys come back with the adjusted shipping slip which accounts for returns and refused deliveries.

Which means that the client can only track the invoice with the shipping #... which is even more compunded by the fact that the dates of documents and total amounts DON'T MATCH... and that you can have n shippings on 1 invoice!


A real pleasure to fix!

Oh ya and I don't have access to the source code to correct that little misdesign Crazy... would be so easy to add 1 column and save the value of the invoice on creation and just fix the issue once for the missing data.

Then the problem would only be to concatenate the shipping # if there's more than 1 on the invoice.


Anyhow, 50 hours later and I should be done right about today with a 99.9% correct solution approved by direction.


Anyhow thanks again for all the help.
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8957 Visits: 19014
Ninja (Remi?)

Sympathies mate. How much leeway do you have with the db? Can you for instance sneak in a trigger here and there, and maybe a link table?

It's an interesting challenge as you and others have pointed out, so I'm fiddling with a recursive CTE to see if it can do anything useful.

“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
Exploring Recursive CTEs by Example Dwain Camps
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20931 Visits: 9671
Chris Morris-439714 (4/8/2010)
Ninja (Remi?)

Sympathies mate. How much leeway do you have with the db? Can you for instance sneak in a trigger here and there, and maybe a link table?

It's an interesting challenge as you and others have pointed out, so I'm fiddling with a recursive CTE to see if it can do anything useful.


I have None. I can either call the vendors to change it or create a db besides it and maintain the new field in the new db manually. Anything else voids the contract.

Now the fun part with a new db is that we have 30 incorporations. Currently 3 of them are in that ERP system whereas the rest of them will gradually make the transition.

The really fun part here is that there is only 1 single database. Everytime you add a new company to the system a new set of ±1200 tables is created like so :

dbo.[<name for the cie here>$Invoice Header]


So any script, index, mod or whatever needs to be applied to n number of companies, ideally without changing the scripts I wrote. I've already gotten around that little PITA for indexes, but for keeping dbs in sync that'd be another little project all in itself. I know I can use 2k8 change management to accomplish this but I've never played with it and I'm not sure it's allowed either.

Anyhow if I just run a job once a day I can easily go around that one at that time.
Garadin
Garadin
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1523 Visits: 4107
Ninja's_RGR'us (4/8/2010)
Chris Morris-439714 (4/8/2010)
Ninja (Remi?)

Sympathies mate. How much leeway do you have with the db? Can you for instance sneak in a trigger here and there, and maybe a link table?

It's an interesting challenge as you and others have pointed out, so I'm fiddling with a recursive CTE to see if it can do anything useful.


I have None. I can either call the vendors to change it or create a db besides it and maintain the new field in the new db manually. Anything else voids the contract.

Now the fun part with a new db is that we have 30 incorporations. Currently 3 of them are in that ERP system whereas the rest of them will gradually make the transition.

The really fun part here is that there is only 1 single database. Everytime you add a new company to the system a new set of ±1200 tables is created like so :

dbo.[<name for the cie here>$Invoice Header]


So any script, index, mod or whatever needs to be applied to n number of companies, ideally without changing the scripts I wrote. I've already gotten around that little PITA for indexes, but for keeping dbs in sync that'd be another little project all in itself. I know I can use 2k8 change management to accomplish this but I've never played with it and I'm not sure it's allowed either.

Anyhow if I just run a job once a day I can easily go around that one at that time.



Wait... you have 30x1200 tables in that database right now? That's sick.

Seth Phelabaum
Consistency is only a virtue if you're not a screwup. ;-)

Links: How to Post Sample Data :: Running Totals :: Tally Table :: Cross Tabs/Pivots :: String Concatenation
Garadin
Garadin
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1523 Visits: 4107
Chris Morris-439714 (4/8/2010)
Ninja (Remi?)

Sympathies mate. How much leeway do you have with the db? Can you for instance sneak in a trigger here and there, and maybe a link table?

It's an interesting challenge as you and others have pointed out, so I'm fiddling with a recursive CTE to see if it can do anything useful.


The problem I kept running into with the recursive CTE is the 'missing in the middle' number. Sequences for combos up to 4 combos aren't a problem, once you hit 5 and can have to generate stuff like '02,05' '02,03,05' etc, it falls apart.

Although, in typing that I might have just thought of way to fix that problem. Keyword being "might" =).

Seth Phelabaum
Consistency is only a virtue if you're not a screwup. ;-)

Links: How to Post Sample Data :: Running Totals :: Tally Table :: Cross Tabs/Pivots :: String Concatenation
Matt Miller (#4)
Matt Miller (#4)
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7639 Visits: 18053
Not to take all of the fun out of tihs - but is there a need to match up the two children? As in - the order can have n shipments and m invoices - under what circumstance do you need to cross-reference a specific shipment to a specific invoice? As long as the shipment details add up to the total on the order and so do the invoices - what else is needed?

I just have a sneaking suspicion that the details might not exactly match, even if the totals tie in.

knowing the "why" might help with the "how".

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20931 Visits: 9671
Ninja's_RGR'us (4/8/2010)
Chris Morris-439714 (4/8/2010)
Ninja (Remi?)

Sympathies mate. How much leeway do you have with the db? Can you for instance sneak in a trigger here and there, and maybe a link table?

It's an interesting challenge as you and others have pointed out, so I'm fiddling with a recursive CTE to see if it can do anything useful.


I have None. I can either call the vendors to change it or create a db besides it and maintain the new field in the new db manually. Anything else voids the contract.

Now the fun part with a new db is that we have 30 incorporations. Currently 3 of them are in that ERP system whereas the rest of them will gradually make the transition.

The really fun part here is that there is only 1 single database. Everytime you add a new company to the system a new set of ±1200 tables is created like so :

dbo.[<name for the cie here>$Invoice Header]


So any script, index, mod or whatever needs to be applied to n number of companies, ideally without changing the scripts I wrote. I've already gotten around that little PITA for indexes, but for keeping dbs in sync that'd be another little project all in itself. I know I can use 2k8 change management to accomplish this but I've never played with it and I'm not sure it's allowed either.

Anyhow if I just run a job once a day I can easily go around that one at that time.



Well not at the moment, there are only 4 cies, 3 of them really the transactional ones. The 4th one is just there to increase the buying power and discounts. You also need to account for 1 test and 1 model cie in there too... which makes it already to around 7200 tables. The final total will be 5 times that in a couple years assuming the plans don't change.

When I hit the + in SSMS to expand all the tables I have plenty of time for a bathroom break!

In VS 2008 to build reports for asp net (not the bids version, but the full pro version), the wizard drills down to table defs, keys and column types for all tables right off the bat. That one takes a couple hours to load at the moment and generates a trace file of a couple 100 MBs with tracing only batch ending and rpc calls and nothing else... loads of fun.
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20931 Visits: 9671
Garadin (4/8/2010)
Chris Morris-439714 (4/8/2010)
Ninja (Remi?)

Sympathies mate. How much leeway do you have with the db? Can you for instance sneak in a trigger here and there, and maybe a link table?

It's an interesting challenge as you and others have pointed out, so I'm fiddling with a recursive CTE to see if it can do anything useful.


The problem I kept running into with the recursive CTE is the 'missing in the middle' number. Sequences for combos up to 4 combos aren't a problem, once you hit 5 and can have to generate stuff like '02,05' '02,03,05' etc, it falls apart.

Although, in typing that I might have just thought of way to fix that problem. Keyword being "might" =).



Be sure to let us know!
Garadin
Garadin
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1523 Visits: 4107
Ninja's_RGR'us (4/8/2010)
Garadin (4/8/2010)
Chris Morris-439714 (4/8/2010)
Ninja (Remi?)

Sympathies mate. How much leeway do you have with the db? Can you for instance sneak in a trigger here and there, and maybe a link table?

It's an interesting challenge as you and others have pointed out, so I'm fiddling with a recursive CTE to see if it can do anything useful.


The problem I kept running into with the recursive CTE is the 'missing in the middle' number. Sequences for combos up to 4 combos aren't a problem, once you hit 5 and can have to generate stuff like '02,05' '02,03,05' etc, it falls apart.

Although, in typing that I might have just thought of way to fix that problem. Keyword being "might" =).



Be sure to let us know!


Probably still a few hours work making it all come together. If I get a chance to mess with it again and get anything to work, I'll post it here. Let me know what you decide on compiling this into a T-SQL Challenge (for either simple talk or beyondrelational), as I'll probably submit something similar if you decide not to.

Seth Phelabaum
Consistency is only a virtue if you're not a screwup. ;-)

Links: How to Post Sample Data :: Running Totals :: Tally Table :: Cross Tabs/Pivots :: String Concatenation
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search