Trying to Get Rid of Cursor for Creating Group Number

Question

Trying to Get Rid of Cursor for Creating Group Number

Viewing 2 posts - 16 through 16 (of 16 total)

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

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 1

Chris Souchik - Thursday, April 5, 2018 6:44 AM
Jason,
That looks like the answer I'm looking for, but I get an error converting varbinary to numeric. I will see if I can work that out.
Eirikur,
The logic you describe is slightly off. The group number should change any time an 'S' is encountered.
Thanks to everyone, I think I will be able to figure it out now. I just hope it scales well.

There is no "VARBINAY" in my solution... It's a fixed width BINARY... In this case, it's a very important distinction.
It's based on Itzik Ben-Gan's solution to The Last non NULL Puzzle
Read through his solution to get a better idea of how and why it works.
In addition to making sure that you're using BINARY (not VARBINARY) you'll also want to make sure you're sizing them properly.
You'll notice that I converted the INT value to BINARY(4) because INTs are 4 bytes and the DECIMAL(9,1) to BINARY(5) because a DECIMAL(9,n) is 5 bytes. (use BOL to verify the correct values for a given data type)
Let me know if you have any additional questions.

Chris Souchik SSC-Addicted Points: 429 More actions · Answer 2

sgmunson - Thursday, April 5, 2018 6:46 AM
Chris Souchik - Thursday, April 5, 2018 6:44 AM
Jason,
That looks like the answer I'm looking for, but I get an error converting varbinary to numeric. I will see if I can work that out.
Eirikur,
The logic you describe is slightly off. The group number should change any time an 'S' is encountered.
Thanks to everyone, I think I will be able to figure it out now. I just hope it scales well.
Chris,
Did you see my solution?

Steve, saw your answer and I'm trying to use it with actual values instead of calculating by group and doing update. I've been working on other stuff today, so I haven't had a chance to play with the answers provided. I've been working with 2008R2 for so long, I forgot that over was added to aggregate functions(just upgraded to 2016 last month). I appreciate everyone's efforts in helping me out with this.