WITH MEMBERS AS
(
SELECT DISTINCT MEM.ID_MEMBER
,MEM.MEMBNO
,MEM.BTHDAT
,MEM.PARENT
,MEM.COUNTY AS COUNTY_ID
,COUNTY.COUNTY
,MEM.SEXCOD AS GENDER
,MEM.EFFDAT
,MEM.EXPDAT
FROM MHS.DBO.DW_MEMBER MEM
	LEFT JOIN MHS.DBO.COUNTYLOOKUP COUNTY ON MEM.COUNTY = COUNTY.CODE
WHERE MEM.EXPDAT = 99991231
)
,COUNTS AS
(
SELECT MEMBERS.COUNTY
,MEMBERS.GENDER
,COUNT(DISTINCT MEMBERS.MEMBNO) AS ACTIVE_MEMBERS
FROM MEMBERS
GROUP BY MEMBERS.COUNTY
,MEMBERS.GENDER
)


SELECT COUNTS.COUNTY
,COUNTS.GENDER
,SUM(COUNTS.ACTIVE_MEMBERS) AS ACTIVE_MEMBERS
FROM COUNTS
GROUP BY ROLLUP (COUNTS.COUNTY
,COUNTS.GENDER)