Query Performance

Question

Post reply

Query Performance

ringovski

Hall of Fame

Points: 3521
More actions
August 15, 2013 at 9:21 pm

#297713

Hi All,
I have written a T SQL statement and i estimate it will take 8 hours to complete, it has been running now for 3 hrs 20 mins and it has produced around half the expected results. I am querying a data warehouse with read only access at the moment, so i can't use the query analyzer. Can anyone look at my code suggest some improvements?
;WITH
cteClosingBalance
(CLINAME1,CLICODE,AGPNAME1,COANAME1,DIVNAME1,COACODE2CODE,COACODE2NAME1,CLTNAME1,closingBalance)
AS(
select
c.CLIName1
,c.CLICode
,g.AGPName1 as [GL Account Group]
,a.COAName1 as [GL Account]
,d.DIVName1
,a.COACode2Code as [GLChart Code 2]
,a.COACode2Name1 as [GLChart Code 2 Name]
,t.CLTName1 as [Client Type]
,SUM(l.GNLBalanceBase) AS 'Closing 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 ('CA100','CA180','CA200','CA210')
and l.GNLFSMID IN (127,128,129,130)
GROUP BY
CLIName1
,CLICode
,AGPName1
,COAName1
,DIVName1
,COACode2Code
,COACode2Name1
,CLTName1
),
cteOpeningBalance
(DIVNAME1,OpeningBalance,CLTNAME1,COACODE2CODE)
AS(
select
d.DIVName1
,SUM(l.GNLBalanceBase) AS 'Opening Balance'
,t.CLTName1
,a.COACode2Code as [GLChart Code 2]
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 ('CA100','CA180','CA200','CA210')
and l.GNLFSMID IN (127)
GROUP BY
DIVName1
,COACode2Code
,t.CLTName1
),
cteJan13
(DIVNAME1,JBalance,CLTName1,COACODE2CODE)
AS(
select
d.DIVName1
,SUM(l.GNLBalanceBase) AS 'January'
,t.CLTName1
,a.COACode2Code as [GLChart Code 2]
from DW.vwChartOfAccount a
inner join DW.vwGeneralLedger l
on a.COAID = l.GNLCOAID
inner join DW.vwDivision d
on l.GNLDIVID = d.DIVID
inner join DW.vwClient c
on l.GNLCLIID = c.CLIID
inner join DW.vwClientType t
on c.CLICLTID = t.CLTID
where COACode2Code in ('CA100','CA180','CA200','CA210')
and l.GNLFSMID IN (128)
GROUP BY
DIVName1
,t.CLTName1
,COACode2Code
),
cteFeb13
(DIVNAME1,FBalance,CLTName1,COACODE2CODE)
AS(
select
d.DIVName1
,SUM(l.GNLBalanceBase) AS 'February'
,t.CLTName1
,a.COACode2Code as [GLChart Code 2]
from DW.vwChartOfAccount a
inner join DW.vwGeneralLedger l
on a.COAID = l.GNLCOAID
inner join DW.vwDivision d
on l.GNLDIVID = d.DIVID
inner join DW.vwClient c
on l.GNLCLIID = c.CLIID
inner join DW.vwClientType t
on c.CLICLTID = t.CLTID
where COACode2Code in ('CA100','CA180','CA200','CA210')
and l.GNLFSMID IN (129)
GROUP BY
DIVName1
,COACode2Code
,t.CLTName1
),
cteMar13
(DIVNAME1,MBalance,CLTName1,COACODE2CODE)
AS(
select
d.DIVName1
,SUM(l.GNLBalanceBase) AS 'March'
,t.CLTName1
,a.COACode2Code as [GLChart Code 2]
from DW.vwChartOfAccount a
inner join DW.vwGeneralLedger l
on a.COAID = l.GNLCOAID
inner join DW.vwDivision d
on l.GNLDIVID = d.DIVID
inner join DW.vwClient c
on l.GNLCLIID = c.CLIID
inner join DW.vwClientType t
on c.CLICLTID = t.CLTID
where COACode2Code in ('CA100','CA180','CA200','CA210')
and l.GNLFSMID IN (130)
GROUP BY
DIVName1
,COACode2Code
,t.CLTName1
)
select
cb.CLIName1 as [Client]
,cb.CLICode as [Client Code]
,cb.AGPName1 as [GL Account Group]
,cb.COAName1 as [GL Account]
,cb.closingBalanceas [Closing Balance]
,cb.COACode2Code as [GLChart Code 2]
,cb.COACode2Name1 as [GLChart Code 2 Name]
,cb.DIVName1 as [Division]
,cb.CLTName1 as [Client Type]
,ob.OpeningBalance as [Opening Balance]
,j.JBalance as [January]
,f.FBalance as [Feburary]
,m.MBalance as [March]
,SUM(ISNULL(ob.OpeningBalance,0) + ISNULL(j.JBalance,0) + ISNULL(f.FBalance,0) + ISNULL(m.MBalance,0)) as [March End]
from cteClosingBalance cb
inner join cteOpeningBalance ob
on ob.DIVNAME1 = cb.DIVNAME1 AND ob.COACODE2CODE = cb.COACODE2CODE AND ob.CLTNAME1 = cb.CLTNAME1
left outer join cteJan13 J
on j.CLTName1 = cb.CLTNAME1 and j.DIVNAME1 = cb.DIVNAME1 AND j.COACODE2CODE = cb.COACODE2CODE
left outer join cteFeb13 f
on f.CLTName1 = cb.CLTNAME1 and f.DIVNAME1 = cb.DIVNAME1 AND f.COACODE2CODE = cb.COACODE2CODE
left outer join cteMar13 m
on m.CLTName1 = cb.CLTNAME1 and m.DIVNAME1 = cb.DIVNAME1 AND m.COACODE2CODE = cb.COACODE2CODE
GROUP BY
cb.CLIName1
,cb.AGPName1
,cb.COAName1
,cb.DIVName1
,cb.COACode2Code
,cb.COACode2Name1
,cb.CLTName1
,cb.closingBalance
,ob.OpeningBalance
,cb.CLICODE
,JBalance
,FBalance
,MBalance
order by cb.DIVName1
Thanks for any help.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

