here is my query with left join and i am automatically developing credit debit and balance... perhaps i am lacking upon column. i used gl_drcr as main column and split it by query into credit and debit but i can not able to get value of balance, i have column of amount also, where i can put amount values.... can you help me to sort out where is mt fault in this query? my stored procedure is as follows:-
GO ALTER procedure [dbo].[sp_getDataML] ( @gl_acid int, @userid int
) as begin
SELECT ROW_NUMBER() OVER (Order by gLedeger.PK_id) as sno,case when master.ma_accname is null then '' else ma_accname end as AccName, gLedeger.gl_date as date,gLedeger.gl_narration as Narration,gLedeger.gl_drcr as DRCR, case when gLedeger.gl_drcr > 0 then abs(gLedeger.gl_drcr) else 0.00 end as debit, case when gLedeger.gl_drcr < 0 then abs(gLedeger.gl_drcr) else 0.00 end as credit, SUM(COALESCE(gLedeger.gl_drcr.credit, 0) - COALESCE(gLedeger.gl_drcr.debit, 0)) AS BalanceFROM gLedeger LEFT JOIN master ON gLedeger.GL_ACID = master.PK_ID left join registration r on master.userId=@userid WHERE gLedeger.GL_ACID = @gl_acid /and LEFT(gLedeger.gl_voucher,2)=@voucher/
ORDER BY gLedeger.gl_date
end