Skip to Content
0
Feb 03, 2019 at 05:54 AM

Balance Sheet Query for report,

84 Views

Hello there experts.

how can i create report like the original balance sheet on the system my asset = liabilities + equity is not equal every time i do it on my report but when i generate the balance sheet on the system it matches, in my crystal report it doesnt please help,

here's my code.

declare @FS table(
	Date datetime,
	AcctCode varchar(100),
	AcctName varchar(100),
	AcctBalance numeric(19,6),
    ppcode int,
    ppname varchar(100))


insert into @FS
select case when a.refdate is null then a.taxdate else a.refdate end as 'Date'
	, b.account
	, c.acctname
	, case when c.groupmask  in (2,3) then (b.Debit - b.credit) * -1 else (b.Debit - b.credit) end
	, d.[AbsEntry]
	, d.[Code]  
from ojdt a 
	inner join jdt1 b on a.transid = b.transid 
	inner join oact c on b.account = c.acctcode 
	inner join ofpr d on a.FinncPriod = d.AbsEntry


insert into @FS
select a.refdate as 'Date'
	, 1
	, 'Profit Period'
	, (sum(b.credit)-sum(b.debit))
	, d.[AbsEntry]
	, d.[Code]   
from ojdt a 
	inner join jdt1 b on a.transid = b.transid 
	inner join oact c on b.account = c.acctcode 
	inner join ofpr d on a.Finncpriod=d.absentry
where c.acttype in ('I','E') 
group by a.taxdate
	, a.refdate
	,d.[AbsEntry]
	, d.[Code]




select x.CompnyName,
	case 


		when b.GroupMask = '1'
		then '101-Current Assests'


		when b.GroupMask = '2'
		then '211-Current Liabilities'


		when b.GroupMask = '3' 
		then '255-Current Equity'


		else 'N/A' end as 'Group'
		
	, left(a.ppname,4) as 'Month'
	, a.ppcode as 'Year'
	, a.AcctCode
	, a.AcctName
	, sum(a.AcctBalance) as 'AcctBalance' 
from @FS a 
	left join oact b on a.AcctCode = b.AcctCode, OADM x 
group by x.CompnyName
	, b.GroupMask
	, a.ppname
	, a.ppcode
	, a.AcctCode
	, a.AcctName
	, b.acctcode
	, b.fathernum 
having sum(a.AcctBalance) <> 0