thava SSCrazy Points: 2275 More actions · Answer 1

i am not sure what you but here is my solution

SELECT c.CLIName1, c.CLICode, g.AGPName1 AS [GL Account Group], a.COAName1 AS [GL Account], d.DIVName1,

a.COACode2Code AS [GLChart Code 2], a.COACode2Name1 AS [GLChart Code 2 Name], t.CLTName1 AS

[Client Type],

SUM(CASE WHEN l.GNLFSMID IN (127) THEN l.GNLBalanceBase ELSE 0 END) AS 'Opening Balance',

SUM(CASE WHEN l.GNLFSMID IN (128) THEN l.GNLBalanceBase ELSE 0 END) AS 'January',

SUM(CASE WHEN l.GNLFSMID IN (129) THEN l.GNLBalanceBase ELSE 0 END) AS 'February',

SUM(CASE WHEN l.GNLFSMID IN (130) THEN l.GNLBalanceBase ELSE 0 END) AS 'March',

SUM(CASE WHEN l.GNLFSMID IN (128) THEN l.GNLBalanceBase ELSE 0 END) AS 'January',

SUM(CASE WHEN l.GNLFSMID IN (127, 128, 129, 130) THEN l.GNLBalanceBase ELSE 0 END) AS 'Closing 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 ('CA100', 'CA180', 'CA200', 'CA210')

GROUP BY

CLIName1, CLICode, AGPName1, COAName1, DIVName1, COACode2Code, COACode2Name1, CLTName1

Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

I'd guess that's way too complicated for the optimiser to deal with and you've got a plan which is far from ideal. Thava has the right idea - you should be able to write this as a single grab from those tables, but writing and testing could take a while. Here's a trick to get you out of your immediate fix: replace all those CTE's with #temp tables. Try with and without the indexes. If you are still experiencing performance problems, post the actual execution plan for one of the queries.

