Query For Trial Balance in Crystal reports

[former_member232853]

Hi All

I am trying to write a sql query for a crystal report. I can get all the accounts, opening balance and closing balance, debits and credits to show.

However i need to show it like the below

Account Code| Account Name| Opening Balance For Period| Jan |Closing Balance jan| Feb|Closing Balance Feb| March| April etc.... | Total Balance|

With the capability to drill down into the month to see what transactions were made (journal Entries against account)

This is the query i have so far:

SELECT T1.Account,Isnull((SELECT SUM(T3.Debit - T3.Credit) FROM OJDT T2

INNER JOIN JDT1 T3 ON T2.TransId = T3.TransId

WHERE DateDiff(dd,T2.RefDate,'20160101') > 0 AND T3.Account LIKE T1.Account

GROUP BY T3.Account),0) 'Opening balance',

     SUM(T1.Debit) 'Debit', SUM(T1.Credit) 'Credit',

     SUM(T1.Debit - T1.Credit)  AS 'Balance'

FROM OJDT T0

INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId

WHERE

     T0.RefDate BETWEEN '20160101' AND '20160131'

GROUP BY

T1.Account

Having SUM(T1.Debit - T1.Credit) != 0

[Former Member · Accepted solution]

Hi Ross,

Try this SQL script to get opening/closing balance month-wise.

/* GL account by month */

declare @PrCl dec(19,6)

declare @de dec(19,6)

declare @cr dec(19,6)

declare @acc char(20)

declare @Pracc char(20)

Create table #W

  (Acc char(20), M char(7), Op dec(19,6),De dec(19,6),Cr dec(19,6),Cl dec(19,6) )

Insert into #W (Acc,M,Op,De,Cr,Cl)

SELECT T1.[Account],convert (char(7),T1.[RefDate],20)Mo,

  0,sum(T1.[Debit])De , sum(T1.[Credit])Cr,0

FROM OJDT T0 INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId

Group by T1.[Account],convert (char(7),T1.[RefDate],20)

Order By T1.[Account],convert (char(7),T1.[RefDate],20)

Declare cu cursor for

Select acc,de,cr from #W

  for update

set @PrAcc=''

Open cu

Fetch next from cu into @acc,@de,@cr

While @@FETCH_STATUS = 0

Begin

If @acc!=@PrAcc set @PrCl=0

Update #W

   set op=@PrCl,

       cl=@PrCl-@Cr+@de

  where current of cu

set @PrCl=@PrCl-@Cr+@de

set @PrAcc=@acc

Fetch next from cu into @acc,@de,@cr

End

deallocate cu

Select * from #w

Drop table #W

With Brgds

Shiva

[former_member232853 · Accepted solution]

Thank you for your query, however i have tried using that query, but i am struggling to pull through the remarks from the journal entry

[Former Member · Accepted solution]

Hi Ross,

You can add another variable for the column for 'Journal Remark' and insert it into the temp table '#W'.

While selecting for the cursor add that variable also. Hope you get it.

With Brgds

Shiva