Many thanks for the reply. I have slightly modified the code for the actual views. But it is still not group the data togther.
;WITH
cteGeneralLedger
(GNLID,GNLCLIID,GNLCOAID,GNLFSMID,GNLBalanceBase)
AS (
SELECT GNLID,GNLCLIID,GNLCOAID,GNLFSMID,GNLBalanceBase
FROM DW.vwGeneralLedger
where GNLCOAID in (614,616,618,1019)
),
cteClients
(CLIID,CLICLTID,CLICode,CLIName)
AS (
SELECT CLIID,CLICLTID,CLICode,CLIName1
FROM DW.vwClient
),
cteChartOfAccount
(COAID,COANAME1,COACode2Code,COACode2NAme2,COAAGPID)
AS (
SELECT COAID,COANAME1,COACode2Code,COACode2NAme2,COAAGPID
FROM DW.vwChartOfAccount
where COACode2Code in ('FA301G','FD100G')
),
cteAccountGroup
(AGPID,AGPNAME1)
AS (
SELECT AGPID,AGPNAME1
FROM DW.vwChartOfAccountGroup
),
cteFiscalMonth
(FSMID,FSMNAME1)
AS (
SELECT FSMID,FSMNAME1
FROM DW.vwFiscalMonth
),
cteClientOwner
(CLOID,CLOCLIID,CLODIVID)
AS (
SELECT CLOID,CLOCLIID,CLODIVID
FROM DW.vwClientOwner
),
cteDivision
(DIVID,DIVName1)
AS (
SELECT DIVID,DIVName1
FROM DW.vwDivision
),
cteClientType
(CLTID,CLTName1)
AS(
SELECT CLTID,CLTName1
FROM dw.[vwClientType]
)
SELECT DISTINCT
c.CLIName AS 'Client'
,c.CLICode AS 'Client Code'
,g.AGPName1 as 'GL Account Group'
,a.COAName1 AS 'GL Account'
,(CASE
WHEN l.GNLFSMID IN (113,114,115,116,117,118,119,120,121,122,123,124,125,127,128,129,130)
THEN SUM(l.GNLBalanceBase) OVER (PARTITION BY l.GNLFSMID)
ELSE '0'
END) AS 'Closing Balance'
,d.DIVName1 AS 'Division'
,a.[COACode2Code] AS 'GLChart Code 2'
,a.COACode2NAme2 AS 'GLChart Code 2 Name'
,t.CLTName1 AS 'Client Type'
,(CASE
WHEN l.GNLFSMID IN (113,114,115,116,117,118,119,120,121,122,123,124,125)
THEN SUM(l.GNLBalanceBase) OVER (PARTITION BY l.GNLFSMID)
ELSE '0'
END) AS 'Opening Balance'
FROM
cteClients c
INNER JOIN [cteGeneralLedger] l
ON l.GNLCLIID = c.CLIID
INNER JOIN [cteChartOfAccount] a
ON l.GNLCOAID = a.COAID
INNER JOIN [cteAccountGroup] g
ON g.AGPID = a.COAAGPID
INNER JOIN [cteclientOwner] o
ON o.CLOCLIID = c.CLIID
INNER JOIN [cteDivision] d
ON d.DIVID = o.CLODIVID
INNER JOIN [cteFiscalMonth] m
ON l.GNLFSMID = m.FSMID
INNER JOIN [cteClientType] t
ON t.CLTID = c.CLICLTID
order by d.DIVName1
Current:
Client,Client Code,GL Account Group,GL Account,Closing BalanceDivision,GLChart Code 2,GLChart Code 2 Name,Client Type,Opening Balance
HOUSE,HOUSE,Intercompany-AP Trade,Interco Payables (to FR5507 PFS),-181819127.3600,Alpha 245,FD100G,FD100G - IC Financial Debts,House,0.0000
HOUSE,HOUSE,Intercompany-AP Trade,Interco Payables (to FR5507 PFS),-175767596.3400,Alpha 245,FD100G,FD100G - IC Financial Debts,House,-175767596.3400
HOUSE,HOUSE,Intercompany-AP Trade,Interco Payables (to FR5507 PFS),0.0000,Alpha 245,FD100G,FD100G - IC Financial Debts,House,0.0000
LION RE:SOURCES SINGAPORE,SG5865SG,Intercompany-AP Trade,Interco Payables (to FR5507 PFS),-181819127.3600,Alpha 245,FD100G,FD100G - IC Financial Debts,Intercompany,0.0000
LION RE:SOURCES SINGAPORE,SG5865SG,Intercompany-AP Trade,Interco Payables (to FR5507 PFS),-175767596.3400,Alpha 245,FD100G,FD100G - IC Financial Debts,Intercompany,-175767596.3400
LION RE:SOURCES SINGAPORE,SG5865SG,Intercompany-AP Trade,Interco Payables (to FR5507 PFS),0.0000,Alpha 245,FD100G,FD100G - IC Financial Debts,Intercompany,0.0000
Expected
ClientClient CodeGL Account GroupGL AccountClosing BalanceDivisionGLChart Code 2GLChart Code 2 NameClient TypeOpening Balance
HOUSEHOUSEIntercompany-AP TradeInterco Payables (to FR5507 PFS)-357586723.70Alpha 245FD100GFD100G - IC Financial DebtsHouse-175767596.3
LION RE:SOURCES SINGAPORESG5865SGIntercompany-AP TradeInterco Payables (to FR5507 PFS)-357586723.70Alpha 245FD100GFD100G - IC Financial DebtsIntercompany-175767596.3
The client, client code, GL account group, gl account, division, gl chart code 2, glchart code 2 name, client type should only appear once with the balances negative and postive numbers added togther.