IF object_id('TempDB..#cteClosingBalance') IS NOT NULL DROP TABLE #cteClosingBalance

IF object_id('TempDB..#cteOpeningBalance') IS NOT NULL DROP TABLE #cteOpeningBalance

IF object_id('TempDB..#cteJan13') IS NOT NULL DROP TABLE #cteJan13

IF object_id('TempDB..#cteFeb13') IS NOT NULL DROP TABLE #cteFeb13

IF object_id('TempDB..#cteMar13') IS NOT NULL DROP TABLE #cteMar13

--;WITH

--cteClosingBalance (CLINAME1,CLICODE,AGPNAME1,COANAME1,DIVNAME1,COACODE2CODE,COACODE2NAME1,CLTNAME1,closingBalance)

--AS(

SELECT

c.CLIName1

,c.CLICode

,g.AGPName1 --as [GL Account Group]

,a.COAName1 --as [GL Account]

,d.DIVName1

,a.COACode2Code --as [GLChart Code 2]

,a.COACode2Name1 --as [GLChart Code 2 Name]

,t.CLTName1 --as [Client Type]

,closingBalance = SUM(l.GNLBalanceBase) --AS 'Closing Balance'

INTO #cteClosingBalance

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 ('CA100','CA180','CA200','CA210')

and l.GNLFSMID IN (127,128,129,130)

GROUP BY

CLIName1

,CLICode

,AGPName1

,COAName1

,DIVName1

,COACode2Code

,COACode2Name1

,CLTName1

--),

--cteOpeningBalance (DIVNAME1,OpeningBalance,CLTNAME1,COACODE2CODE)

--AS (

SELECT

d.DIVName1

,OpeningBalance = SUM(l.GNLBalanceBase) --AS 'Opening Balance'

,t.CLTName1

,a.COACode2Code --as [GLChart Code 2]

INTO #cteOpeningBalance

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 ('CA100','CA180','CA200','CA210')

and l.GNLFSMID IN (127)

GROUP BY

DIVName1

,COACode2Code

,t.CLTName1

--),

--cteJan13 (DIVNAME1,JBalance,CLTName1,COACODE2CODE)

--AS(

SELECT

d.DIVName1

,JBalance = SUM(l.GNLBalanceBase) --AS 'January'

,t.CLTName1

,a.COACode2Code --as [GLChart Code 2]

INTO #cteJan13

FROM DW.vwChartOfAccount a

inner join DW.vwGeneralLedger l

on a.COAID = l.GNLCOAID

inner join DW.vwDivision d

on l.GNLDIVID = d.DIVID

inner join DW.vwClient c

on l.GNLCLIID = c.CLIID

inner join DW.vwClientType t

on c.CLICLTID = t.CLTID

WHERE COACode2Code in ('CA100','CA180','CA200','CA210')

and l.GNLFSMID IN (128)

GROUP BY

DIVName1

,t.CLTName1

,COACode2Code

--),

--cteFeb13 (DIVNAME1,FBalance,CLTName1,COACODE2CODE)

--AS(

SELECT

d.DIVName1

,FBalance = SUM(l.GNLBalanceBase) AS 'February'

,t.CLTName1

,a.COACode2Code --as [GLChart Code 2]

INTO #cteFeb13

FROM DW.vwChartOfAccount a

inner join DW.vwGeneralLedger l

on a.COAID = l.GNLCOAID

inner join DW.vwDivision d

on l.GNLDIVID = d.DIVID

inner join DW.vwClient c

on l.GNLCLIID = c.CLIID

inner join DW.vwClientType t

on c.CLICLTID = t.CLTID

WHERE COACode2Code in ('CA100','CA180','CA200','CA210')

and l.GNLFSMID IN (129)

GROUP BY

DIVName1

,COACode2Code

,t.CLTName1

--),

--cteMar13 (DIVNAME1,MBalance,CLTName1,COACODE2CODE)

