Sunday, April 17, 2016

Best use of cross Apply GL General ledger Debit Credit with negative values


CREATE TABLE #test
(GLID varchar(10), Amt int)
INSERT INTO #test
(GLID, Amt)
SELECT '1111', -100 UNION ALL
SELECT '2222', 250 UNION ALL
SELECT '3333', -300 UNION ALL
SELECT '4444', 150

select * from #test

select t1.GLID,

(case  when t1.Amt < 0 then 0
else t1.Amt
end

) AS 'DR',

(case  when t1.Amt > 0 then 0
else abs( t1.Amt  )
end

) AS 'CR' from #test t1 cross apply #test t2 where t1.GLID = t2. GLID

No comments: