This code returns exactly the right number of rows and the numbers in the 'closing balance' column are right.
But there is only one calculated field 'closing balance' and when I try to a second calculated field 'opening balance' it all goes wrong. It adds the wrong numbers and the number of rows goes up. See second lot of SQL code with the case statements.
Can anyone tell me what is wrong with the case statements.
select DISTINCT
c.CLIName1,
c.CLICode,
g.AGPName1 as [GL Account Group],
a.COAName1 as [GL Account],
SUM(l.GNLBalanceBase) AS 'Closing Balance',
d.DIVName1,
a.COACode2Code as [GLChart Code 2],
a.COACode2Name1 as [GLChart Code 2 Name],
t.CLTName1 as [Client Type]
from DW.vwChartOfAccount a
inner join DW.vwChartOfAccountGroup g
on a.COAAGPID = g.AGPID
inner join DW.vwGeneralLedger l
on a.COAID = l.GNLCOAID
inner join DW.vwClient c
on l.GNLCLIID = c.CLIID
inner join DW.vwClientType t
on c.CLICLTID = t.CLTID
inner join DW.vwDivision d
on l.GNLDIVID = d.DIVID
where COACode2Code in ('FA301G','FD100G')
and l.GNLFSMID IN (127,128,129,130)
GROUP BY
CLIName1
,CLICode
,AGPName1
,COAName1
,DIVName1
,COACode2Code
,COACode2Name1
,CLTName1
order by d.DIVName1
Correct Samples:
ClientClient CodeGL Account GroupGL AccountClosing Balance DivisionGLChart Code 2GLChart Code 2 NameClient Type
None,None,Intercompany-AR Trade,Interco Receivables (fr FR5507PFS),-491296.58,ARC Worldwide,FA301G,FA301G - IC loans, advances and deposits
None,None,Intercompany-AP Trade,Interco Payables (to FR5507 PFS),5059096.78,ARC Worldwide,FD100G,FD100G - IC Financial Debts
select DISTINCT
c.CLIName1
,c.CLICode
,g.AGPName1 as [GL Account Group]
,a.COAName1 as [GL Account]
,(CASE
WHEN l.GNLFSMID IN (127,128,129,130)
THEN SUM(l.GNLBalanceBase) OVER (PARTITION BY l.GNLCOAID)
ELSE '0'
END) AS 'Closing Balance'
,d.DIVName1
,a.COACode2Code as [GLChart Code 2]
,a.COACode2Name1 as [GLChart Code 2 Name]
,t.CLTName1 as [Client Type]
,(CASE
WHEN l.GNLFSMID IN (127)
THEN SUM(l.GNLBalanceBase) OVER (PARTITION BY l.GNLCOAID)
ELSE '0'
END) AS 'Opening Balance'
from DW.vwChartOfAccount a
inner join DW.vwChartOfAccountGroup g
on a.COAAGPID = g.AGPID
inner join DW.vwGeneralLedger l
on a.COAID = l.GNLCOAID
inner join DW.vwClient c
on l.GNLCLIID = c.CLIID
inner join DW.vwClientType t
on c.CLICLTID = t.CLTID
inner join DW.vwDivision d
on l.GNLDIVID = d.DIVID
where COACode2Code in ('FA301G','FD100G')
GROUP BY
CLIName1
,CLICode
,AGPName1
,COAName1
,DIVName1
,COACode2Code
,COACode2Name1
,CLTName1
,l.GNLFSMID
,l.GNLBalanceBase
,l.GNLCOAID
order by d.DIVName1
Incorrect Samples:
CLIName1CLICodeGL Account GroupGL AccountClosing BalanceDIVName1GLChart Code 2GLChart Code 2 NameClient TypeOpening Balance
None,None,Intercompany-AP Trade,Interco Payables (to FR5507 PFS),-32679585.0300,ARC Worldwide,FD100G,FD100G - IC Financial Debts,None,-32679585.0300
None,None,Intercompany-AP Trade,Interco Payables (to FR5507 PFS),-32679585.0300,ARC Worldwide,FD100G,FD100G - IC Financial Debts,None,0.0000
NoneNoneIntercompany-AP TradeInterco Payables (to FR5507 PFS)0.0000ARC WorldwideFD100GFD100G - IC Financial DebtsNone0.0000
NoneNoneIntercompany-AR TradeInterco Receivables (fr FR5507PFS)0.0000ARC WorldwideFA301GFA301G - IC loans, advances and depositsNone0.0000
NoneNoneIntercompany-AR TradeInterco Receivables (fr FR5507PFS)99201731.6100ARC WorldwideFA301GFA301G - IC loans, advances and depositsNone0.0000
NoneNoneIntercompany-AR TradeInterco Receivables (fr FR5507PFS)99201731.6100ARC WorldwideFA301GFA301G - IC loans, advances and depositsNone99201731.6100