Solved: Additional parameters needed for this JOIN query

I have the following SQL code attached. Everything works well with this code but there are a few additional parameters that I need in the output. The current output lists each activity by rows. Within those rows I need a column that show how many of that particular type of activity there is and how many unduplicated individuals that attended for that period. For example

ActivityName #of times #unduplicated #of individuals (duplicated) ActivityDate
Basketball 3 10 3 1/1/18
Basketball 3 10 10 1/2/18
Basketball 3 10 9 1/3/18
Volleyball 2 8 7 1/1/18
Volleyball 2 8 6 1/2/18

Hopefully this makes sense. You will notice in the desired output example above that the unduplicated number is repeated for one activity to show how many there were. The unduplicated column\, already being calculated, shows how many attended that specific date.

With CTE_Hours as
(select distinct AgencyID, Agency, Classification, Objectives, Advocate, AdvocacyType, ActivityID, RegID, cast(ActivityDate as Date) ActivityDate, Fiscal from tblOrgHours 
  where [Hours] > 0) select  H.Agency
       ,A.ActivityName
	   ,H.Classification
       ,H.ActivityDate
	   ,H.Objectives
	   ,H.Advocate
	   ,H.AdvocacyType
       ,Count(H.RegID) as [# individuals]
       ,SUM(CASE when R.AgeCurrent >= 11 and R.AgeCurrent <= 13 then 1 else 0 end) as [Ages 11-13]
       ,SUM(CASE when R.AgeCurrent >= 14 and R.AgeCurrent <= 18 then 1 else 0 end) as [Ages 14-18]
	   ,SUM(CASE when R.AgeCurrent >= 19 and R.AgeCurrent <= 24 then 1 else 0 end) as [Ages 19-24]
	   ,SUM(CASE when R.AgeCurrent >= 25 and R.AgeCurrent <= 65 then 1 else 0 end) as [Ages 25-65]
	   ,SUM(CASE when R.AgeCurrent >= 66 then 1 else 0 end) as [Ages 65+]
	   ,SUM(CASE when R.Board = '1' then 1 else 0 end) as [CommunityCommittee]
	   ,SUM(CASE when R.YouthCommittee = '1' then 1 else 0 end) as [YouthCommittee]
	   ,SUM(CASE when R.Parentcheck = '1' then 1 else 0 end) as [Parentcheck]
	   ,SUM(CASE when R.CommunityResident = '1' then 1 else 0 end) as [CommunityResident]
	   ,SUM(CASE when R.Race = 'Asian' then 1 else 0 end) as [Asian]
	   ,SUM(CASE when R.Race = 'African-American' then 1 else 0 end) as [African-American]
	   ,SUM(CASE when R.Race = 'Caucasian' then 1 else 0 end) as [Caucasian]
	   ,SUM(CASE when R.Race = 'Native-American' then 1 else 0 end) as [Native-American]
	   ,SUM(CASE when R.Race = 'Multi-Racial' then 1 else 0 end) as [Multi-Racial]
	   ,SUM(CASE when R.Race = 'Latino-Hispanic' then 1 else 0 end) as [Latino-Hispanic]
       ,SUM(CASE when R.Gender = 'Male' then 1 else 0 end) as [Male]
       ,SUM(CASE when R.Gender = 'Female' then 1 else 0 end) as [Female]
	   ,SUM(CASE when R.Sector = 'Business' then 1 else 0 end) as [Business]
	   ,SUM(CASE when R.Sector = 'Civic-Volunteer' then 1 else 0 end) as [Civic-Volunteer]
	   ,SUM(CASE when R.Sector = 'Community Resident' then 1 else 0 end) as [Community Resident]
	   ,SUM(CASE when R.Sector = 'Faith Based' then 1 else 0 end) as [Faith Based]
	   ,SUM(CASE when R.Sector = 'Healthcare' then 1 else 0 end) as [Healthcare]
	   ,SUM(CASE when R.Sector = 'Human Support Agencies' then 1 else 0 end) as [Human Support Agencies]
	   ,SUM(CASE when R.Sector = 'Law Enforcement' then 1 else 0 end) as [Law Enforcement]
	   ,SUM(CASE when R.Sector = 'Local Government' then 1 else 0 end) as [Local Government]
	   ,SUM(CASE when R.Sector = 'Media' then 1 else 0 end) as [Media]
	   ,SUM(CASE when R.Sector = 'Parent or Guardian' then 1 else 0 end) as [Parent or Guardian]
	   ,SUM(CASE when R.Sector = 'Philanthropic' then 1 else 0 end) as [Philanthropic]
	   ,SUM(CASE when R.Sector = 'Schools' then 1 else 0 end) as [Schools]
	   ,SUM(CASE when R.Sector = 'Youth' then 1 else 0 end) as [Youth]

from CTE_Hours H                                                                    -- notice how we can now select from the named query CTE_Hours
inner join tblOrgRegistrations R on H.Regid = R.RegID
inner join tblOrgActivities A on H.ActivityID = A.ActivityID

where R.AgeCurrent between 11 and 999
And h.Agency = 'Administrator'
And H.Fiscal = 2018
And H.ActivityDate >= '1/1/17'
And H.ActivityDate < '12/31/17'

group by H.Agency,H.Classification, A.ActivityName,H.ActivityDate, H.Objectives, H.Advocate, H.AdvocacyType
Order by 1,2,3

Open in new window

tblOrgRegistrations--1-.xlsx
tblOrgActivities--1-.xlsx
tblOrgHours.xlsx