Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Complicated Query Requirement Expand / Collapse
Author
Message
Posted Sunday, December 16, 2012 5:34 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 17, 2012 1:49 AM
Points: 45, Visits: 138
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

***************************************************



Id Final Value Describtion Example
2 990 1 Value - 2 Value 1000-10
3 981 1 Value - 2 Value - 3 Value 1000-10- 9
4 976 1 Value - 2 Value - 3 Value - 4 value 1000-10-9-5














Post #1397035
Posted Sunday, December 16, 2012 7:00 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:22 PM
Points: 36,016, Visits: 30,308
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1397054
Posted Sunday, December 16, 2012 8:09 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 17, 2012 1:49 AM
Points: 45, Visits: 138
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
Post #1397060
Posted Sunday, December 16, 2012 8:45 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 17, 2012 1:49 AM
Points: 45, Visits: 138
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
Post #1397064
Posted Monday, December 17, 2012 12:04 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:52 AM
Points: 2,820, Visits: 3,917
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
Post #1397106
Posted Monday, December 17, 2012 12:36 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 19, 2013 4:30 AM
Points: 78, Visits: 195
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.
Post #1397111
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse