My first thought with this was to introduce a second CTE to assign the groupings to the rows and therefore make it easier to use a GROUP BY. E.g.
DECLARE @ZMONTH INT = 5
DECLARE @ZYEAR INT = 2015
;with cte as
(
SELECT CAST((INT_ARREARS+PRIN_ARREARS)/DEDUCT AS INT) as COMPS ,
INT_ARREARS+PRIN_ARREARS AS TOTS,
IDNO,AC_BALANCE,LOANNUMBER,CUSTOMER
FROM HLOANS
where DEDUCT >0 and INT_ARREARS+PRIN_ARREARS>0
AND CAST((INT_ARREARS+PRIN_ARREARS)/DEDUCT AS INT) >0
AND CMONTH=@ZMONTH AND CYEAR=@ZYEAR
), cte2 as
(
SELECT CUSTOMER, AC_BALANCE,
CASE WHEN COMPS BETWEEN 1 AND 3 THEN CAST('1-3' AS Varchar(8))
WHEN COMPS BETWEEN 4 AND 6 THEN '4-6'
ELSE 'The Rest' END AS [Grouping],
TOTS
FROM cte
)
SELECT CUSTOMER, SUM(AC_BALANCE) as [LOAN BALANCE], [Grouping],
SUM(TOTS) AS TOTS,
OLEM = CASE WHEN [Grouping] = '1-3' THEN SUM(TOTS) ELSE 0.0 END,
[SUB-STANDARD] = CASE WHEN [Grouping] = '4-6' THEN SUM(TOTS) ELSE 0.0 END
FROM cte2
GROUP BY CUSTOMER, [Grouping]
ORDER BY [Grouping]
An alternative approach would be to have the groupings in a separate table which can then be part of a simple JOIN. I've used a TABLE variable here but it could just as easily be a temporary table or even a permanent table on the database.
DECLARE @ZMONTH INT = 5
DECLARE @ZYEAR INT = 2015
DECLARE @Groupings TABLE (comp int, title varchar(30))
DECLARE @maxComp int = (select CAST(MAX((INT_ARREARS+PRIN_ARREARS)/DEDUCT) AS INT) FROM HLOANS)
;WITH q AS
(
SELECT 1 AS num
UNION ALL
SELECT num + 1
FROM q
WHERE num < @maxComp
)
INSERT INTO @Groupings
SELECT num, CASE WHEN num BETWEEN 1 AND 3 THEN CAST('1-3' AS Varchar(8))
WHEN num BETWEEN 4 AND 6 THEN '4-6'
ELSE 'The Rest' END AS [Grouping]
FROM q
;with cte as
(
SELECT CAST((INT_ARREARS+PRIN_ARREARS)/DEDUCT AS INT) as COMPS ,
INT_ARREARS+PRIN_ARREARS AS TOTS,
IDNO,AC_BALANCE,LOANNUMBER,CUSTOMER
FROM HLOANS
where DEDUCT >0 and INT_ARREARS+PRIN_ARREARS>0
AND CAST((INT_ARREARS+PRIN_ARREARS)/DEDUCT AS INT) >0
AND CMONTH=@ZMONTH AND CYEAR=@ZYEAR
)
SELECT CUSTOMER, SUM(AC_BALANCE) as [LOAN BALANCE], g.title AS [Grouping],
SUM(TOTS) AS TOTS,
OLEM = CASE WHEN G.[title] = '1-3' THEN SUM(TOTS) ELSE 0.0 END,
[SUB-STANDARD] = CASE WHEN G.title= '4-6' THEN SUM(TOTS) ELSE 0.0 END
FROM cte
INNER JOIN @Groupings G ON cte.COMPS = g.comp
GROUP BY CUSTOMER, G.title
ORDER BY G.title
The first bit (
q
) is a recursive CTE that just generates the numbers 1 through to the maximum possible value of
COMP
and gives each of those numbers the title of the grouping that it falls into. My test data produced
num title
1 1-3
2 1-3
3 1-3
4 4-6
5 4-6
The rest of the query is more or less the same as your original query but with a straight-forward JOIN to that list of numbers. It's then very easy to GROUP by the title from the temporary table rather than having CASE statements in the GROUP BY and ORDER BY clauses.
This should run a little faster than the alternative as all of the character handling has been taken out of the main query and is only "calculated" once per possible value.