Need help with cte or self Join

Question

Post reply

Need help with cte or self Join

krishusavalia

SSCrazy

Points: 2385
More actions
September 1, 2011 at 11:54 am

#247366

I have a table, TableA
Id Rows
1 10
2 30
3 10
Now I need two more column in that,
Expected result:
Id Rows Start End
1 10 0 11
2 30 10 41
3 10 40 51
Is there any way that I can do it using cte or self join??

Viewing 6 posts - 1 through 5 (of 5 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

What would be the logic to populate those values?

Do you already have the columns defined?

Is the start value a running total?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

krishusavalia SSCrazy Points: 2385 More actions · Answer 2

No we don't have the start and end column.

I need to computed it on the fly and then insert into temp table with all 4 column.

From source ,only 2 column is there.

krishusavalia SSCrazy Points: 2385 More actions · Answer 3

sample Source table is

CREATE TABLE #TABLEA

(

Id INT,

RowNo Int

)

INSERT INTO #TABLEA

SELECT 1,20

UNION ALL

SELECT 2,40

UNION ALL

SELECT 3,10

SELECT * FROM #TABLEA

and I need to compute 2 more column

Expected Result:

Id RowNo start End

1 20 0 21

2 40 20 61

3 10 60 71

toddasd SSCertifiable Points: 6298 More actions · Answer 4

What would be the logic to populate those values?
Do you already have the columns defined?
Is the start value a running total?

That takes care of #2, but the important question being #1, still remains.

______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

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

toddasd (9/1/2011)
What would be the logic to populate those values?
Do you already have the columns defined?
Is the start value a running total?
That takes care of #2, but the important question being #1, still remains.

I think I understand the problem... The "End" column is really a "NextID" column. The first row is always 1... if you add ten rows to that, what would be the next available "id"? 11. Now, add 30 rows to that and the next available "id" is 41.

--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)
Intro to Tally Tables and Functions