Grouping and crosstab like behaviour.... hairy query

Question

Grouping and crosstab like behaviour.... hairy query

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

ZA_Crafty Ten Centuries Points: 1228 More actions · Answer 1

Ok Sorted now

I excluded the rows that caused the error, and found the results still a bit big, so I aimed at compressing the data even more. I then decided to remove @Part completly which solved both problems at the same time. No More silly errors about characters not being found. Also the original Data was 55K rows. Your query compressed it to about 11K rows. By Removing @Part and the new column Part, This came to 513 rows in total. How is that for compressing data?:-D

Now I can build a function that will resolve a factor for each of rows in stock table.

David Burrows SSC Guru Points: 65143 More actions · Answer 2

My solution for what it's worth 😉

SELECTIDENTITY(INT,1,1) AS [RowNum],

PartNumber,

Factor

INTO#MyHead

FROMdbo.XlsDump

ORDER BY PartNumber, Factor

SELECT IDENTITY(INT,0,1) AS [GroupNumber],0 AS [RowNum]

INTO #MyHead2

INSERT #MyHead2 (RowNum)

SELECT a.RowNum

FROM #MyHead a

LEFT JOIN #MyHead n ON n.RowNum=a.RowNum+1

WHERE NOT (a.Factor=n.Factor)

OR n.RowNum IS NULL

ORDER BY a.RowNum ASC

SELECTa.Factor,

MIN(a.PartNumber) As [PartFrom],

MAX(a.PartNumber) As [PartTo]

FROM #MyHead2 h

JOIN #MyHead2 l ON l.GroupNumber=h.GroupNumber-1

JOIN #MyHead a ON a.RowNum BETWEEN l.RowNum+1 AND h.RowNum

GROUP BY a.Factor, h.GroupNumber

ORDER BY PartFrom

Far away is close at hand in the images of elsewhere.
Anon.

Jeff Moden SSC Guru Points: 1004419 More actions · Answer 3

ZA_Crafty (3/9/2011)
Ok Sorted now
I excluded the rows that caused the error, and found the results still a bit big, so I aimed at compressing the data even more. I then decided to remove @Part completly which solved both problems at the same time. No More silly errors about characters not being found. Also the original Data was 55K rows. Your query compressed it to about 11K rows. By Removing @Part and the new column Part, This came to 513 rows in total. How is that for compressing data?:-D
Now I can build a function that will resolve a factor for each of rows in stock table.

Apologies. I lost track of this one. Are you all set now?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ZA_Crafty Ten Centuries Points: 1228 More actions · Answer 4

Yes!

Thanks a mil for that!!!

I've got another cursor running on our live system with

"No other way"

But that is low priority, and I might turn addressing that issue into an article at some stage. Cursor only picks up 10 - 15 lines at a time, so there really is no impact except for the challenge of it 🙂

I just came out of a meeting with code changes and new modules that will keep me busy until end of May, so this will only happen after that 🙂

Jeff Moden SSC Guru Points: 1004419 More actions · Answer 5

Ok... thanks, ZA.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)