--AS(

SELECT

d.DIVName1

,MBalance = SUM(l.GNLBalanceBase) --AS 'March'

,t.CLTName1

,a.COACode2Code --as [GLChart Code 2]

INTO #cteMar13

FROM DW.vwChartOfAccount a

inner join DW.vwGeneralLedger l

on a.COAID = l.GNLCOAID

inner join DW.vwDivision d

on l.GNLDIVID = d.DIVID

inner join DW.vwClient c

on l.GNLCLIID = c.CLIID

inner join DW.vwClientType t

on c.CLICLTID = t.CLTID

WHERE COACode2Code in ('CA100','CA180','CA200','CA210')

and l.GNLFSMID IN (130)

GROUP BY

DIVName1

,COACode2Code

,t.CLTName1

--)

CREATE CLUSTERED INDEX cx_Stuff ON #cteClosingBalance (DIVNAME1,COACODE2CODE,CLTNAME1)

CREATE CLUSTERED INDEX cx_Stuff ON #cteOpeningBalance (DIVNAME1,COACODE2CODE,CLTNAME1)

CREATE CLUSTERED INDEX cx_Stuff ON #cteJan13 (CLTName1,DIVNAME1,COACODE2CODE)

CREATE CLUSTERED INDEX cx_Stuff ON #cteFeb13 (CLTName1,DIVNAME1,COACODE2CODE)

CREATE CLUSTERED INDEX cx_Stuff ON #cteMar13 (CLTName1,DIVNAME1,COACODE2CODE)

SELECT

cb.CLIName1 as [Client]

,cb.CLICode as [Client Code]

,cb.AGPName1 as [GL Account Group]

,cb.COAName1 as [GL Account]

,cb.closingBalanceas [Closing Balance]

,cb.COACode2Code as [GLChart Code 2]

,cb.COACode2Name1 as [GLChart Code 2 Name]

,cb.DIVName1 as [Division]

,cb.CLTName1 as [Client Type]

,ob.OpeningBalance as [Opening Balance]

,j.JBalance as [January]

,f.FBalance as [Feburary]

,m.MBalance as [March]

,SUM(ISNULL(ob.OpeningBalance,0) + ISNULL(j.JBalance,0) + ISNULL(f.FBalance,0) + ISNULL(m.MBalance,0)) as [March End]

FROM #cteClosingBalance cb

inner join #cteOpeningBalance ob

on ob.DIVNAME1 = cb.DIVNAME1

AND ob.COACODE2CODE = cb.COACODE2CODE

AND ob.CLTNAME1 = cb.CLTNAME1

left outer join #cteJan13 J

on j.CLTName1 = cb.CLTNAME1

and j.DIVNAME1 = cb.DIVNAME1

AND j.COACODE2CODE = cb.COACODE2CODE

left outer join #cteFeb13 f

on f.CLTName1 = cb.CLTNAME1

and f.DIVNAME1 = cb.DIVNAME1

AND f.COACODE2CODE = cb.COACODE2CODE

left outer join #cteMar13 m

on m.CLTName1 = cb.CLTNAME1

and m.DIVNAME1 = cb.DIVNAME1

AND m.COACODE2CODE = cb.COACODE2CODE

GROUP BY

cb.CLIName1

,cb.AGPName1

,cb.COAName1

,cb.DIVName1

,cb.COACode2Code

,cb.COACode2Name1

,cb.CLTName1

,cb.closingBalance

,cb.CLICODE

,ob.OpeningBalance

,JBalance

,FBalance

,MBalance

ORDER BY cb.DIVName1

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Stefan_G SSCertifiable Points: 6609 More actions · Answer 3

