April 8, 2010 at 12:42 pm
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.
April 8, 2010 at 12:44 pm
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. 😀
April 8, 2010 at 12:50 pm
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 :ermm:
April 8, 2010 at 11:05 pm
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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 9, 2010 at 4:51 am
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.
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
April 9, 2010 at 7:13 am
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.
April 9, 2010 at 7:34 am
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.
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
April 9, 2010 at 7:42 am
Chris Morris-439714 (4/9/2010)
Hi NinjaIt 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.
April 9, 2010 at 9:25 am
Ninja's_RGR'us (4/9/2010)
Chris Morris-439714 (4/9/2010)
Hi NinjaIt 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
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
April 9, 2010 at 9:30 am
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.
April 9, 2010 at 9:32 am
Garadin (4/9/2010)
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.
Cool, thanks for the feedback Seth.
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
April 9, 2010 at 10:06 am
Chris Morris-439714 (4/9/2010)
Ninja's_RGR'us (4/9/2010)
Chris Morris-439714 (4/9/2010)
Hi NinjaIt 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
I'm not sure I'm using it right...
-- 16 : 65518
-- 20 : 1048554
DECLARE @MaxRows AS INT = 20
;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(@MaxRows 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 < @MaxRows -- < 16 is the number of shipping rows
April 9, 2010 at 10:10 am
Garadin (4/9/2010)
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.
I'd still like to post it someday but I'd post the full challenge of the real world scenario with a couple 10000s of documents (with obfuscated data of course).
Also adding in the 3 ways it needs to be used... in a list, in a single doc or an update batch and with all compagnies (and variable list of cies) at once :w00t:.
Ya and by the way recursive cross join on google didn't really help me much :hehe:.
April 9, 2010 at 10:13 am
Paul White NZ (4/8/2010)
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 😉
Any luck on the clr routine?
April 9, 2010 at 11:36 am
Ninja's_RGR'us (4/9/2010)
Garadin (4/9/2010)
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.
I'd still like to post it someday but I'd post the full challenge of the real world scenario with a couple 10000s of documents (with obfuscated data of course).
Also adding in the 3 ways it needs to be used... in a list, in a single doc or an update batch and with all compagnies (and variable list of cies) at once :w00t:.
Ya and by the way recursive cross join on google didn't really help me much :hehe:.
I'll just not go any farther with it if you still plan to use it some day, I have another one I was going to submit anyways, so it's no big deal either way.
I will say that I would not have submitted the whole thing as one challenge though. I thought about it, but I think there are several bars for these challenges. Stuff like:
Having an interesting challenge
Being able to properly explain the challenge and account for all variables
Level of T-SQL Knowledge to accomplish the challenge *at all*
Amount of time it takes to get a solution working
Amount of time it takes to optimize a solution
etc.
While I think it is extremely interesting, and you *might* be able to account for every possible variable, I think it goes overboard on all the rest. The level of knowledge required to pull it off at all is too high, the amount of time it'd take to put together an entire working solution is way too much to get many participants and optimizing a solution / trying different ones is almost out of the question due to the first two factors.
In my opinion, the challenges need to be something that can be done in a few hours, not a few days.
Asking someone to recreate bejeweled in SQL is one thing, asking them to recreate Chess with a computer opponent is quite another :hehe:
Viewing 15 posts - 31 through 45 (of 64 total)
You must be logged in to reply to this topic. Login to reply