General Ledger related queries, topics and discussions
User avatar
By lhaof
#675 Hi there,

Trying to make a balance query against iscala to get ledger balances. Can someone help me out with
I Only want the balances for accounts starting with 1 or 2
Thanks in advance!
**************************************
DECLARE @DateTo Datetime = '2016-06-30 23:59:59'

SUBSTRING(GL06001,1,4) as 'Account',
ROUND(SUM(GL06004),2) AS 'Balance'

FROM GL060116

WHERE SUBSTRING(GL06001,1,1) IN ('1','2')
AND (CONVERT(VARCHAR(10),GL06003,120) <= @DateTo)
AND (CONVERT(VARCHAR(10),GL06003,120) >= '2000-01-01')
--AND (CONVERT(VARCHAR(10),GL06014,120) <= @DateTo)
--AND (CONVERT(VARCHAR(10),GL06014,120) >= '2000-01-01')
--AND GL06038='GL'
GROUP BY SUBSTRING(GL06001,1,4)
ORDER BY SUBSTRING(GL06001,1,4)


User avatar
By GabrielValentini
#677 Hello the query is OK.
In iScala your transaction table are for fiscal period.
For example GL060116 you have only transaction for company 01 and year 2016.
All transaction previus 01/01/2016 not in this table. (only if you have in this table a initial transaction of period with the balance for previus exercises)
The rest of query its ok.
Sorry for my english i argentine and talk in spanish language.