Hi,
First of all, you said that you want Due No's like 1 to 12, 13 to 24, etc., but in your expected result [Due No] is 12-24, not 13-24...
Here's my sample (1-12, 13-24, 25-36):
CREATE TABLE #TempData
([Collection] INT,
[advisor_Code] VARCHAR(50),
[Plan_name] VARCHAR(50),
[advisor_id] INT,
[Due_No] INT);
INSERT INTO #TempData ([Collection], [advisor_Code], [Plan_name], [advisor_id], [Due_No])
VALUES (3200, '1010200022', 'GIFDL - 36', 127, 1),
(1000, '1010200022', 'GIFDL - 36', 127, 2),
(1000, '1010200022', 'GIFDL - 36', 127, 3),
(1000, '1010200022', 'GIFDL - 36', 127, 4),
(5000, '1010200022', 'GIFDL - 36', 127, 12),
(5000, '1010200022', 'GIFDL - 36', 127, 13);
;WITH Ranges
AS (SELECT 1 AS [RangeFrom],
12 AS [RangeTo]
UNION ALL
SELECT ( [RangeTo] + 1 ) AS [RangeFrom],
( [RangeTo] + 12 ) AS [RangeTo]
FROM Ranges
WHERE [RangeTo] < 36)
SELECT SUM([Collection]) AS [Collection],
[advisor_Code],
[Plan_name],
[advisor_id],
CAST([RangeFrom] AS VARCHAR(10)) + '-' + CAST([RangeTo] AS VARCHAR(10)) AS [Due_No]
FROM Ranges AS r
LEFT OUTER JOIN #TempData AS t
ON t.[Due_No] >= r.[RangeFrom]
AND t.[Due_No] <= r.[RangeTo]
GROUP BY [RangeFrom],
[RangeTo],
[advisor_Code],
[Plan_name],
[advisor_id]
OPTION (MAXRECURSION 32767);
DROP TABLE #TempData;
Max [Due_No] is limited to 36 (
WHERE [RangeTo] < 36
). Instead of CTE you can use a table or table variable.
Result:
Collection advisor_Code Plan_name advisor_id Due_No
11200 1010200022 GIFDL - 36 127 1-12
5000 1010200022 GIFDL - 36 127 13-24
NULL NULL NULL NULL 25-36