I am trying to create a new column that has the count of distinct service codes per Client ID. My last column in the second query has all 0s.
SELECT SERVICE_CODE, COUNT(SERVICE_CODE) AS SERVICE_COUNTS
GROUP BY SERVICE_CODE
SELECT T.*, COALESCE(D.SERVICE_COUNTS, 0)
FROM #TAPMAR T
SELECT SERVICE_CODE, COUNT(*) AS SERVICE_COUNTS
GROUP BY SERVICE_CODE) D ON D.SERVICE_CODE = T.CASE_NUM;
The problem is probably the
join . . . I don't see why
service_code should match
I would suggest window functions in any case:
SELECT T.*, COUNT(*) OVER (PARTITION BY SERVICE_CODE) as NumOnService, COUNT(*) OVER (PARTITION BY CASE_NUM) as NumOnCase FROM #TAPMAR T;
This adds the count fro both service and case. I'm not sure which one you really want.