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 «««23456»»»

Recursive cross join to get all available combinaisons Expand / Collapse
Author
Message
Posted Thursday, April 8, 2010 12:42 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
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" =).



Actually the problem is the reverse. Let's say you have 128 detail rows in the order. In each of the subsequent documents you'll ALWAYS have 128 detail rows. They will perfectly match for rowid and sku #. Where the game changes is the Qty field which will be 0 if nothing is shipped or invoiced.

The only way I can safely link the invoice to the shipment(s) is to add up all the Quantities of any # of shipments untill have have a perfect match for all rows on the invoice. Now to be batting 1000 I'd love to also match the line amount but that one varies from 1 document to the next so it can't be trusted for the time being.

For 99%+ of the case if I add up all the shipping documents the totals will match the order because in 99% of the case I have only 1 invoice per order. The fun really begins when I have more than 1 invoice.

Now let's not start talking about partial invoicing and invoices later credited because of errors.

Post #899900
Posted Thursday, April 8, 2010 12:44 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
Garadin (4/8/2010)
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.



I'd love to but like eveerybody else I'm really pressed for time. I could easily work 100 hours/week right now just to barely keep up with the current client demands I have... and I'm moving into a new house of top of that so let's say april is quite booked fo rme right now.
Post #899903
Posted Thursday, April 8, 2010 12:50 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:28 AM
Points: 1,519, Visits: 4,076
Ninja's_RGR'us (4/8/2010)
Garadin (4/8/2010)
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.



I'd love to but like eveerybody else I'm really pressed for time. I could easily work 100 hours/week right now just to barely keep up with the current client demands I have... and I'm moving into a new house of top of that so let's say april is quite booked fo rme right now.


I hear ya. I'd love nothing more than to tear back into this right now just to see if I can figure out a way to do it... I just don't have the time currently


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 #899910
Posted Thursday, April 8, 2010 11:05 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
I'm going to try to find a little time tonight to knock the handshake thing up in a CLR routine - it's a lot easier to write the m-from-n requirement there, and the processor-intensive nature of the task should make it suitable. It's probably going to be of no use to the Ninja - but I keep wondering about it



Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #900187
Posted Friday, April 9, 2010 4:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:48 AM
Points: 6,799, Visits: 14,012
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" =).


Here's a rCTE which works for up to 9 rows. Changing it to work with any number of rows wouldn't be too hard but would make it almost unreadable, and probably pig-slow too. Up to 99 rows would be easy.

;WITH sourcedata AS (SELECT CAST(9 AS INT) AS MaxRows)
, calculator AS (
SELECT
LastColumn = 1,
LastNumber = 1,
Store = CAST(',1' AS VARCHAR(20))
FROM sourcedata
UNION ALL
SELECT
LastColumn = CASE
WHEN lr.LastColumn = tr.MaxRows OR lr.LastNumber = tr.MaxRows THEN lr.LastColumn - 1
ELSE lr.LastColumn + 1 END,

LastNumber = CASE WHEN lr.LastNumber = tr.MaxRows
-- go back one column, fetch number, increment
THEN CAST(SUBSTRING(lr.Store, (lr.LastColumn - 1)*2, 1) AS INT) + 1
ELSE lr.LastNumber + 1 END,

Store = CASE
WHEN lr.LastNumber = tr.MaxRows
THEN CAST(
LEFT(lr.Store, (lr.LastColumn - 2)*2) + ',' + LTRIM(STR(
CAST(SUBSTRING(lr.Store, (lr.LastColumn - 1)*2, 1) AS INT) + 1
))
AS VARCHAR(20))
ELSE CAST(lr.Store + ',' + LTRIM(STR(lr.LastNumber + 1)) AS VARCHAR(20)) END

FROM calculator lr
INNER JOIN sourcedata tr
ON NOT (lr.LastColumn = 1 AND lr.LastNumber = tr.MaxRows)
) SELECT * FROM calculator OPTION (MAXRECURSION 0)

It probably doesn't have any use in context - sorry Ninja - but it's interesting as an example of the stuff you can do with rCTE's, such as generating rows as and when you feel like it, and performing calculations on "adjacent" rows.

Here's some results:

