Subtraction Issue - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Subtraction Issue

Author

Topic

khaos
Starting Member

6 Posts

Posted - 2011-07-05 : 19:36:06

Hello!
My issue is that my stored procedure only works if there is data in the table that it's referencing. For example:
Table A:
----------------------------------
Type | Totals
----------------------------------
Active 0

Table B:
----------------------------------
TotalActive
------------------------------------
5

So basically if Table A's values are Null, that is there are no records with Type 'Active' in them, then it should show the Total Active column as 5, or do a subtraction 5-0(Null) = 5

Here's my stored procedure:

 
ALTER PROCEDURE [dbo].[GetDaysLeft]
@ID int
AS
DECLARE
@DaysLeft float,
@DaysTaken float,
@TotalDays float
SET @DaysLeft = 0
SET @DaysTaken = (SELECT SUM(DayType) from dbo.Tectonic WHERE
Type = 'Active' AND ID = @ID)
SET @TotalDays = (SELECT Totals from dbo.Users WHERE ID = @ID)
SET @Daysleft = @TotalDays - @DaysTaken
Select @DaysLeft AS FSDLeft

Just by itself, the stored procedure does the calculations properly, only when there is a record in Table A as 'Active', otherwise when there is no data then it doesn't return anything because there are no values in Table A stating 'Active'.

It either doesn't return anything or it returns only 5 (which is the total in Table B) which is partially right, but when I put in a record stating Active in Table A, it again returns 5 instead of 4. This happened after I tried:

(Select Coalesce(0, SUM(DayType)) from dbo.Tectonic Where Type = 'Active' AND ID = @ID)

Any ideas?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-05 : 20:11:27

Try changing the coalesce to this:

SET @DaysTaken = coalesce((SELECT SUM(DayType) from dbo.Tectonic WHERE
Type = 'Active' AND ID = @ID),0)

You may be able to get the same result using a single query using this though:

select
	Totals-coalesce(sum(DayType),0) as FSDLeft
from
	dbo.Tectonic t
	inner join dbo.Users u
		on u.ID=t.ID
where
	u.ID = @ID;

obiron
Starting Member

23 Posts

Posted - 2011-07-06 : 08:10:16

jsut to clarify Sunitabecks solution - you have the elements the wrong way round in the COALESCE.

Coalesce searches items in the array until it finds one that is not NULL. Because you have 0 (Zero) as the first element, it will always use that.

Obiron

khaos
Starting Member

6 Posts

Posted - 2011-07-06 : 11:48:40

Thanks a lot guys for your responses, Sunita your solution worked for me. Thanks again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources