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.

tblOrgActivities--1-.xlsx

tblOrgHours.xlsx

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
```

tblOrgRegistrations--1-.xlsxtblOrgActivities--1-.xlsx

tblOrgHours.xlsx

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert

See if this solution works for you by signing up for a 7 day free trial.

Unlock 1 Answer and 18 Comments.

Try for 7 days”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

Our community of experts have been thoroughly vetted for their expertise and industry experience.

The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.