Trial Balance Query with opening Balance

[Former Member]

Dear All

Can you please share query of Trial Balance with opening format should be as below

GL Code GL Name Opeing Balance Dr. Cr. Closing Balance

All GL Codes including those in which no entery during the period.

[Former Member]

create proc [dbo].[TRIALBALANCE]
@Fdate datetime,
@Tdate datetime
as
begin


select A.AcctCode,A.AcctName,sum(A.OP)'Opening Balance' ,sum(A.Debit)'Debit',sum(A.Credit)'Credit'
,(sum(A.OP)+sum(A.Debit)-sum(A.Credit))'Balance',isnull(A.Location,'Blank')'Location',isnull(A.Project,'Blank')'Project'
from
(select T3.AcctCode,T3.AcctName,sum(T1.Debit-T1.Credit)'OP',0.00 'Debit',0.00 'Credit',T0.U_UNE_LOCNM 'Location',(case when T1.Project='' then null else isnull(T1.Project,null) end)'Project'
from OJDT T0 inner join JDT1 T1 
on T0.TransId=T1.TransId 


left join oact T3 on T3.AcctCode=T1.Account 
where  T0.RefDate < @Fdate
group by T3.AcctCode,T3.AcctName,T0.U_UNE_LOCNM,T1.Project




union all


select T3.AcctCode,T3.AcctName,0.00'OP',sum(T1.Debit) 'Debit',sum(T1.Credit) 'Credit',T0.U_UNE_LOCNM 'Location',(case when T1.Project='' then null else isnull(T1.Project,null) end)'Project'
from OJDT T0 inner join JDT1 T1 
on T0.TransId=T1.TransId 


left join oact T3 on T3.AcctCode=T1.Account 
where  T0.RefDate between @Fdate and @Tdate
group by T3.AcctCode,T3.AcctName,T0.U_UNE_LOCNM,T1.Project


)A
group by A.AcctCode,A.AcctName,A.Location,A.Project
order by A.AcctCode
end

[AlpeshDave]

Data show only expense need to vendor and customer also.

[Abdul]

Use system standard trial balance its same you are requesting via query.