LastColumn  LastNumber  Store
----------- ----------- --------------------
1 1 ,1
2 2 ,1,2
3 3 ,1,2,3
4 4 ,1,2,3,4
5 5 ,1,2,3,4,5
6 6 ,1,2,3,4,5,6
7 7 ,1,2,3,4,5,6,7
8 8 ,1,2,3,4,5,6,7,8
9 9 ,1,2,3,4,5,6,7,8,9
8 9 ,1,2,3,4,5,6,7,9
7 8 ,1,2,3,4,5,6,8
8 9 ,1,2,3,4,5,6,8,9
7 9 ,1,2,3,4,5,6,9
6 7 ,1,2,3,4,5,7
7 8 ,1,2,3,4,5,7,8
8 9 ,1,2,3,4,5,7,8,9
7 9 ,1,2,3,4,5,7,9
6 8 ,1,2,3,4,5,8
7 9 ,1,2,3,4,5,8,9
6 9 ,1,2,3,4,5,9
5 6 ,1,2,3,4,6

Oh cr@p - Seth, let me know if you're still considering setting up a challenge and I'll edit this out.


“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 #900349
Posted Friday, April 9, 2010 7:13 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
Chris Morris-439714 (4/9/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" =).


Here's a rCTE which works for up to 9 rows. Changing it to work with any number of rows wouldn't be too hard but would make it almost unreadable, and probably pig-slow too. Up to 99 rows would be easy.

;WITH sourcedata AS (SELECT CAST(9 AS INT) AS MaxRows)
, calculator AS (
SELECT
LastColumn = 1,
LastNumber = 1,
Store = CAST(',1' AS VARCHAR(20))
FROM sourcedata
UNION ALL
SELECT
LastColumn = CASE
WHEN lr.LastColumn = tr.MaxRows OR lr.LastNumber = tr.MaxRows THEN lr.LastColumn - 1
ELSE lr.LastColumn + 1 END,

LastNumber = CASE WHEN lr.LastNumber = tr.MaxRows
-- go back one column, fetch number, increment
THEN CAST(SUBSTRING(lr.Store, (lr.LastColumn - 1)*2, 1) AS INT) + 1
ELSE lr.LastNumber + 1 END,

Store = CASE
WHEN lr.LastNumber = tr.MaxRows
THEN CAST(
LEFT(lr.Store, (lr.LastColumn - 2)*2) + ',' + LTRIM(STR(
CAST(SUBSTRING(lr.Store, (lr.LastColumn - 1)*2, 1) AS INT) + 1
))
AS VARCHAR(20))
ELSE CAST(lr.Store + ',' + LTRIM(STR(lr.LastNumber + 1)) AS VARCHAR(20)) END

FROM calculator lr
INNER JOIN sourcedata tr
ON NOT (lr.LastColumn = 1 AND lr.LastNumber = tr.MaxRows)
) SELECT * FROM calculator OPTION (MAXRECURSION 0)

It probably doesn't have any use in context - sorry Ninja - but it's interesting as an example of the stuff you can do with rCTE's, such as generating rows as and when you feel like it, and performing calculations on "adjacent" rows.

Here's some results:

LastColumn  LastNumber  Store
----------- ----------- --------------------
1 1 ,1
2 2 ,1,2
3 3 ,1,2,3
4 4 ,1,2,3,4
5 5 ,1,2,3,4,5
6 6 ,1,2,3,4,5,6
7 7 ,1,2,3,4,5,6,7
8 8 ,1,2,3,4,5,6,7,8
9 9 ,1,2,3,4,5,6,7,8,9
8 9 ,1,2,3,4,5,6,7,9
7 8 ,1,2,3,4,5,6,8
8 9 ,1,2,3,4,5,6,8,9
7 9 ,1,2,3,4,5,6,9
6 7 ,1,2,3,4,5,7
7 8 ,1,2,3,4,5,7,8
8 9 ,1,2,3,4,5,7,8,9
7 9 ,1,2,3,4,5,7,9
6 8 ,1,2,3,4,5,8
7 9 ,1,2,3,4,5,8,9
6 9 ,1,2,3,4,5,9
5 6 ,1,2,3,4,6

Oh cr@p - Seth, let me know if you're still considering setting up a challenge and I'll edit this out.



It's very interesting, can you adjust it to work with 16 rows as max # and add a couple comments... I'm not too sure of what's going on in there.

TIA.
Post #900478
Posted Friday, April 9, 2010 7:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:48 AM
Points: 6,799, Visits: 14,012
Hi Ninja

It would be better to change the string list from comma-delimited to fixed position, three character positions per item - that way, the upper limit would be 999 rows. You would still have the benefit of the ceiling figure in the feed row (9 in the example), and the string arithmetic would be far simpler than modifying the existing comma-delimited string to account for numbers of more than one digit.

Work is in progress, mate.


“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 #900503
Posted Friday, April 9, 2010 7:42 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
Chris Morris-439714 (4/9/2010)
Hi Ninja

It would be better to change the string list from comma-delimited to fixed position, three character positions per item - that way, the upper limit would be 999 rows. You would still have the benefit of the ceiling figure in the feed row (9 in the example), and the string arithmetic would be far simpler than modifying the existing comma-delimited string to account for numbers of more than one digit.

Work is in progress, mate.



Whatever you say... I'm really clueless when it comes to cte especially with recursion... and I have no time to read up on the subject at the moment.
Post #900509
Posted Friday, April 9, 2010 9:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:48 AM
Points: 6,799, Visits: 14,012
Ninja's_RGR'us (4/9/2010)
Chris Morris-439714 (4/9/2010)
Hi Ninja

It would be better to change the string list from comma-delimited to fixed position, three character positions per item - that way, the upper limit would be 999 rows. You would still have the benefit of the ceiling figure in the feed row (9 in the example), and the string arithmetic would be far simpler than modifying the existing comma-delimited string to account for numbers of more than one digit.

Work is in progress, mate.



Whatever you say... I'm really clueless when it comes to cte especially with recursion... and I have no time to read up on the subject at the moment.


No worries: -

;WITH calculator AS (
SELECT
ColumnNo = 1,
Number = 1,
Store = CAST(' 1' AS VARCHAR(3000))
UNION ALL
SELECT
ColumnNo = CASE WHEN lr.Number = tr.MaxRows
THEN lr.ColumnNo - 1
ELSE lr.ColumnNo + 1 END,
Number = CASE WHEN lr.Number = tr.MaxRows
-- go back one 3-char column position, increment number found there
THEN CAST(RIGHT(LEFT(lr.Store, (lr.ColumnNo - 1) * 3), 3) AS INT) + 1
ELSE lr.Number + 1 END,
Store = CASE WHEN lr.Number = tr.MaxRows
THEN CAST(
LEFT(lr.Store, (lr.ColumnNo - 2) * 3) -- < chop off last two 3-char column positions
+ RIGHT(' ' + LTRIM(STR( -- < add new Number
CAST(RIGHT(LEFT(lr.Store, (lr.ColumnNo - 1) * 3), 3) AS INT) + 1
)), 3)
AS VARCHAR(3000))
ELSE CAST(lr.Store + RIGHT(' ' + LTRIM(STR(lr.Number + 1)), 3) AS VARCHAR(3000)) END

FROM calculator lr
INNER JOIN (SELECT CAST(16 AS INT) AS MaxRows) tr -- < 16 is the number of shipping rows
ON NOT (lr.ColumnNo = 1 AND lr.Number = tr.MaxRows)
)
SELECT *
FROM calculator
--WHERE ColumnNo > 1 AND ColumnNo < 16 -- < 16 is the number of shipping rows
OPTION (MAXRECURSION 0)

-- Maxrows = 16: (65,535 row(s) affected) / 00:00:02
-- Maxrows = 20: (1,048,575 row(s) affected) / 00:00:46
-- Maxrows = 21: (2,097,151 row(s) affected) / 00:01:33
-- Maxrows = 22: (4,194,303 row(s) affected) / 00:03:08



“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 #900646
Posted Friday, April 9, 2010 9:30 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:28 AM
Points: 1,519, Visits: 4,076
Chris Morris-439714 (4/9/2010)

Oh cr@p - Seth, let me know if you're still considering setting up a challenge and I'll edit this out.


I am, but I know Jacob already has the ones for the next couple months created, so it's not a big deal. Between the wording, time delay etc. this post has a ridiculously small chance of being seen by most of the people in it.


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 #900650
« Prev Topic | Next Topic »

Add to briefcase «««23456»»»

Permissions Expand / Collapse