You can use this query and alter it to fit your needs:
/*
Trial Balance Query
*/
declare @Year int
declare @cmp varchar(4)
set @Year = 2018;
With TB (CompanyID,CompanyDescription,Account,[Description],BeginBal,Debit,Credit,NetChange,Ending) as
(
select A.Segment1 [CompanyID],
C.dscriptn [CompanyDescription],
[account number] ACCOUNT,
B.DSCRIPTN [Description],
SUM(CASE When [Period ID] = 0 THEN [Period Balance] ELSE 0 END) AS BEGINBAL,
SUM(CASE When [Period ID] > 0 THEN [Debit Amount] ELSE 0 END) AS Debit,
SUM(CASE When [Period ID] > 0 THEN [Credit Amount] ELSE 0 END) AS Credit,
SUM(CASE When [Period ID] > 0 THEN [Period Balance] ELSE 0 END) AS NetChange,
SUM([Period Balance]) AS ENDING
FROM (AccountSummary A
left join gl40200 B
on A.Segment2 = b.SGMNTID)
left join gl40200 C on
A.segment1 = c.sgmntid
WHERE [Account Type]='posting account'
and [Year] = @Year
and b.sgmtnumb = 2
and c.sgmtnumb = 1
GROUP BY [segment1]
,[Account Number]
,b.dscriptn
,C.dscriptn
)
Select [CompanyID]
,[CompanyDescription]
,Account
,[Description] = case [Description]
when '' then (select top 1 actdescr from gl00100 where MNACSGMT = Account)
else [Description]
end
,BeginBal
,Debit
,Credit
,NetChange
,Ending
from TB
order by CompanyID,account;
