Positive in sql server - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Positive in sql server

Author

Topic

Nil35
Starting Member

20 Posts

Posted - 2013-07-24 : 19:15:06

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

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-24 : 20:06:23

Is this what you are looking for:
[CODE]

;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

[/CODE]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-07-24 : 22:20:38

perhaps it will be clearer if you can post some sample data and the expected result

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-25 : 04:14:45

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

Nil35
Starting Member

20 Posts

Posted - 2013-07-25 : 10:45:15

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-07-25 : 10:55:37

i am still don't quite understand what are you trying to achieve.

Please post some sample data and expected result.

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-07-25 : 10:58:51

or is this what you want ?

SELECT	SUM(CASE WHEN ID = 1 THEN DK ELSE -XYZ END)
FROM	#TEST

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-26 : 00:13:08

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources