declare @acc_code1 varchar(7)= '2310001' declare @f_date1 datetime= '20161101' select acc_code, SUM(data1.dr) as dr, SUM(data1.cr) as cr, sum(data1.dr)-sum(data1.cr) as balance, CASE sign(sum(data1.dr) - sum(data1.cr)) when 1 then 'dr' when -1 then 'cr' else '' end as drcr from ( select code as acc_code, SUM(open_dr) as dr,SUM(open_cr) as cr from master where acc_code=@acc_code1 group by code union all select acc_code, SUM(dr_amount) as dr, SUM(cr_amount) as cr from vouchers where acc_code=@acc_code1 and date<@f_date1 group by acc_code union all select acc_code, SUM(dr_amount) as dr, SUM(cr_amount) as cr from cashsalp where acc_code=@acc_code1 and date<@f_date1 group by acc_code) as data1 group by acc_code order by acc_code>Dear Experts.
>declare @acc_code1 varchar(7)= '2310001' >declare @f_date1 datetime= '2016-11-01' > >select acc_code,SUM(data1.dr)as dr, SUM(data1.cr)as cr, >sum(data1.dr)-sum(data1.cr)as balance, >(CASE > WHEN SUM(data1.dr)>SUM(data1.cr) THEN drcr='dr' > WHEN SUM(data1.cr)>SUM(data1.dr) THEN drcr='cr' > WHEN SUM(data1.cr)=SUM(data1.dr)THEN drcr='' > END) > from >( >select code as acc_code, SUM(open_dr)as dr,SUM(open_cr)as cr > from master where acc_code=@acc_code1 group by code >union all >select acc_code, SUM(dr_amount)as dr,SUM(cr_amount)as cr > from vouchers where acc_code=@acc_code1 and date<@f_date1 group by acc_code >union all >select acc_code,SUM(dr_amount)as dr,SUM(cr_amount)as cr > from cashsalp where acc_code=@acc_code1 and date<@f_date1 group by acc_code) >as data1 >group by acc_code >order by acc_code > >>