Assign seqnumber to a column

Question

Assign seqnumber to a column

Venkata-433090

Old Hand

Points: 336
More actions
June 17, 2008 at 11:43 am

#93324

Here's case I've to handle a scenario in an ETL procedure:
The input table will be look like:
sno prodid row_count
---- ------- ----------
1 p1 20
2 p2 25
3 p3 250
4 p4 400
5 p5 100
I want the output like:
sno prodid row_count seqnum
---- ------- ---------- --------
1 p1 20 1
2 p2 25 1
3 p3 250 1
4 p4 400 2
5 p5 100 2
In theory, all products that have cumulative row count <= 500 should be bundled under one sequence number. could someone suggest how to write the SQL?
thanks
M:

Viewing 8 posts - 1 through 7 (of 7 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

I'm not sure I'm following your requirements correctly, but it looks like something that could be done with a modified running total query. There's an article about how to do those in the Articles section of this site.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Venkata-433090 Old Hand Points: 336 More actions · Answer 2

thanks for responding. yes, it can be done thru a running total query. But, stuck on how to update a record when the counter reaches/near 500.

thx

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 3

Is it up to 500 and the next row would through it over?

Does this have to happen regularly, like with every import?

This sounds like something I might tackle in an SSIS package, holding the insert until I'd read the next row. I can think of RBAR/cursor ways of doing this, but not necessarily anything directly in SQL I'd write. Perhaps Jeff Moden or one of the experts will tackle this.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

Sounds to me like a straight application of a running total... It is just that you keep the running total in the variable, and update some different column.

Take a look at this article - will help describe the technique....

http://www.sqlservercentral.com/articles/Advanced+Querying/61716/[/url]

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

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

Matt Miller (6/17/2008)
Take a look at this article - will help describe the technique....

Man... you're right... you have been recommending this and the Tally table articles a lot! I'm humbled by your attentions. Thanks, Matt.

--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)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

Jeff Moden (6/17/2008)
Matt Miller (6/17/2008)
Take a look at this article - will help describe the technique....
Man... you're right... you have been recommending this and the Tally table articles a lot! I'm humbled by your attentions. Thanks, Matt.

Hey - when you get a new bench saw, you spend a lot of time in the garage don't you?

I don't need to tell you, but that darned thing can be used for a LOT of things. That latest article on it from you has most of the big examples covered in one shot, so it was an obvious choice to the old Briefcase..... It kind of hits all of the key points, so why not recommend it...

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Venkata-433090 Old Hand Points: 336 More actions · Answer 7

Venkata-433090

Old Hand

Points: 336

June 18, 2008 at 12:34 pm

#830693

Thanks guys. will try and keep u posted.