Simple Query - 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 2000 Forums

SQL Server Development (2000)

Simple Query

Author

Topic

CLages
Posting Yak Master

116 Posts

Posted - 2004-04-28 : 16:57:17

Deb Cre Value
1 2 10,00
1 3 10,00
4 1 5,00

i have a table named Duplicat with this 3 columns

I would like to do a SUM(value)

Select Sum(value) from Duplicat Where deb = 1 or Cre = 1
in this case I get 25,00

My problem is, when the Value (in this case 5,00) is in "CRE" column
i dont want to Add, I want to Subtract

then the result would be 15,00

How can i do this?

Tks

C. Lages

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-28 : 17:25:09

DROP TABLE #Duplicate

CREATE TABLE #Duplicate(
Deb INT,
Cre INT,
Value MONEY)

INSERT #Duplicate(Deb,Cre,Value)
SELECT 1,2,10.00
UNION ALL
SELECT 1,3,10.00
UNION ALL
SELECT 4,1,5.0

DECLARE @entry INT

SELECT @entry = 1

SELECT
SUM(
CASE
WHEN Deb = @entry THEN Value
WHEN Cre = @entry THEN Value*-1
ELSE 0
END)
FROM #Duplicate
WHERE
Deb = @entry
OR Cre = @entry

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

CLages
Posting Yak Master

116 Posts

Posted - 2004-04-28 : 17:49:48

TKS A LOT
THAT IS IT
Carlos Lages

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources