cancel
Showing results for 
Search instead for 
Did you mean: 

Trial Balance Query with opening Balance

Former Member
0 Kudos

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.

View Entire Topic
former_member575911
Discoverer
0 Kudos



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
Newcomer
0 Kudos
Data show only expense need to vendor and customer also.