CODEWITHSUNDEEP
sql-servert-sqlcaseiif
Alexander
Alexander

Reputation: 405

if a number in column is negative,show positive,else show the number

I have a [Preko]column in a query that is calculating the difference between two columns.If the number is negative,I need to show it positive,and if it is positive,it should stay the same.I can't use ABS in this one.I tried with the case,but it didn't work. The problem I am getting is that column Preko is invalid.

This is the code for my second try with iif:

SELECT FP.Firma
      ,FP.NazFirme
      ,FP.Konto
      ,FP.NazivKonta
      ,FP.Partner
      ,FP.NazivPartnera
      ,Sum(FP.Duguje) AS dug
      ,Sum(FP.Potrazuje) AS pot
      ,Sum(IIf([FP].[Konto] Like '2*'
      ,[duguje]-[potrazuje]
      ,[potrazuje]-[duguje])) AS USaldo
      ,Sum(IIf([datumval]<= '1.1.2017'
      ,IIf([FP].[Konto] Like '2*'
      ,[duguje]-[potrazuje]
      ,[potrazuje]-[duguje]),0)) AS [Preko]
      ,IIf([Preko]<0,0,[Preko]) AS Preko1
FROM tblFinansijskiPodaci FP
Where FP.Firma = 1 
AND FP.Partner=1110
GROUP BY FP.Firma
        ,FP.NazFirme
        ,FP.Konto
        ,FP.NazivKonta,
        ,FP.Partner
        ,FP.NazivPartnera 
HAVING (((FP.Konto)=2040))

Upvotes: 1

Views: 3096

Answers (3)

Matthew Baker
Matthew Baker

Reputation: 2729

WITH cte AS (
SELECT FP.Firma
      ,FP.NazFirme
      ,FP.Konto
      ,FP.NazivKonta
      ,FP.Partner
      ,FP.NazivPartnera
      ,Sum(FP.Duguje) AS dug
      ,Sum(FP.Potrazuje) AS pot
      ,Sum(IIf([FP].[Konto] Like '2*'
      ,[duguje]-[potrazuje]
      ,[potrazuje]-[duguje])) AS USaldo
      ,Sum(IIf([datumval]<= '1.1.2017'
      ,IIf([FP].[Konto] Like '2*'
      ,[duguje]-[potrazuje]
      ,[potrazuje]-[duguje]),0)) AS [Preko]
FROM tblFinansijskiPodaci FP
Where FP.Firma = 1 
AND FP.Partner=1110
GROUP BY FP.Firma
        ,FP.NazFirme
        ,FP.Konto
        ,FP.NazivKonta,
        ,FP.Partner
        ,FP.NazivPartnera 
HAVING (((FP.Konto)=2040))
)
SELECT *, CASE WHEN Preko<0 THEN 0 ELSE Preko END preko1 FROM cte

Upvotes: 1

Matthew Baker
Matthew Baker

Reputation: 2729

You can also use a case statement instead of the iif section.

  CASE WHEN Preko<0 THEN 0 ELSE Preko END

or

  CASE WHEN Preko<0 THEN -Preko ELSE Preko END

That seems to be more in line with your logic depending on how you want to handle the negatives.

Upvotes: 2

Matthew Baker
Matthew Baker

Reputation: 2729

Agree with HoneyBadger - ABS is the way to go ABS(-1) returns 1. Have a look at the APEX SQL tools for a quick formatting option. It's free and makes your code a lot easier to read, which means you'll find you get more answers.

Upvotes: 2

Related Questions