Skip to Content

How to get value of closing balance for previous period​ in query of SAP B1?.

Mar 27, 2017 at 03:09 PM


avatar image

Hi all,

How can I get value of closing balance for previous period in query of SAP B1?

This is the query I tried to do

SELECT T0.[AcctCode], T0.[AcctName],sum(T1.[Debit])-sum(T1.[Credit])as Total , T1.[ProfitCode],(select T4.[OcrName]from OOCR T4 where T4.[OcrCode]=T1.[ProfitCode])as'Cost center name',(selectsum(T5.[Debit])-sum(T5.[Credit])as Total from
OACT T4  INNERJOIN JDT1 T5 ON T4.[AcctCode]= T5.[Account]INNERJOIN OJDT T6 ON T5.[TransId]= T6.[TransId]INNERJOIN OOCR T7 ON T5.[ProfitCode]= T7.[OcrCode]where T6.[RefDate]>=Cast(YEAR(GETDATE())as nvarchar(4000))+'-01-01'and T6.[RefDate]<[%0]and T4.[AcctCode]=T0.[AcctCode]and T5.[ProfitCode]<>''AND T5.[ProfitCode]=T1.[ProfitCode])as Opening_balance
FROM OACT T0  INNERJOIN JDT1 T1 ON T0.[AcctCode]= T1.[Account]INNERJOIN OJDT T2 ON T1.[TransId]= T2.[TransId]INNERJOIN OOCR T3 ON T1.[ProfitCode]= T3.[OcrCode]WHERE T1.[ProfitCode]<>''and T2.[RefDate]>=[%0]AND  T2.[RefDate]<=[%1]GROUPBY T0.[AcctCode], T0.[AcctName], T1.[ProfitCode]

But column of opening_balance is skipping to fill some rows caused by

AND T5.[ProfitCode]=T1.[ProfitCode]

found in sub query in where clause,

If I replace it with

groupby T5.[ProfitCode]

Execute button does not work.

Please anyone can help me to correct my query.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

0 Answers