Almog Almog - 5 months ago 17
SQL Question

IF ELSE In Column Selection

I am trying to write a query that make balance sheet is SAP Business one.
I have a sum calculation in there and I want it to be like that:

if SUM(T1.Credit-T1.Debit) > 0
show the value in column "Positive Total" and in negative total put 0 or something.
if SUM(T1.Credit-T1.Debit) < 0
show the value in column "Negative Total" and in "Positive total" put 0 or something.


The code is:

SELECT T1.Account, Isnull((SELECT SUM(T3.Debit - T3.Credit)
FROM OJDT T2
INNER JOIN JDT1 T3 ON T2.TransId = T3.TransId
INNER JOIN OACT o ON o.AcctCode = T3.Account
WHERE DateDiff(dd,T2.RefDate,'20140101') > 0
AND T3.Account LIKE T1.Account
GROUP BY T3.Account),0) AS 'Opening Balance',
SUM(T1.Debit) AS 'Debit',
SUM(T1.Credit) AS 'Credit',
SUM(T1.Credit-T1.Debit ) AS 'Positive Total',
SUM(T1.Credit-T1.Debit ) AS 'Negative Total'
,AcctName, GroupMask,Levels,
(CASE GroupMask
WHEN 1 THEN 'ASSET'
WHEN 2 THEN 'LIABILITIES'
WHEN 3 THEN 'Capital and Reserves'
WHEN 4 THEN 'Turnover'
WHEN 5 THEN 'Cost of Sales'
WHEN 6 THEN 'Operating Costs'
WHEN 7 THEN 'Non-Operating Income and Expenditure'
ELSE 'NOT INCLUDED'
END) [GROUP]
FROM OJDT T0
INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId
INNER JOIN OACT T4 ON T4.AcctCode = T1.Account
WHERE T0.RefDate BETWEEN '20150101' AND '20160131'
GROUP BY T1.Account, AcctName, GroupMask, Levels
Having SUM(T1.Debit - T1.Credit) != 0
ORDER BY T1.Account


Thanks In advance,
Almog.

Answer

Try this:

CASE WHEN SUM(T1.Credit-T1.Debit ) > 0 
    THEN SUM(T1.Credit-T1.Debit ) 
    ELSE 0 
END  'Positive Total',
CASE WHEN SUM(T1.Credit-T1.Debit ) < 0 
    THEN SUM(T1.Credit-T1.Debit ) 
    ELSE 0 
END AS 'Negative Total'