InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 17 Jun 2013 23:14:16 GMT20http://www.sqlservercentral.com/Forums/Topic899004-392-1.aspxisn't that a somewhat heavy laptop ? :-DFri, 16 Apr 2010 13:39:02 GMTALZDBAhttp://www.sqlservercentral.com/Forums/Topic899004-392-1.aspx[quote][b]Paul White NZ (4/10/2010)[/b][hr][quote][b]Ninja's_RGR'us (4/10/2010)[/b][hr]Wow that's fast... under 3 secs on our prod server.[/quote]Now you have made my laptop feel inadequate :([/quote]Did I say prod?, I meant pre-prod :-P.No seriously it was the prod server, under full rush stress :w00t:.Fri, 16 Apr 2010 12:50:54 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic899004-392-1.aspx[quote][b]Ninja's_RGR'us (4/16/2010)[/b][hr]Thanks for the code. However it seems to be on the slower side of things. I takes about 47 secs to run with 20 while I have 1 solution than runs under 3 for the same 20 rows.Keep trying :hehe:.[/quote]Apparently I didn't refresh my browser... missed alot of the solutions. Glad my thought process was somewhat in the right direction though!:-DFri, 16 Apr 2010 12:31:39 GMTTimothy J Hartfordhttp://www.sqlservercentral.com/Forums/Topic899004-392-1.aspxThanks for the code. However it seems to be on the slower side of things. I takes about 47 secs to run with 20 while I have 1 solution than runs under 3 for the same 20 rows.Keep trying :hehe:.Fri, 16 Apr 2010 10:18:37 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic899004-392-1.aspxDid you expand the tally table? ; WITH E1(N) AS ( --=== Create Ten 1's very quickly SELECT N FROM (SELECT 1 N0, 1 N1, 1 N2, 1 N3, 1 N4, 1 N5, 1 N6, 1 N7, 1 N8, 1 N9) AS E0 UNPIVOT (N FOR Nx IN (N0, N1, N2, N3, N4, N5, N6, N7, N8, N9)) AS unpvt ), --10 E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100 E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000 E8(N) AS (SELECT 1 FROM E4 a, E4 b), --100,000,000cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E8)Fri, 16 Apr 2010 09:15:18 GMTTimothy J Hartfordhttp://www.sqlservercentral.com/Forums/Topic899004-392-1.aspxI tried with those values... and I got only 319 rows back... only 1M short of the correct answerDECLARE @People TABLE (Person VARCHAR(50))INSERT INTO @People VALUES ('0')INSERT INTO @People VALUES ('1')INSERT INTO @People VALUES ('2')INSERT INTO @People VALUES ('3')INSERT INTO @People VALUES ('4')INSERT INTO @People VALUES ('5')INSERT INTO @People VALUES ('6')INSERT INTO @People VALUES ('7')INSERT INTO @People VALUES ('8')INSERT INTO @People VALUES ('9')INSERT INTO @People VALUES ('10')INSERT INTO @People VALUES ('11')INSERT INTO @People VALUES ('12')INSERT INTO @People VALUES ('13')INSERT INTO @People VALUES ('14')INSERT INTO @People VALUES ('15')Fri, 16 Apr 2010 07:29:16 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic899004-392-1.aspxI may be way off... but this seems like a simple binary problem to me. [code="sql"]DECLARE @People TABLE (Person VARCHAR(50))INSERT INTO @People VALUES ('Ted')INSERT INTO @People VALUES ('Tom')INSERT INTO @People VALUES ('Mary')INSERT INTO @People VALUES ('Sue')--INSERT INTO @People VALUES ('Frank'); WITH --"Inline" CTE Driven "Tally Table" produces values up to 10K... enough to cover VARCHAR(8000). E1(N) AS ( --=== Create Ten 1's very quickly SELECT N FROM (SELECT 1 N0, 1 N1, 1 N2, 1 N3, 1 N4, 1 N5, 1 N6, 1 N7, 1 N8, 1 N9) AS E0 UNPIVOT (N FOR Nx IN (N0, N1, N2, N3, N4, N5, N6, N7, N8, N9)) AS unpvt ), --10 E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100 --E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E2)SELECT N, PersonFROM cteTally JOIN (SELECT Person, ROW_NUMBER() OVER(ORDER BY Person) R, POWER(2,ROW_NUMBER() OVER(ORDER BY Person)-1) B /* Bitflag - I am sure there is a better way to do this */ FROM @People) P ON P.B & N = P.BWHERE N <= POWER(2,(SELECT COUNT(*) FROM @People))ORDER BY N[/code]And then you have your list. All you would have to do is at your count filter or whatever you needed to do with this data set.Hope it helps!(p.s. Just saw a post from Jeff Moden on the tally CTE... had to give it a try... thanks Jeff! :) )Fri, 16 Apr 2010 06:49:42 GMTTimothy J Hartfordhttp://www.sqlservercentral.com/Forums/Topic899004-392-1.aspxAlways nice to get a happy ending :-)Wed, 14 Apr 2010 16:33:21 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic899004-392-1.aspxThanks a million to all who've helped on this problem.I've had a very happy conclusion yesterday. After being told but the erp cie that it was impossible to add the field we needed and hence have the correct design, I've found out about 2 other ledgers in the system and if I cross both of them I can get the actual document # for each line of the invoices and shippings. That means that I can simply do a distinct in a derived table + concatenation which gives the results well under 1 sec for even 10 000 documents.Maybe there's a God after all :w00t:.Wed, 14 Apr 2010 08:46:22 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic899004-392-1.aspxNice examples.IMO for a very temporary solution it still may be tolerable ... to use a hybrid solution [code="sql"]CREATE function ufn_GetOrderDetailsCombinations ( @PurchaseOrderID int )returns tableas return ( /*Select * from ufn_GetOrderDetailsCombinations ( 167 )-- MUST USE MAXRECURSION IN QUERY !OPTION (MAXRECURSION 0)*/ with cteOrderDetailSeq as ( --Minimize the data scope as soon as possible ! SELECT POD.PurchaseOrderID , convert(varchar(5000), POD.PurchaseOrderDetailID) + ' (' + convert(char(10), POH.ShipDate, 121) + ')' as OrderDetailInfo , rank() over ( partition by POD.PurchaseOrderID order by POD.PurchaseOrderID, POD.PurchaseOrderDetailID ) as SEQNR FROM [b] [AdventureWorks][/b].[Purchasing].[PurchaseOrderHeader] POH INNER JOIN [b][AdventureWorks][/b].[Purchasing].[PurchaseOrderDetail] POD on POD.PurchaseOrderID = POH.PurchaseOrderID Where POH.PurchaseOrderID = @PurchaseOrderID ) , cteMaxMembers as ( Select count(*) as MaxMembers from cteOrderDetailSeq ) , cteCompositions AS ( -- Chris Morris-439714 calculator cte 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 cteCompositions lr INNER JOIN ( Select MaxMembers as MaxRows from cteMaxMembers ) tr ON NOT ( lr.ColumnNo = 1 AND lr.Number = tr.MaxRows ) ) , cteCompose as ( Select ODS.PurchaseOrderID , ODS.OrderDetailInfo , C.Store from cteCompositions C /* replace spaces with split character */ cross apply master.dbo.fn_ALZDBA_Split(replace(replace(C.Store, ' ', ';'),' ',';'), ';') S inner join cteOrderDetailSeq ODS on ODS.SEQNR = S.item ) Select C.PurchaseOrderID , STUFF(( SELECT COALESCE(',' + S1.OrderDetailInfo, '') FROM cteCompose S1 WHERE S1.PurchaseOrderID = C.PurchaseOrderID and S1.Store = C.Store FOR XML PATH('') ), 1, 1, '') as OrderDetails from cteCompose C group by C.PurchaseOrderID , C.Store ) ; go Select * from ufn_GetOrderDetailsCombinations ( 167 )OPTION (MAXRECURSION 0)go/*(63 row(s) affected) CPU time = 172 ms, elapsed time = 409 ms.*/Select * from ufn_GetOrderDetailsCombinations ( 455 )OPTION (MAXRECURSION 0)go/*(1023 row(s) affected) CPU time = 4766 ms, elapsed time = 8744 ms.*//*PurchaseOrderID nDetails nCombinations1015 30 1073741823135 26 671088631255 24 16777215515 20 1048575685 19 524287765 17 131071835 16 655351305 15 327672045 14 163831905 13 8191325 12 4095645 11 2047455 10 1023185 9 511295 8 25527 6 638 5 3111 4 157 3 72 2 31 1 1*/[/code]Although an enduser may not be very happy if (s)he has to pick one out of 1023 ;-)Wed, 14 Apr 2010 07:27:21 GMTALZDBAhttp://www.sqlservercentral.com/Forums/Topic899004-392-1.aspx[quote][b]Paul White NZ (4/12/2010)[/b][hr][quote][b]Chris Morris-439714 (4/12/2010)[/b][hr]Heh in any case, it's a little sluggish...[/quote]But full of recursive awesomeness!!!Great code.[/quote]But so slow! I've got a little library of rCTE's now for a variety of jobs, but they're mostly code curiosities which have quicker equivalents using more conventional TSQL - no opportunity yet to say "the fastest solution for this is a rCTE".Mon, 12 Apr 2010 04:05:47 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic899004-392-1.aspx[quote][b]Chris Morris-439714 (4/12/2010)[/b][hr]Heh in any case, it's a little sluggish...[/quote]But full of recursive awesomeness!!!Great code.Mon, 12 Apr 2010 03:13:55 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic899004-392-1.aspx[quote][b]Ninja's_RGR'us (4/9/2010)[/b][hr]-- 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:08I'm not sure I'm using it right...-- 16 : 65518-- 20 : 1048554[/quote]That's the WHERE clause, which filters out the single row in the result set which contains all values plus other rows which contain only a single value.Maxrows = 16: (65,535 row(s) affected) [i]without[/i] the WHERE clause16 : 65518 [i]with[/i] the WHERE clause filtering out 16+1 rowsHeh in any case, it's a little sluggish compared to Paul's code ;-) Mon, 12 Apr 2010 03:02:42 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic899004-392-1.aspxDBCC TimeWarp would be so usefull for this one :-D.Sun, 11 Apr 2010 06:34:56 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic899004-392-1.aspx[quote][b]Ninja's_RGR'us (4/10/2010)[/b][hr]Wow that's fast... under 3 secs on our prod server.[/quote]Now you have made my laptop feel inadequate :(Sat, 10 Apr 2010 21:15:05 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic899004-392-1.aspx[quote][b]Ninja's_RGR'us (4/10/2010)[/b][hr]Wow that's fast... under 3 secs on our prod server.Only 3 more secs to shave off boys ! ;-)[/quote]If my math skills are anywhere near where I think they are, you now want the routine to go back in time? DBCC TimeWarp?Sat, 10 Apr 2010 17:52:03 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic899004-392-1.aspx[quote][b]Paul White NZ (4/10/2010)[/b][hr]While revisiting the Numbers table solution, I discovered that the following code creates a table from 20 names in 6 seconds on my old laptop:[code="sql"]USE tempdb;GOIF OBJECT_ID(N'temp..#t', N'U') IS NOT NULL DROP TABLE #t;GOSELECT N, list = ( CASE WHEN N & 1 = 1 THEN 'Alice,' ELSE '' END + CASE WHEN N & 2 = 2 THEN 'Bob,' ELSE '' END + CASE WHEN N & 4 = 4 THEN 'Carol,' ELSE '' END + CASE WHEN N & 8 = 8 THEN 'Dan,' ELSE '' END + CASE WHEN N & 16 = 16 THEN 'Eric,' ELSE '' END + CASE WHEN N & 32 = 32 THEN 'Frank,' ELSE '' END + CASE WHEN N & 64 = 64 THEN 'George,' ELSE '' END + CASE WHEN N & 128 = 128 THEN 'Harry,' ELSE '' END + CASE WHEN N & 256 = 256 THEN 'Ian,' ELSE '' END + CASE WHEN N & 512 = 512 THEN 'John,' ELSE '' END + CASE WHEN N & 1024 = 1024 THEN 'Kevin,' ELSE '' END + CASE WHEN N & 2048 = 2048 THEN 'Lisa,' ELSE '' END + CASE WHEN N & 4096 = 4096 THEN 'Mandy,' ELSE '' END + CASE WHEN N & 8192 = 8192 THEN 'Norman,' ELSE '' END + CASE WHEN N & 16384 = 16384 THEN 'Oscar,' ELSE '' END + CASE WHEN N & 32768 = 32768 THEN 'Paul,' ELSE '' END + CASE WHEN N & 65536 = 65536 THEN 'Quincy,' ELSE '' END + CASE WHEN N & 131072 = 131072 THEN 'Roger,' ELSE '' END + CASE WHEN N & 262144 = 262144 THEN 'Sam,' ELSE '' END + CASE WHEN N & 524288 = 524288 THEN 'Terri,' ELSE '' END )INTO #tFROM dbo.Numbers(POWER(2, 20) - 1);[/code][/quote]Wow that's fast... under 3 secs on our prod server.Only 3 more secs to shave off boys ! ;-)Sat, 10 Apr 2010 13:00:33 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic899004-392-1.aspxWhile revisiting the Numbers table solution, I discovered that the following code creates a table from 20 names in 6 seconds on my old laptop:[code="sql"]USE tempdb;GOIF OBJECT_ID(N'temp..#t', N'U') IS NOT NULL DROP TABLE #t;GOSELECT N, list = ( CASE WHEN N & 1 = 1 THEN 'Alice,' ELSE '' END + CASE WHEN N & 2 = 2 THEN 'Bob,' ELSE '' END + CASE WHEN N & 4 = 4 THEN 'Carol,' ELSE '' END + CASE WHEN N & 8 = 8 THEN 'Dan,' ELSE '' END + CASE WHEN N & 16 = 16 THEN 'Eric,' ELSE '' END + CASE WHEN N & 32 = 32 THEN 'Frank,' ELSE '' END + CASE WHEN N & 64 = 64 THEN 'George,' ELSE '' END + CASE WHEN N & 128 = 128 THEN 'Harry,' ELSE '' END + CASE WHEN N & 256 = 256 THEN 'Ian,' ELSE '' END + CASE WHEN N & 512 = 512 THEN 'John,' ELSE '' END + CASE WHEN N & 1024 = 1024 THEN 'Kevin,' ELSE '' END + CASE WHEN N & 2048 = 2048 THEN 'Lisa,' ELSE '' END + CASE WHEN N & 4096 = 4096 THEN 'Mandy,' ELSE '' END + CASE WHEN N & 8192 = 8192 THEN 'Norman,' ELSE '' END + CASE WHEN N & 16384 = 16384 THEN 'Oscar,' ELSE '' END + CASE WHEN N & 32768 = 32768 THEN 'Paul,' ELSE '' END + CASE WHEN N & 65536 = 65536 THEN 'Quincy,' ELSE '' END + CASE WHEN N & 131072 = 131072 THEN 'Roger,' ELSE '' END + CASE WHEN N & 262144 = 262144 THEN 'Sam,' ELSE '' END + CASE WHEN N & 524288 = 524288 THEN 'Terri,' ELSE '' END )INTO #tFROM dbo.Numbers(POWER(2, 20) - 1);[/code]Sat, 10 Apr 2010 07:54:25 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic899004-392-1.aspx[quote][b]Paul White NZ (4/9/2010)[/b][hr][quote][b]Ninja's_RGR'us (4/9/2010)[/b][hr]Any luck on the clr routine?[/quote]Been busy on another thread - writing a SQLCLR routine to return the phase of the moon for a given date :w00t:Blame Lowell. I'll get to this soon.[/quote]No hurries, I was just curious...Sat, 10 Apr 2010 06:19:46 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic899004-392-1.aspx[quote][b]Ninja's_RGR'us (4/9/2010)[/b][hr]Any luck on the clr routine?[/quote]Been busy on another thread - writing a SQLCLR routine to return the phase of the moon for a given date :w00t:Blame Lowell. I'll get to this soon.Fri, 09 Apr 2010 21:54:07 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic899004-392-1.aspx[quote][b]Ninja's_RGR'us (4/9/2010)[/b][hr][quote][b]Garadin (4/9/2010)[/b][hr][quote][b]Chris Morris-439714 (4/9/2010)[/b][hr]Oh cr@p - Seth, let me know if you're still considering setting up a challenge and I'll edit this out.[/quote]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.[/quote]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:.[/quote]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 challengeBeing able to properly explain the challenge and account for all variablesLevel of T-SQL Knowledge to accomplish the challenge *at all*Amount of time it takes to get a solution workingAmount of time it takes to optimize a solutionetc.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:Fri, 09 Apr 2010 11:36:22 GMTGaradinhttp://www.sqlservercentral.com/Forums/Topic899004-392-1.aspx[quote][b]Paul White NZ (4/8/2010)[/b][hr]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 ;-)[/quote]Any luck on the clr routine?Fri, 09 Apr 2010 10:13:30 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic899004-392-1.aspx[quote][b]Garadin (4/9/2010)[/b][hr][quote][b]Chris Morris-439714 (4/9/2010)[/b][hr]Oh cr@p - Seth, let me know if you're still considering setting up a challenge and I'll edit this out.[/quote]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.[/quote]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:.Fri, 09 Apr 2010 10:10:29 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic899004-392-1.aspx[quote][b]Chris Morris-439714 (4/9/2010)[/b][hr][quote][b]Ninja's_RGR'us (4/9/2010)[/b][hr][quote][b]Chris Morris-439714 (4/9/2010)[/b][hr]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.[/quote]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.[/quote]No worries: -[code="sql"];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[/code][/quote]I'm not sure I'm using it right...-- 16 : 65518-- 20 : 1048554[code]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 [/code]Fri, 09 Apr 2010 10:06:14 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic899004-392-1.aspx[quote][b]Garadin (4/9/2010)[/b][hr][quote][b]Chris Morris-439714 (4/9/2010)[/b][hr]Oh cr@p - Seth, let me know if you're still considering setting up a challenge and I'll edit this out.[/quote]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.[/quote]Cool, thanks for the feedback Seth.Fri, 09 Apr 2010 09:32:58 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic899004-392-1.aspx[quote][b]Chris Morris-439714 (4/9/2010)[/b][hr]Oh cr@p - Seth, let me know if you're still considering setting up a challenge and I'll edit this out.[/quote]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.Fri, 09 Apr 2010 09:30:56 GMTGaradinhttp://www.sqlservercentral.com/Forums/Topic899004-392-1.aspx[quote][b]Ninja's_RGR'us (4/9/2010)[/b][hr][quote][b]Chris Morris-439714 (4/9/2010)[/b][hr]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.[/quote]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.[/quote]No worries: -[code="sql"];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[/code]Fri, 09 Apr 2010 09:25:57 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic899004-392-1.aspx[quote][b]Chris Morris-439714 (4/9/2010)[/b][hr]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.[/quote]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.Fri, 09 Apr 2010 07:42:11 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic899004-392-1.aspxHi 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.Fri, 09 Apr 2010 07:34:43 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic899004-392-1.aspx[quote][b]Chris Morris-439714 (4/9/2010)[/b][hr][quote][b]Garadin (4/8/2010)[/b][hr][quote][b]Chris Morris-439714 (4/8/2010)[/b][hr]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.[/quote]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" =).[/quote]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.[code="sql"];WITH sourcedata AS (SELECT CAST(9 AS INT) AS MaxRows), calculator AS (SELECT LastColumn = 1, LastNumber = 1, Store = CAST(',1' AS VARCHAR(20))FROM sourcedataUNION 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 lrINNER JOIN sourcedata tr ON NOT (lr.LastColumn = 1 AND lr.LastNumber = tr.MaxRows) ) SELECT * FROM calculator OPTION (MAXRECURSION 0)[/code]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:[code="sql"]LastColumn LastNumber Store----------- ----------- --------------------1 1 ,12 2 ,1,23 3 ,1,2,34 4 ,1,2,3,45 5 ,1,2,3,4,56 6 ,1,2,3,4,5,67 7 ,1,2,3,4,5,6,78 8 ,1,2,3,4,5,6,7,89 9 ,1,2,3,4,5,6,7,8,98 9 ,1,2,3,4,5,6,7,97 8 ,1,2,3,4,5,6,88 9 ,1,2,3,4,5,6,8,97 9 ,1,2,3,4,5,6,96 7 ,1,2,3,4,5,77 8 ,1,2,3,4,5,7,88 9 ,1,2,3,4,5,7,8,97 9 ,1,2,3,4,5,7,96 8 ,1,2,3,4,5,87 9 ,1,2,3,4,5,8,96 9 ,1,2,3,4,5,95 6 ,1,2,3,4,6[/code]Oh cr@p - Seth, let me know if you're still considering setting up a challenge and I'll edit this out.[/quote]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.Fri, 09 Apr 2010 07:13:03 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic899004-392-1.aspx[quote][b]Garadin (4/8/2010)[/b][hr][quote][b]Chris Morris-439714 (4/8/2010)[/b][hr]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.[/quote]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" =).[/quote]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.[code="sql"];WITH sourcedata AS (SELECT CAST(9 AS INT) AS MaxRows), calculator AS (SELECT LastColumn = 1, LastNumber = 1, Store = CAST(',1' AS VARCHAR(20))FROM sourcedataUNION 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 lrINNER JOIN sourcedata tr ON NOT (lr.LastColumn = 1 AND lr.LastNumber = tr.MaxRows) ) SELECT * FROM calculator OPTION (MAXRECURSION 0)[/code]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:[code="sql"]LastColumn LastNumber Store----------- ----------- --------------------1 1 ,12 2 ,1,23 3 ,1,2,34 4 ,1,2,3,45 5 ,1,2,3,4,56 6 ,1,2,3,4,5,67 7 ,1,2,3,4,5,6,78 8 ,1,2,3,4,5,6,7,89 9 ,1,2,3,4,5,6,7,8,98 9 ,1,2,3,4,5,6,7,97 8 ,1,2,3,4,5,6,88 9 ,1,2,3,4,5,6,8,97 9 ,1,2,3,4,5,6,96 7 ,1,2,3,4,5,77 8 ,1,2,3,4,5,7,88 9 ,1,2,3,4,5,7,8,97 9 ,1,2,3,4,5,7,96 8 ,1,2,3,4,5,87 9 ,1,2,3,4,5,8,96 9 ,1,2,3,4,5,95 6 ,1,2,3,4,6[/code]Oh cr@p - Seth, let me know if you're still considering setting up a challenge and I'll edit this out.Fri, 09 Apr 2010 04:51:12 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic899004-392-1.aspxI'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 ;-)Thu, 08 Apr 2010 23:05:01 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic899004-392-1.aspx[quote][b]Ninja's_RGR'us (4/8/2010)[/b][hr][quote][b]Garadin (4/8/2010)[/b][hr][quote][b]Ninja's_RGR'us (4/8/2010)[/b][hr][quote][b]Garadin (4/8/2010)[/b][hr][quote][b]Chris Morris-439714 (4/8/2010)[/b][hr]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.[/quote]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" =).[/quote]Be sure to let us know![/quote]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.[/quote]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. :-D[/quote]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:Thu, 08 Apr 2010 12:50:12 GMTGaradinhttp://www.sqlservercentral.com/Forums/Topic899004-392-1.aspx[quote][b]Garadin (4/8/2010)[/b][hr][quote][b]Ninja's_RGR'us (4/8/2010)[/b][hr][quote][b]Garadin (4/8/2010)[/b][hr][quote][b]Chris Morris-439714 (4/8/2010)[/b][hr]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.[/quote]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" =).[/quote]Be sure to let us know![/quote]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.[/quote]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. :-DThu, 08 Apr 2010 12:44:42 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic899004-392-1.aspx[quote][b]Garadin (4/8/2010)[/b][hr][quote][b]Chris Morris-439714 (4/8/2010)[/b][hr]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.[/quote]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" =).[/quote]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.Thu, 08 Apr 2010 12:42:35 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic899004-392-1.aspx[quote][b]Ninja's_RGR'us (4/8/2010)[/b][hr][quote][b]Garadin (4/8/2010)[/b][hr][quote][b]Chris Morris-439714 (4/8/2010)[/b][hr]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.[/quote]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" =).[/quote]Be sure to let us know![/quote]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.Thu, 08 Apr 2010 12:28:17 GMTGaradinhttp://www.sqlservercentral.com/Forums/Topic899004-392-1.aspx[quote][b]Garadin (4/8/2010)[/b][hr][quote][b]Chris Morris-439714 (4/8/2010)[/b][hr]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.[/quote]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" =).[/quote]Be sure to let us know!Thu, 08 Apr 2010 12:16:24 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic899004-392-1.aspx[quote][b]Ninja's_RGR'us (4/8/2010)[/b][hr][quote][b]Chris Morris-439714 (4/8/2010)[/b][hr]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.[/quote]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.[/quote]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.Thu, 08 Apr 2010 12:15:34 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic899004-392-1.aspxNot 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".Thu, 08 Apr 2010 12:09:47 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic899004-392-1.aspx[quote][b]Chris Morris-439714 (4/8/2010)[/b][hr]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.[/quote]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" =).Thu, 08 Apr 2010 11:42:17 GMTGaradin