Well, I'm not sure if these totals are correct but here's another stab at it...I made some significant additions to the opening balance columns (actually one for each year). It's hard for me to know given that I'm not immersed in this data like you are of course.
;WITH
cteGeneralLedger
(GNLID,GNLCLIID,GNLCOAID,GNLFSMID,GNLBalanceBase)
AS (
SELECT 1,1385,576,113,-4845.0000 UNION ALL
SELECT 2,1385,713,114,-395.8500 UNION ALL
SELECT 3,3139,39,115,8703.3400 UNION ALL
SELECT 4,727,39,116,-1321.6500 UNION ALL
SELECT 5,727,39,117,-8811.0000 UNION ALL
SELECT 6,3139,713,118,-15416.5200 UNION ALL
SELECT 7,1480,713,119,18429.9200 UNION ALL
SELECT 8,3144,39,120,1321.6500 UNION ALL
SELECT 9,1480,713,121,-10799.0000 UNION ALL
SELECT 10,3144,576,122,4371.0000 UNION ALL
SELECT 11,3139,713,123,1619.8500 UNION ALL
SELECT 12,727,45,124,1786.1400 UNION ALL
SELECT 13,3139,39,125,46.4200 UNION ALL
SELECT 14,727,576,127,-12802.4000 UNION ALL
SELECT 15,1480,713,128,1198.2600 UNION ALL
SELECT 16,1480,713,129,-1785.0000 UNION ALL
SELECT 17,3139,713,130,800.0000
),
cteClients
(CLIID,CLICode,CLIName)
AS (
SELECT 727,'HARK','HARK CONSULTING' UNION ALL
SELECT 1385,'3HPARC','3HARCHITECTS' UNION ALL
SELECT 1480,'GUYCAR','GUY CARPENTER' UNION ALL
SELECT 3139,'ABNAP','ABB PTE LTD' UNION ALL
SELECT 3144,'SYSACC','SYSTEM ACCESS'
),
cteChartOfAccount
(COAID,COANAME1,COACode2Code,COACode2NAme2,COAAGPID)
AS (
SELECT 39,'Total Billings','RV10','RV10 Billings',1 UNION ALL
SELECT 45,'Prod Billing','RV40','RV40 Revenue',2 UNION ALL
SELECT 576,'Loan','FD100G','FD100G Financial Debts',3 UNION ALL
SELECT 713,'Receivables','FA301G','FA301G Loans Advances',4
),
cteAccountGroup
(AGPID,AGPNAME1)
AS (
SELECT 1,'Assets' UNION ALL
SELECT 2,'Goodwill' UNION ALL
SELECT 3,'Deferred' UNION ALL
SELECT 4,'Trade' UNION ALL
SELECT 5,'Cash'
),
cteFiscalMonth
(FSMID,FSMNAME1)
AS (
SELECT 113,'Opening Balance 12' UNION ALL
SELECT 114,'Jan 12' UNION ALL
SELECT 115,'Feb 12' UNION ALL
SELECT 116,'Mar 12' UNION ALL
SELECT 117,'Apr 12' UNION ALL
SELECT 118,'May 12' UNION ALL
SELECT 119,'Jun 12' UNION ALL
SELECT 120,'Jul 12' UNION ALL
SELECT 121,'Aug 12' UNION ALL
SELECT 122,'Sep 12' UNION ALL
SELECT 123,'Oct 12' UNION ALL
SELECT 124,'Nov 12' UNION ALL
SELECT 125,'Dec 12' UNION ALL
SELECT 127,'Opening Balance 13' UNION ALL
SELECT 128,'Jan 13' UNION ALL
SELECT 129,'Feb 13' UNION ALL
SELECT 130,'Mar 13'
),
cteClientOwner
(CLOID,CLOCLIID,CLODIVID)
AS (
SELECT 1,727,1 UNION ALL
SELECT 2,1385,2 UNION ALL
SELECT 3,1480,3 UNION ALL
SELECT 4,3139,3 UNION ALL
SELECT 5,3144,4 UNION ALL
SELECT 6,727,5
),
cteDivision
(DIVID,DIVName1)
AS (
SELECT 1,'Digital' UNION ALL
SELECT 2,'Zenith' UNION ALL
SELECT 3,'Stars' UNION ALL
SELECT 4,'MSL' UNION ALL
SELECT 5,'Leo'
)
SELECT DISTINCT
c.CLIName AS 'Client'
,c.CLICode AS 'Client Code'
,l.GNLID AS 'GL Account ID'
,g.AGPName1 as 'GL Account Group'
,a.COAName1 AS 'GL Account'
,SUM(l.GNLBalanceBase) OVER (PARTITION BY l.GNLFSMID) AS 'Closing Balance'
,d.DIVName1 AS 'Division'
,a.[COACode2Code] AS 'GLChart Code 2'
,a.COACode2NAme2 AS 'GLChart Code 2 Name'
,(CASE
WHEN m.FSMID BETWEEN 113 AND 125
THEN SUM(l.GNLBalanceBase) OVER (PARTITION BY
(SELECT SUM(FSMID)
FROM cteFiscalMonth m1
INNER JOIN [cteGeneralLedger] l1
ON l1.GNLFSMID = m1.FSMID
WHERE
l1.GNLID = l.GNLID
AND m1.FSMID BETWEEN 113 AND 125))
ELSE '0'
END) AS 'Opening Balance 2012'
,(CASE
WHEN m.FSMID BETWEEN 127 AND 130
THEN SUM(l.GNLBalanceBase) OVER (PARTITION BY
(SELECT SUM(FSMID)
FROM cteFiscalMonth m1
INNER JOIN [cteGeneralLedger] l1
ON l1.GNLFSMID = m1.FSMID
WHERE
l1.GNLID = l.GNLID
AND m1.FSMID BETWEEN 127 AND 130))
ELSE '0'
END) AS 'Opening Balance 2013'
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