Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»»

Recursive cross join to get all available combinaisons Expand / Collapse
Author
Message
Posted Thursday, April 08, 2010 9:39 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:33 AM
Points: 6,754, Visits: 12,854
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
Post #899706
Posted Thursday, April 08, 2010 10:07 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, January 16, 2014 12:03 PM
Points: 21,376, Visits: 9,594
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 ... 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.
Post #899741
Posted Thursday, April 08, 2010 10:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:33 AM
Points: 6,754, Visits: 12,854
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
Post #899752
Posted Thursday, April 08, 2010 10:33 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, January 16, 2014 12:03 PM
Points: 21,376, Visits: 9,594
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.
Post #899778
Posted Thursday, April 08, 2010 11:37 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, April 04, 2014 9:22 PM
Points: 1,525, Visits: 4,070
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
Post #899841
Posted Thursday, April 08, 2010 11:42 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, April 04, 2014 9:22 PM
Points: 1,525, Visits: 4,070
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
Post #899846
Posted Thursday, April 08, 2010 12:09 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:56 PM
Points: 7,084, Visits: 14,683
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?
Post #899868
Posted Thursday, April 08, 2010 12:15 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, January 16, 2014 12:03 PM
Points: 21,376, Visits: 9,594
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.
Post #899874
Posted Thursday, April 08, 2010 12:16 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, January 16, 2014 12:03 PM
Points: 21,376, Visits: 9,594
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!
Post #899875
Posted Thursday, April 08, 2010 12:28 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, April 04, 2014 9:22 PM
Points: 1,525, Visits: 4,070
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
Post #899883
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse