SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Positive in sql server
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Nil35
Starting Member

USA
20 Posts

Posted - 07/24/2013 :  19:15:06  Show Profile  Reply with Quote
Here is recursive CTE i need something that B.AB should not negative
if you do this in Excel formula is =+A1-B2

;WITH
CTE1 AS
(
SELECT A.ID,cast(A.AB as FLOAT)
as AB FROM #TEST A
WHERE ID = 1
UNION ALL
SELECT A.id, B.AB - A.XYZ from #TEST a
INNER JOIN CTE1 b on a.ID = b.ID +1
)
select * from CTE1



Thanks In advance

nil

Edited by - Nil35 on 07/24/2013 19:32:17

MuMu88
Aged Yak Warrior

547 Posts

Posted - 07/24/2013 :  20:06:23  Show Profile  Reply with Quote
Is this what you are looking for:


;WITH
CTE1 AS
(
SELECT A.ID,cast(A.AB as FLOAT)
 as AB FROM #TEST A
WHERE ID = 1
UNION ALL
SELECT A.id, B.AB - A.XYZ from #TEST a
INNER JOIN CTE1 b on a.ID = b.ID +1
WHERE (B.AB - A.XYZ) >= 0
)
select * from CTE1


Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 07/24/2013 :  22:20:38  Show Profile  Reply with Quote
perhaps it will be clearer if you can post some sample data and the expected result


KH
Time is always against us

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/25/2013 :  04:14:45  Show Profile  Reply with Quote
why do you need a cTE for this? you're not doing any cumulative sum as i can see.
wont this be enough?


SELECT A.id, B.AB - A.XYZ from #TEST a
INNER JOIN #TEST b on a.ID = b.ID +1


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Nil35
Starting Member

USA
20 Posts

Posted - 07/25/2013 :  10:45:15  Show Profile  Reply with Quote
sorry about incomplete scenario
I Need CTE because I am taking 1 row with some calculation and doing cumulative sum its recursive CTE.
and while doing cumulative sum I dont want any negative number
if you see excle formula =+A1-B2 (here A1 is always Positive)


;WITH
CTE1 AS
(
SELECT A.ID,cast(A.DK as FLOAT)
as AB FROM #TEST A
WHERE ID = 1
UNION ALL
SELECT A.id, B.AB - A.XYZ from #TEST a
INNER JOIN CTE1 b on a.ID = b.ID +1
)
select * from CTE1



nil
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 07/25/2013 :  10:55:37  Show Profile  Reply with Quote
i am still don't quite understand what are you trying to achieve.

Please post some sample data and expected result.


KH
Time is always against us

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 07/25/2013 :  10:58:51  Show Profile  Reply with Quote
or is this what you want ?
SELECT	SUM(CASE WHEN ID = 1 THEN DK ELSE -XYZ END)
FROM	#TEST



KH
Time is always against us

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/26/2013 :  00:13:08  Show Profile  Reply with Quote
quote:
Originally posted by Nil35

sorry about incomplete scenario
I Need CTE because I am taking 1 row with some calculation and doing cumulative sum its recursive CTE.
and while doing cumulative sum I dont want any negative number
if you see excle formula =+A1-B2 (here A1 is always Positive)


;WITH
CTE1 AS
(
SELECT A.ID,cast(A.DK as FLOAT)
as AB FROM #TEST A
WHERE ID = 1
UNION ALL
SELECT A.id, B.AB - A.XYZ from #TEST a
INNER JOIN CTE1 b on a.ID = b.ID +1
)
select * from CTE1



nil


As i see you're not doing any cumulative calculation. You're just comparing successive rows and applying calculation between them. This can be very easily achieved by a single join. No need of any CTE and recursion

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000