Account balance of G/L for specific date range

[former_member1133820]

Dear All,

I wish to write a query to get the details of g/l account balance on a given date. How to get the closing balance of each row? I have gone through some query with the monthly opening & closing balance for each account but how to show the details transactions(debit, credit & balance) of each G/L in a given date range?

Hope to get some guidance from the experts.

Thanks & BR,

Leng

[Former Member]

Hi Hui

To look for the G-L account balance, you can do so by using Trial Balance report rather than query, in trial balance there is an option for Account Balance, select the Date Range on the sceen window, On G-L Accounts you can change the level from 1 to 10 , on level 10 u will get the details for each accounts (Active Accounts), when system shows the report over on the top there is an option to "Hide Titles"

On this report you will get the Debit credit and other transactions, and also on trial balance report window there are option to include Opening balance.

follow the pah: Finance-->Financial Report-->Trial Balance.

Thanks And Regards,

Bhushan Verma

[former_member1133820]

Hi Bhushan,

I need to put it in query as I need to map it with another temp table.

Thanks & BR,

Leng

[former_member196081]

try below

DECLARE @D1 DATETIME

DECLARE @D2 DATETIME

SET @D1=  '2013-11-01 00:00:00.000'

SET @D2=  '2014-04-01 00:00:00.000'

select

T0.RefDate as  'Posting Date' ,T0.DueDate ,T0.TaxDate as 'Document Date',T0.BaseRef  as 'Doc.No.' ,

T0.TransId as 'Trans.No.' ,T0.Memo as 'Remarks' , T1.[ContraAct] 'OffsetAcct',

Account=(CASE WHEN T2.AcctName IS NULL THEN  t3.cardname else  T2.AcctName end ),

  T1.Debit, T1.Credit

from OJDT T0

inner join JDT1 T1 on T0.TransId =T1.TransId

left outer Join OACT T2 ON T2.AcctCode=T1.ContraAct

left outer join OCRD t3 on t3.CardCode=T1.ContraAct

Where T0.RefDate>=@d1 and T0.RefDate<=@d2  and (T1.[ContraAct]='311200006' or t1.ShortName='311200006')

order by T0.TransId ,t1.Line_ID

Note :: replace the contraact and shortname field as per your reuirment

[former_member1133820]

Hi Deepak,

Thanks for the query but I wish to get the cumulative balance for each G/L as well.

Thanks & BR,

Leng