thava (8/15/2013)
SELECT c.CLIName1, c.CLICode, g.AGPName1 AS [GL Account Group], a.COAName1 AS [GL Account], d.DIVName1, a.COACode2Code AS [GLChart Code 2], a.COACode2Name1 AS [GLChart Code 2 Name], t.CLTName1 AS [Client Type], SUM(CASE WHEN l.GNLFSMID IN (127) THEN l.GNLBalanceBase ELSE 0 END) AS 'Opening Balance', SUM(CASE WHEN l.GNLFSMID IN (128) THEN l.GNLBalanceBase ELSE 0 END) AS 'January', SUM(CASE WHEN l.GNLFSMID IN (129) THEN l.GNLBalanceBase ELSE 0 END) AS 'February', SUM(CASE WHEN l.GNLFSMID IN (130) THEN l.GNLBalanceBase ELSE 0 END) AS 'March', SUM(CASE WHEN l.GNLFSMID IN (128) THEN l.GNLBalanceBase ELSE 0 END) AS 'January', SUM(CASE WHEN l.GNLFSMID IN (127, 128, 129, 130) THEN l.GNLBalanceBase ELSE 0 END) AS 'Closing 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 ('CA100', 'CA180', 'CA200', 'CA210') GROUP BY CLIName1, CLICode, AGPName1, COAName1, DIVName1, COACode2Code, COACode2Name1, CLTName1

This is a good idea - scan the large tables a single time.

To improve performance even more you should include a WHERE condition for GNLFSMID as well:

WHERE COACode2Code IN ('CA100', 'CA180', 'CA200', 'CA210')

AND

l.GNLFSMID IN (127, 128, 129, 130)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Stefan_G (8/16/2013)
thava (8/15/2013)
...
This is a good idea - scan the large tables a single time.
To improve performance even more you should include a WHERE condition for GNLFSMID as well:
WHERE COACode2Code IN ('CA100', 'CA180', 'CA200', 'CA210')
AND
l.GNLFSMID IN (127, 128, 129, 130)

Quite right too.

I'd be careful with piling on thava's suggestion for the time being though:

The table source names suggest views.

The aggregation level for the closing balance query is completely different to the others.

The opening balance query and the closing balance query have a table source which is missing from the three single-month queries.

At this point in time, the only queries which can be safely reconstructed in this manner are the monthly ones:

SELECT

d.DIVName1

,t.CLTName1

,a.COACode2Code

,JBalance = SUM(CASE WHEN l.GNLFSMID = 128 THEN l.GNLBalanceBase ELSE 0 END)

,FBalance = SUM(CASE WHEN l.GNLFSMID = 129 THEN l.GNLBalanceBase ELSE 0 END)

,MBalance = SUM(CASE WHEN l.GNLFSMID = 130 THEN l.GNLBalanceBase ELSE 0 END)

FROM DW.vwChartOfAccount a

inner join DW.vwGeneralLedger l

on a.COAID = l.GNLCOAID

inner join DW.vwDivision d

on l.GNLDIVID = d.DIVID

inner join DW.vwClient c

on l.GNLCLIID = c.CLIID

inner join DW.vwClientType t

on c.CLICLTID = t.CLTID

WHERE COACode2Code in ('CA100','CA180','CA200','CA210')

AND l.GNLFSMID IN (128,129,130)

GROUP BY

DIVName1

,COACode2Code

,t.CLTName1

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ringovski Hall of Fame Points: 3521 More actions · Answer 5

