Use of SUM and COUNT with GROUP BY

OK, so we need a different approach. We need a table of numbers, a concept that I discuss here:

http://ift.tt/1tJi55N

(Only read down to the next header.)

We join the numbers to temp table with BETWEEN over the numbers. Then we count the distinct combination of member ID and number.

We also need to make an adjustment how to build the string for the DISTINCT. I initially assumed that your columns were integer, why I used str() which produces a right-adjusted fixed-length string of 10 characters. (The default, you can specify a different width as the second parameter.) But str() expects a float value as input, and will fail if for instance member id would be non-numeric. We cannot just concatenate the strings, since in that case (MemberID, SubID) = ('12345', '61') would be the same as ('123456', '1'). So we must convert to char to get fixed length. All that said, and some more test data added, here is a solution:

CREATE TABLE #Test
(
     [State] CHAR(2),
     DOB DATE,
     StartDt DATE,
     EndDt DATE,
     [Desc] VARCHAR(8),
     Category CHAR(1),
     MemberID VARCHAR(10),
     SubID VARCHAR(2),
     Code VARCHAR(5)
)

INSERT INTO #Test
VALUES
('NC', '20130130', '20140101', '20140120', 'Child', 'B', '123456', '2',    '38'),
('NC', '20130130', '20140121', '20140131', 'Child', 'B', '123456', '2',    '39'),
('NC', '20130130', '20140201', '20140531', 'Child', 'B', '123456', '2',    '38'),
('NC', '20130130', '20140401', '20140613', 'Child', 'B', '123456', '2',    '39'),
('NC', '20130130', '20140601', '20140731', 'Child', 'B', '123456', '2',    '38'),
('NC', '20130130', '20140614', '20140731', 'Child', 'B', '123456', '2',    '39'),
('NC', '20130129', '20140601', '20140731', 'Child', 'B', '9123456', '1',    '38'),
('NC', '20130129', '20140614', '20140831', 'Child', 'B', '9123456', '1',    '39'),
('NC', '20140117', '20140101', '20140731', 'Infant', 'S', '456789', '1', '49'),
('NC', '20140204', '20140201', '20140731', 'Infant', 'S', '246376', '3', '49')

SELECT * FROM #Test ORDER BY MemberID, StartDt, EndDt

SELECT
         [State]
     , [Desc]
     , Category
     , COUNT(DISTINCT convert(char(10), MemberID) +
                                        convert(char(2), SubID) +
                                        convert(char(5), Code)) AS TotalCnt
     , COUNT(DISTINCT convert(char(10), MemberID) +
                                        convert(char(2), SubID) +
                                        str(N.Number)) AS CalMonths
FROM     #Test t
JOIN     Numbers N ON N.Number BETWEEN
                         datediff(MONTH, '19000101', t.StartDt) AND
                         datediff(MONTH, '19000101', t.EndDt)
GROUP BY [State], [Desc], Category

go
DROP TABLE #Test

---

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se