Complicated Query Requirement

Question

Post reply

Complicated Query Requirement

Subbu S

Old Hand

Points: 389
More actions
December 16, 2012 at 5:34 pm

#98742

Hi All,
I want the following manipulation without using and loop r cursors, i tried with CTE, but i didnt get the output.
***************************************************
Select 1 [Id],1000[value] into TempA
Select 2[Id], 10 Value, 1 parentid into TempB
Insert into TempB
Select 3,9,1
union all
select 4,5,1
***************************************************
IdFinal ValueDescribtion Example
29901 Value - 2 Value 1000-10
39811 Value - 2 Value - 3 Value 1000-10- 9
49761 Value - 2 Value - 3 Value - 4 value 1000-10-9-5

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

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

Subbu S (12/16/2012)
i tried with CTE, but i didnt get the output

Can we see it, please?

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

Subbu S Old Hand Points: 389 More actions · Answer 2

WITH CTE_BSI(ID, VAL)

AS

(

SELECT ID, VALUE FROM TempA AS A

UNION ALL

SELECT t.ID,(c.val - value) FROM TempB T join CTE_BSI C on T.parent = c.Id

)

SELECT ID, VAL FROM CTE_BSI

Subbu S Old Hand Points: 389 More actions · Answer 3

Finally i found the solution, here u go,

Select 1 [Id],1000[value] into TempA

Select 2[Id], 10 value, 1 parent into TempB

Insert into TempB

Select 3,9,1

union all

select 4,5,1

Select * From TempA;

Select * From TempB;

Select ROW_NUMBER() Over (Partition By Parent Order By B.Id) Rnum,B.*,A.value [PVal] into tempc From tempb B

join tempa A on a.id = b.parent

Select * From TempC;

Select *, PVal - (Select SUM(value) From tempc E Where E.parent = D.Parent AND E.Rnum <= D.Rnum) From tempc D

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

gad you found the solution yourself but in future always try to post table defintion , query you tried (though its incorrect) along with test data so that people can easliy jump into your prblm 🙂

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

rhythm.varshney Ten Centuries Points: 1138 More actions · Answer 5

Subbu S (12/16/2012)
Finally i found the solution, here u go,
Select 1 [Id],1000[value] into TempA
Select 2[Id], 10 value, 1 parent into TempB
Insert into TempB
Select 3,9,1
union all
select 4,5,1
Select * From TempA;
Select * From TempB;
Select ROW_NUMBER() Over (Partition By Parent Order By B.Id) Rnum,B.*,A.value [PVal] into tempc From tempb B
join tempa A on a.id = b.parent
Select * From TempC;
Select *, PVal - (Select SUM(value) From tempc E Where E.parent = D.Parent AND E.Rnum <= D.Rnum) From tempc D

As you mentioned in your original post that you don't want to use Loop or Cursor.But in your posted query also the inner query will execute repeatedly for outer query.Is not it ?

Then what is the gain you had.