ChrisM@Work (8/16/2013)
Stefan_G (8/16/2013)
thava (8/15/2013)
...
This is a good idea - scan the large tables a single time.
To improve performance even more you should include a WHERE condition for GNLFSMID as well:
WHERE COACode2Code IN ('CA100', 'CA180', 'CA200', 'CA210')
AND
l.GNLFSMID IN (127, 128, 129, 130)
Quite right too.
I'd be careful with piling on thava's suggestion for the time being though:
The table source names suggest views.
The aggregation level for the closing balance query is completely different to the others.
The opening balance query and the closing balance query have a table source which is missing from the three single-month queries.
At this point in time, the only queries which can be safely reconstructed in this manner are the monthly ones:
SELECT
d.DIVName1
,t.CLTName1
,a.COACode2Code
,JBalance = SUM(CASE WHEN l.GNLFSMID = 128 THEN l.GNLBalanceBase ELSE 0 END)
,FBalance = SUM(CASE WHEN l.GNLFSMID = 129 THEN l.GNLBalanceBase ELSE 0 END)
,MBalance = SUM(CASE WHEN l.GNLFSMID = 130 THEN l.GNLBalanceBase ELSE 0 END)
FROM DW.vwChartOfAccount a
inner join DW.vwGeneralLedger l
on a.COAID = l.GNLCOAID
inner join DW.vwDivision d
on l.GNLDIVID = d.DIVID
inner join DW.vwClient c
on l.GNLCLIID = c.CLIID
inner join DW.vwClientType t
on c.CLICLTID = t.CLTID
WHERE COACode2Code in ('CA100','CA180','CA200','CA210')
AND l.GNLFSMID IN (128,129,130)
GROUP BY
DIVName1
,COACode2Code
,t.CLTName1

Thanks for the replies everyone.

Chris what do you mean by:

The aggregation level for the closing balance query is completely different to the others.

The opening balance query and the closing balance query have a table source which is missing from the three single-month queries.

Is there something wrong with the maths?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

ringovski (8/18/2013)
ChrisM@Work (8/16/2013)
Stefan_G (8/16/2013)
thava (8/15/2013)
...
This is a good idea - scan the large tables a single time.
To improve performance even more you should include a WHERE condition for GNLFSMID as well:
WHERE COACode2Code IN ('CA100', 'CA180', 'CA200', 'CA210')
AND
l.GNLFSMID IN (127, 128, 129, 130)
Quite right too.
I'd be careful with piling on thava's suggestion for the time being though:
The table source names suggest views.
The aggregation level for the closing balance query is completely different to the others.
The opening balance query and the closing balance query have a table source which is missing from the three single-month queries.
At this point in time, the only queries which can be safely reconstructed in this manner are the monthly ones:
SELECT
d.DIVName1
,t.CLTName1
,a.COACode2Code
,JBalance = SUM(CASE WHEN l.GNLFSMID = 128 THEN l.GNLBalanceBase ELSE 0 END)
,FBalance = SUM(CASE WHEN l.GNLFSMID = 129 THEN l.GNLBalanceBase ELSE 0 END)
,MBalance = SUM(CASE WHEN l.GNLFSMID = 130 THEN l.GNLBalanceBase ELSE 0 END)
FROM DW.vwChartOfAccount a
inner join DW.vwGeneralLedger l
on a.COAID = l.GNLCOAID
inner join DW.vwDivision d
on l.GNLDIVID = d.DIVID
inner join DW.vwClient c
on l.GNLCLIID = c.CLIID
inner join DW.vwClientType t
on c.CLICLTID = t.CLTID
WHERE COACode2Code in ('CA100','CA180','CA200','CA210')
AND l.GNLFSMID IN (128,129,130)
GROUP BY
DIVName1
,COACode2Code
,t.CLTName1
Thanks for the replies everyone.
Chris what do you mean by:
The aggregation level for the closing balance query is completely different to the others.
The opening balance query and the closing balance query have a table source which is missing from the three single-month queries.
Is there something wrong with the maths?

Closing balance query:

GROUP BY CLIName1,CLICode,AGPName1,COAName1,DIVName1,COACode2Code,COACode2Name1,CLTName1

Extra table in opening and closing balance query:

DW.vwChartOfAccountGroup

Opening balance query and monthly queries:

GROUP BY DIVName1,COACode2Code,CLTName1

It's not possible for anyone following this thread to determine if there's anything wrong with the maths, we don't have your data or your spec. What we can say is that changing the aggregation level of the closing balance query to match the other four queries is unlikely to give the same result as your original query.

An extra table in two of the queries is also a red flag; vwChartOfAccountGroup looks like a lookup table (view) in which case, if it has one row per row in vwChartOfAccount then it's a red herring, otherwise it's also changing the cardinality of the output sets making it more difficult i.e. more costly to combine all five queries into one.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden