Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

In the following query, I need to acquire an unduplicated count of all H.RegID across different age groups. Currently lines 21 -24 provide a basic count of students in EACH activity. What I additionally need is a distinct count ACROSS all the activities for each of those age groups also. How can I accomplish this?

```
With CTE_Hours as
( select distinct H.AgencyID, H.Agency,H.Hours, H.Classification, H.Objectives,H.ActivityType,H.ActivityOther,H.Narrative,H.Outcome,H.Duration,H.Frequency,H.Strategy,H.Need,H.NeedOther, H.ActivityID, H.RegID, cast(H.ActivityDate as Date) ActivityDate, H.Fiscal
from tblOrgHours H
inner join tblOrgRegistrations R on H.Regid = R.RegID and R.AgeCurrent between 11 and 999
where H.[Hours] > 0
And H.Agency = 'Cicero - area project'
And H.Fiscal = 2018
And H.ActivityDate >= '20170101'
And H.ActivityDate < '20180101'
)
select H.Agency
,A.ActivityName
,H.Classification
,H.ActivityDate
,H.Objectives
,H.Hours
,count(A.ActivityName) over (partition by H.Agency,A.ActivityName order by A.activityName) as [ActivityCount]
,max(I.[Tot Individuals for Activity]) [Tot Individuals for Activity]
,Count(H.RegID) as [# individuals]
,H.[ActivityType],H.ActivityOther,H.Narrative,H.Outcome,H.Duration,H.Frequency,H.Strategy,H.Need,H.NeedOther
,SUM(CASE when R.AgeCurrent >= 11 and R.AgeCurrent <= 12 then 1 else 0 end) as [Ages 11-12]
,SUM(CASE when R.AgeCurrent >= 13 and R.AgeCurrent <= 17 then 1 else 0 end) as [Ages 13-17]
,SUM(CASE when R.AgeCurrent >= 18 and R.AgeCurrent <= 20 then 1 else 0 end) as [Ages 18-20]
,SUM(CASE when R.AgeCurrent >= 21 and R.AgeCurrent <= 24 then 1 else 0 end) as [Ages 21-24]
,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
inner join (select AgencyID, ActivityID, count(distinct Regid) as [Tot Individuals for Activity] from CTE_Hours group by AgencyID,Activityid) I on I.AgencyID = H.AgencyID and I.Activityid = H.ActivityId
group by H.Agency,H.Classification,H.Hours, A.ActivityName,H.ActivityDate, H.Objectives,H.ActivityType,H.ActivityOther,H.Narrative,H.Outcome,H.Duration,H.Frequency,H.Strategy,H.Need,H.NeedOther
Order by H.Agency, H.Classification,A.ActivityName, H.ActivityDate
```

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with premium.
Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Might want to start re-thinking the approach a bit and maybe include the 1 on 1 relationship to tblordregidtrations in the first part of the CTE_Hours, or maybe some embedded CTE's to evaluate hours + registrations then add in the activities after...

Anyway, this seems to work, however not ideal because we need to revisit cte_hours yet again....

```
With CTE_Hours as
( select distinct H.AgencyID, H.Agency, H.Classification, H.Objectives, H.Advocate, H.AdvocacyType, H.ActivityID, H.RegID, R.AgeCurrent, cast(H.ActivityDate as Date) ActivityDate, H.Fiscal
from tblOrgHours H
inner join tblOrgRegistrations R on H.Regid = R.RegID and R.AgeCurrent between 11 and 999
where H.[Hours] > 0
And H.Agency = 'Administrator'
And H.Fiscal = 2018
And H.ActivityDate >= '20170101'
And H.ActivityDate < '20180101'
)
select H.Agency
,A.ActivityName
,H.Classification
,H.ActivityDate
,H.Objectives
,H.Advocate
,H.AdvocacyType
,count(A.ActivityName) over (partition by H.Agency,A.ActivityName order by A.activityName) as [Tot Activities for Agency]
,max(I.[Tot Individuals for Activity]) [Tot Individuals for Activity]
,MAX(AGE.[Tot Ages 11-13]) as [Tot Ages 11-13]
,max(AGE.[Tot Ages 14-18]) as [Tot Ages 14-18]
,max(AGE.[Tot Ages 19-24]) as [Tot Ages 19-24]
,max(AGE.[Tot Ages 25-65]) as [Tot Ages 25-65]
,max(AGE.[Tot Ages 65+]) as [Tot Ages 65+]
,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
inner join (select AgencyID, ActivityID, count(distinct Regid) as [Tot Individuals for Activity] from CTE_Hours group by AgencyID,Activityid) I on I.AgencyID = H.AgencyID and I.Activityid = H.ActivityId
cross apply (select AgencyID
,sum(CASE when AgeCurrent >= 11 and AgeCurrent <= 13 then 1 else 0 end) as [Tot Ages 11-13]
,sum(CASE when AgeCurrent >= 14 and AgeCurrent <= 18 then 1 else 0 end) as [Tot Ages 14-18]
,sum(CASE when AgeCurrent >= 19 and AgeCurrent <= 24 then 1 else 0 end) as [Tot Ages 19-24]
,sum(CASE when AgeCurrent >= 25 and AgeCurrent <= 65 then 1 else 0 end) as [Tot Ages 25-65]
,sum(CASE when AgeCurrent >= 66 then 1 else 0 end) as [Tot Ages 65+]
from (select distinct agencyid,regid,agecurrent from CTE_Hours) d
where d.agencyid = h.agencyid
group by AgencyID) AGE
group by H.Agency,H.Classification, A.ActivityName,H.ActivityDate, H.Objectives, H.Advocate, H.AdvocacyType
Order by 1,2,3
```

So I ran the code and here are the results. You'll see that the some of the the Tot Ages 11-13 and Tot Ages 14-18 don't align with the [Tot Individuals for Activity]

Hmm. This is a tough one.

Output-from-code-from-Mark.xlsx

Had a look at your spreadsheet, and there are physical alignment problems - which I corrected (attached) - is that what you mean

Output-from-code-from-Mark.xlsx

```
With CTE_Hours as
( select distinct H.AgencyID, H.Agency, H.Classification, H.Objectives, H.Advocate, H.AdvocacyType, H.ActivityID, H.RegID, R.AgeCurrent, cast(H.ActivityDate as Date) ActivityDate, H.Fiscal
from tblOrgHours H
inner join tblOrgRegistrations R on H.Regid = R.RegID and R.AgeCurrent between 11 and 999
where H.[Hours] > 0
And H.Agency = 'Administrator'
And H.Fiscal = 2018
And H.ActivityDate >= '20170101'
And H.ActivityDate < '20180101'
)
select H.Agency
,A.ActivityName
,H.Classification
,H.ActivityDate
,H.Objectives
,H.Advocate
,H.AdvocacyType
,count(A.ActivityName) over (partition by H.Agency,A.ActivityName order by A.activityName) as [Tot Activities for Agency]
,max(I.[Tot Individuals for Activity]) [Tot Individuals for Activity]
,MAX(I.[Tot Ages 11-13]) as [Tot Ages 11-13]
,max(I.[Tot Ages 14-18]) as [Tot Ages 14-18]
,max(I.[Tot Ages 19-24]) as [Tot Ages 19-24]
,max(I.[Tot Ages 25-65]) as [Tot Ages 25-65]
,max(I.[Tot Ages 65+]) as [Tot Ages 65+]
,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
inner join (select AgencyID, ActivityID, count(distinct Regid) as [Tot Individuals for Activity]
,sum(CASE when AgeCurrent >= 11 and AgeCurrent <= 13 then 1 else 0 end) as [Tot Ages 11-13]
,sum(CASE when AgeCurrent >= 14 and AgeCurrent <= 18 then 1 else 0 end) as [Tot Ages 14-18]
,sum(CASE when AgeCurrent >= 19 and AgeCurrent <= 24 then 1 else 0 end) as [Tot Ages 19-24]
,sum(CASE when AgeCurrent >= 25 and AgeCurrent <= 65 then 1 else 0 end) as [Tot Ages 25-65]
,sum(CASE when AgeCurrent >= 66 then 1 else 0 end) as [Tot Ages 65+]
from CTE_Hours group by AgencyID,Activityid) I on I.AgencyID = H.AgencyID and I.Activityid = H.ActivityId
group by H.Agency,H.Classification, A.ActivityName,H.ActivityDate, H.Objectives, H.Advocate, H.AdvocacyType
Order by 1,2,3
```

As I understand the code now, [Tot Individuals for Activity] is a distinct count of all individuals for that activity across all the dates, correct?

So then, the [Tot Ages 11-13], etc.... would have to total to the number in the [Tot Individuals for Activity] column. In these prior examples, it does not do that.

Correct

>> So then, the [Tot Ages 11-13], etc.... would have to total to the number in the [Tot Individuals for Activity]

Also correct in the last post - prior post was regardless of activity. And the tot age groups do add up in my testing - albeit limited matching entries.

Is it possible to get a fresh copy of spreadsheets ?

Here is one last attempt before we have to get fresh data samples it does rely on a 1:1 relationship from REGID to registrations

```
With CTE_Hours as
( select distinct H.AgencyID, H.Agency, isnull(H.Classification,'') Classification, isnull(H.Objectives,'') Objectives, isnull(H.Advocate,'') Advocate, isnull(H.AdvocacyType,'') AdvocacyType, H.ActivityID, H.RegID
, R.AgeCurrent, R.Board, R.YouthCommittee, R.Parentcheck, R.CommunityResident, R.Race, R.Gender, R.Sector
, cast(H.ActivityDate as Date) ActivityDate, H.Fiscal
from tblOrgHours H
inner join tblOrgRegistrations R on H.Regid = R.RegID and R.AgeCurrent between 11 and 999
where H.[Hours] > 0
And H.Agency = 'Administrator'
And H.Fiscal = 2018
And H.ActivityDate >= '20170101'
And H.ActivityDate < '20180101'
)
select H.Agency
,A.ActivityName
,H.Classification
,H.ActivityDate
,H.Objectives
,H.Advocate
,H.AdvocacyType
,count(A.ActivityName) over (partition by H.Agency,A.ActivityName order by A.activityName) as [Tot Activities for Agency]
,max(I.[Tot Individuals for Activity]) [Tot Individuals for Activity]
,MAX(I.[Tot Ages 11-13]) as [Tot Ages 11-13]
,max(I.[Tot Ages 14-18]) as [Tot Ages 14-18]
,max(I.[Tot Ages 19-24]) as [Tot Ages 19-24]
,max(I.[Tot Ages 25-65]) as [Tot Ages 25-65]
,max(I.[Tot Ages 65+]) as [Tot Ages 65+]
,Count(H.RegID) as [# individuals]
,SUM(CASE when H.AgeCurrent >= 11 and H.AgeCurrent <= 13 then 1 else 0 end) as [Ages 11-13]
,SUM(CASE when H.AgeCurrent >= 14 and H.AgeCurrent <= 18 then 1 else 0 end) as [Ages 14-18]
,SUM(CASE when H.AgeCurrent >= 19 and H.AgeCurrent <= 24 then 1 else 0 end) as [Ages 19-24]
,SUM(CASE when H.AgeCurrent >= 25 and H.AgeCurrent <= 65 then 1 else 0 end) as [Ages 25-65]
,SUM(CASE when H.AgeCurrent >= 66 then 1 else 0 end) as [Ages 65+]
,SUM(CASE when H.Board = '1' then 1 else 0 end) as [CommunityCommittee]
,SUM(CASE when H.YouthCommittee = '1' then 1 else 0 end) as [YouthCommittee]
,SUM(CASE when H.Parentcheck = '1' then 1 else 0 end) as [Parentcheck]
,SUM(CASE when H.CommunityResident = '1' then 1 else 0 end) as [CommunityResident]
,SUM(CASE when H.Race = 'Asian' then 1 else 0 end) as [Asian]
,SUM(CASE when H.Race = 'African-American' then 1 else 0 end) as [African-American]
,SUM(CASE when H.Race = 'Caucasian' then 1 else 0 end) as [Caucasian]
,SUM(CASE when H.Race = 'Native-American' then 1 else 0 end) as [Native-American]
,SUM(CASE when H.Race = 'Multi-Racial' then 1 else 0 end) as [Multi-Racial]
,SUM(CASE when H.Race = 'Latino-Hispanic' then 1 else 0 end) as [Latino-Hispanic]
,SUM(CASE when H.Gender = 'Male' then 1 else 0 end) as [Male]
,SUM(CASE when H.Gender = 'Female' then 1 else 0 end) as [Female]
,SUM(CASE when H.Sector = 'Business' then 1 else 0 end) as [Business]
,SUM(CASE when H.Sector = 'Civic-Volunteer' then 1 else 0 end) as [Civic-Volunteer]
,SUM(CASE when H.Sector = 'Community Resident' then 1 else 0 end) as [Community Resident]
,SUM(CASE when H.Sector = 'Faith Based' then 1 else 0 end) as [Faith Based]
,SUM(CASE when H.Sector = 'Healthcare' then 1 else 0 end) as [Healthcare]
,SUM(CASE when H.Sector = 'Human Support Agencies' then 1 else 0 end) as [Human Support Agencies]
,SUM(CASE when H.Sector = 'Law Enforcement' then 1 else 0 end) as [Law Enforcement]
,SUM(CASE when H.Sector = 'Local Government' then 1 else 0 end) as [Local Government]
,SUM(CASE when H.Sector = 'Media' then 1 else 0 end) as [Media]
,SUM(CASE when H.Sector = 'Parent or Guardian' then 1 else 0 end) as [Parent or Guardian]
,SUM(CASE when H.Sector = 'Philanthropic' then 1 else 0 end) as [Philanthropic]
,SUM(CASE when H.Sector = 'Schools' then 1 else 0 end) as [Schools]
,SUM(CASE when H.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 tblOrgActivities A on H.ActivityID = A.ActivityID
cross apply (select AgencyID, ActivityID, count(Regid) as [Tot Individuals for Activity]
,sum(CASE when AgeCurrent >= 11 and AgeCurrent <= 13 then 1 else 0 end) as [Tot Ages 11-13]
,sum(CASE when AgeCurrent >= 14 and AgeCurrent <= 18 then 1 else 0 end) as [Tot Ages 14-18]
,sum(CASE when AgeCurrent >= 19 and AgeCurrent <= 24 then 1 else 0 end) as [Tot Ages 19-24]
,sum(CASE when AgeCurrent >= 25 and AgeCurrent <= 65 then 1 else 0 end) as [Tot Ages 25-65]
,sum(CASE when AgeCurrent >= 66 then 1 else 0 end) as [Tot Ages 65+]
from (select distinct agencyid, activityID, regid, agecurrent from CTE_Hours) d
where d.AgencyID = H.AgencyID and d.Activityid = H.ActivityId
group by agencyID, ActivityID) I
group by H.Agency,H.Classification, A.ActivityName,H.ActivityDate, H.Objectives, H.Advocate, H.AdvocacyType
Order by 1,2,3
```

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial```
With CTE_Hours as
( select distinct H.AgencyID, H.Agency,H.Hours, H.Classification, H.Objectives,H.ActivityType,H.ActivityOther,H.Narrative,H.Outcome,H.Duration,H.Frequency,H.Strategy,H.Need,H.NeedOther, H.ActivityID, H.RegID, cast(H.ActivityDate as Date) ActivityDate, H.Fiscal
from tblOrgHours H
inner join tblOrgRegistrations R on H.Regid = R.RegID and R.AgeCurrent between 11 and 999
where H.[Hours] > 0
And H.Agency = 'Cicero - area project'
And H.Fiscal = 2018
And H.ActivityDate >= '20170101'
And H.ActivityDate < '20180601'
)
select H.Agency
,A.ActivityName
,H.Classification
,H.ActivityDate
,H.Objectives
,H.Hours
,count(A.ActivityName) over (partition by H.Agency,A.ActivityName order by A.activityName) as [ActivityCount]
,max(I.[Tot Individuals for Activity]) [Tot Individuals for Activity]
,Count(H.RegID) as [# individuals]
,H.[ActivityType],H.ActivityOther,H.Narrative,H.Outcome,H.Duration,H.Frequency,H.Strategy,H.Need,H.NeedOther
,SUM(CASE when R.AgeCurrent >= 11 and R.AgeCurrent <= 12 then 1 else 0 end) as [Ages 11-12]
,SUM(CASE when R.AgeCurrent >= 13 and R.AgeCurrent <= 17 then 1 else 0 end) as [Ages 13-17]
,SUM(CASE when R.AgeCurrent >= 18 and R.AgeCurrent <= 20 then 1 else 0 end) as [Ages 18-20]
,SUM(CASE when R.AgeCurrent >= 21 and R.AgeCurrent <= 24 then 1 else 0 end) as [Ages 21-24]
,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
inner join (select AgencyID, ActivityID, count(distinct Regid) as [Tot Individuals for Activity] from CTE_Hours group by AgencyID,Activityid) I on I.AgencyID = H.AgencyID and I.Activityid = H.ActivityId
group by H.Agency,H.Classification,H.Hours, A.ActivityName,H.ActivityDate, H.Objectives,H.ActivityType,H.ActivityOther,H.Narrative,H.Outcome,H.Duration,H.Frequency,H.Strategy,H.Need,H.NeedOther
Order by H.Agency, H.Classification,A.ActivityName, H.ActivityDate
```

The CTE_Hours definition

1) added isnull() checking to the columns e.g. H.Classification, H.Objectives, H.Advocate, H.AdvocacyType, etc

-- add the same type of handling for H.ActivityType, H.ActivityOther, H.Narrative, H.Outcome, H.Duration, H.Frequency, H.Strategy, H.Need, H.NeedOther,

2) added the columns to be used from tblOrgRegistrations e.g. R.Sector, R.Gender

-- added, R.Board, R.YouthCommittee, R.Parentcheck, R.CommunityResident, R.Race, R.Gender, R.Sector

The Select from CTE_Hours

1) removed the redundant join to tblOrgRegistrations - already done in the CTE_Hours definition

2) changed the table alias from R. to H. for the selected columns that used to be R.

3) changed inner join to derived table I to a cross apply

4) changed the derived table I so we can get all we need from CTE_Hours

5) the group by should either have those (missing) columns added to the select, or, remove any columns not specifically selected.

Summary

I think given the differences, is to use mine and add in the otherwise missing columns if, and only if, they are going to be used following the CTE_Hours definition in the select and if being selected, in the group by.

Does that help explain the impacts/changes ?

Here is the adjusted code and it seems to work. If you could take a pass with your eyes that would be great.

```
With CTE_Hours as
( select distinct H.AgencyID, H.Agency,H.Hours, isnull(H.Classification,'') Classification, isnull(H.Objectives,'') Objectives,H.ActivityType,H.ActivityOther,H.Narrative,H.Outcome,H.Duration,H.Frequency,H.Strategy,H.Need,H.NeedOther, H.ActivityID, H.RegID
, R.AgeCurrent, R.Board, R.YouthCommittee, R.Parentcheck, R.CommunityResident, R.Race, R.Gender, R.Sector
, cast(H.ActivityDate as Date) ActivityDate, H.Fiscal
from tblOrgHours H
inner join tblOrgRegistrations R on H.Regid = R.RegID and R.AgeCurrent between 11 and 999
where H.[Hours] > 0
And H.Agency = 'Cicero - Area Project'
And H.Fiscal = 2018
And H.ActivityDate >= '20170101'
And H.ActivityDate < '20180601'
)
select H.Agency
,A.ActivityName
,H.Classification
,H.ActivityDate
,H.Objectives
,H.Hours
,count(A.ActivityName) over (partition by H.Agency,A.ActivityName order by A.activityName) as [Tot Activities for Agency]
,max(I.[Tot Individuals for Activity]) [Tot Individuals for Activity]
,MAX(I.[Tot Ages 11-13]) as [Tot Ages 11-13]
,max(I.[Tot Ages 14-18]) as [Tot Ages 14-18]
,max(I.[Tot Ages 19-24]) as [Tot Ages 19-24]
,max(I.[Tot Ages 25-65]) as [Tot Ages 25-65]
,max(I.[Tot Ages 65+]) as [Tot Ages 65+]
,Count(H.RegID) as [# individuals]
,SUM(CASE when H.AgeCurrent >= 11 and H.AgeCurrent <= 13 then 1 else 0 end) as [Ages 11-13]
,SUM(CASE when H.AgeCurrent >= 14 and H.AgeCurrent <= 18 then 1 else 0 end) as [Ages 14-18]
,SUM(CASE when H.AgeCurrent >= 19 and H.AgeCurrent <= 24 then 1 else 0 end) as [Ages 19-24]
,SUM(CASE when H.AgeCurrent >= 25 and H.AgeCurrent <= 65 then 1 else 0 end) as [Ages 25-65]
,SUM(CASE when H.AgeCurrent >= 66 then 1 else 0 end) as [Ages 65+]
,SUM(CASE when H.Board = '1' then 1 else 0 end) as [CommunityCommittee]
,SUM(CASE when H.YouthCommittee = '1' then 1 else 0 end) as [YouthCommittee]
,SUM(CASE when H.Parentcheck = '1' then 1 else 0 end) as [Parentcheck]
,SUM(CASE when H.CommunityResident = '1' then 1 else 0 end) as [CommunityResident]
,SUM(CASE when H.Race = 'Asian' then 1 else 0 end) as [Asian]
,SUM(CASE when H.Race = 'African-American' then 1 else 0 end) as [African-American]
,SUM(CASE when H.Race = 'Caucasian' then 1 else 0 end) as [Caucasian]
,SUM(CASE when H.Race = 'Native-American' then 1 else 0 end) as [Native-American]
,SUM(CASE when H.Race = 'Multi-Racial' then 1 else 0 end) as [Multi-Racial]
,SUM(CASE when H.Race = 'Latino-Hispanic' then 1 else 0 end) as [Latino-Hispanic]
,SUM(CASE when H.Gender = 'Male' then 1 else 0 end) as [Male]
,SUM(CASE when H.Gender = 'Female' then 1 else 0 end) as [Female]
,SUM(CASE when H.Sector = 'Business' then 1 else 0 end) as [Business]
,SUM(CASE when H.Sector = 'Civic-Volunteer' then 1 else 0 end) as [Civic-Volunteer]
,SUM(CASE when H.Sector = 'Community Resident' then 1 else 0 end) as [Community Resident]
,SUM(CASE when H.Sector = 'Faith Based' then 1 else 0 end) as [Faith Based]
,SUM(CASE when H.Sector = 'Healthcare' then 1 else 0 end) as [Healthcare]
,SUM(CASE when H.Sector = 'Human Support Agencies' then 1 else 0 end) as [Human Support Agencies]
,SUM(CASE when H.Sector = 'Law Enforcement' then 1 else 0 end) as [Law Enforcement]
,SUM(CASE when H.Sector = 'Local Government' then 1 else 0 end) as [Local Government]
,SUM(CASE when H.Sector = 'Media' then 1 else 0 end) as [Media]
,SUM(CASE when H.Sector = 'Parent or Guardian' then 1 else 0 end) as [Parent or Guardian]
,SUM(CASE when H.Sector = 'Philanthropic' then 1 else 0 end) as [Philanthropic]
,SUM(CASE when H.Sector = 'Schools' then 1 else 0 end) as [Schools]
,SUM(CASE when H.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 tblOrgActivities A on H.ActivityID = A.ActivityID
cross apply (select AgencyID, ActivityID, count(Regid) as [Tot Individuals for Activity]
,sum(CASE when AgeCurrent >= 11 and AgeCurrent <= 13 then 1 else 0 end) as [Tot Ages 11-13]
,sum(CASE when AgeCurrent >= 14 and AgeCurrent <= 18 then 1 else 0 end) as [Tot Ages 14-18]
,sum(CASE when AgeCurrent >= 19 and AgeCurrent <= 24 then 1 else 0 end) as [Tot Ages 19-24]
,sum(CASE when AgeCurrent >= 25 and AgeCurrent <= 65 then 1 else 0 end) as [Tot Ages 25-65]
,sum(CASE when AgeCurrent >= 66 then 1 else 0 end) as [Tot Ages 65+]
from (select distinct agencyid, activityID, regid, agecurrent from CTE_Hours) d
where d.AgencyID = H.AgencyID and d.Activityid = H.ActivityId
group by agencyID, ActivityID) I
group by H.Agency,H.Classification,H.Hours, A.ActivityName,H.ActivityDate, H.Objectives,H.ActivityType,H.ActivityOther,H.Narrative,H.Outcome,H.Duration,H.Frequency,H.Strategy,H.Need,H.NeedOther
Order by H.Agency, H.Classification,A.ActivityName, H.ActivityDate
```

```
With CTE_Hours as
( select distinct H.AgencyID, H.Agency,H.Hours, isnull(H.Classification,'') Classification, isnull(H.Objectives,'') Objectives,H.ActivityType,H.ActivityOther,H.Narrative,H.Outcome,H.Duration,H.Frequency,H.Strategy,H.Need,H.NeedOther, H.ActivityID, H.RegID
, R.AgeCurrent, R.Board, R.YouthCommittee, R.Parentcheck, R.CommunityResident, R.Race, R.Gender, R.Sector
, cast(H.ActivityDate as Date) ActivityDate, H.Fiscal
from tblOrgHours H
inner join tblOrgRegistrations R on H.Regid = R.RegID and R.AgeCurrent between 11 and 999
where H.[Hours] > 0
And H.Agency = 'Cicero - Area Project'
And H.Fiscal = 2018
And H.ActivityDate >= '20170101'
And H.ActivityDate < '20180601'
)
select H.Agency
,A.ActivityName
,H.Classification
,H.ActivityDate
,H.Objectives
,H.Hours
,count(A.ActivityName) over (partition by H.Agency,A.ActivityName order by A.activityName) as [Tot Activities for Agency]
,max(I.[Tot Individuals for Activity]) [Tot Individuals for Activity]
,MAX(I.[Tot Ages 11-13]) as [Tot Ages 11-13]
,max(I.[Tot Ages 14-18]) as [Tot Ages 14-18]
,max(I.[Tot Ages 19-24]) as [Tot Ages 19-24]
,max(I.[Tot Ages 25-65]) as [Tot Ages 25-65]
,max(I.[Tot Ages 65+]) as [Tot Ages 65+]
,Count(H.RegID) as [# individuals]
,SUM(CASE when H.AgeCurrent >= 11 and H.AgeCurrent <= 12 then 1 else 0 end) as [Ages 11-12]
,SUM(CASE when H.AgeCurrent >= 13 and H.AgeCurrent <= 17 then 1 else 0 end) as [Ages 13-17]
,SUM(CASE when H.AgeCurrent >= 18 and H.AgeCurrent <= 20 then 1 else 0 end) as [Ages 18-20]
,SUM(CASE when H.AgeCurrent >= 21 and H.AgeCurrent <= 24 then 1 else 0 end) as [Ages 21-24]
,SUM(CASE when H.Board = '1' then 1 else 0 end) as [CommunityCommittee]
,SUM(CASE when H.YouthCommittee = '1' then 1 else 0 end) as [YouthCommittee]
,SUM(CASE when H.Parentcheck = '1' then 1 else 0 end) as [Parentcheck]
,SUM(CASE when H.CommunityResident = '1' then 1 else 0 end) as [CommunityResident]
,SUM(CASE when H.Race = 'Asian' then 1 else 0 end) as [Asian]
,SUM(CASE when H.Race = 'African-American' then 1 else 0 end) as [African-American]
,SUM(CASE when H.Race = 'Caucasian' then 1 else 0 end) as [Caucasian]
,SUM(CASE when H.Race = 'Native-American' then 1 else 0 end) as [Native-American]
,SUM(CASE when H.Race = 'Multi-Racial' then 1 else 0 end) as [Multi-Racial]
,SUM(CASE when H.Race = 'Latino-Hispanic' then 1 else 0 end) as [Latino-Hispanic]
,SUM(CASE when H.Gender = 'Male' then 1 else 0 end) as [Male]
,SUM(CASE when H.Gender = 'Female' then 1 else 0 end) as [Female]
,SUM(CASE when H.Sector = 'Business' then 1 else 0 end) as [Business]
,SUM(CASE when H.Sector = 'Civic-Volunteer' then 1 else 0 end) as [Civic-Volunteer]
,SUM(CASE when H.Sector = 'Community Resident' then 1 else 0 end) as [Community Resident]
,SUM(CASE when H.Sector = 'Faith Based' then 1 else 0 end) as [Faith Based]
,SUM(CASE when H.Sector = 'Healthcare' then 1 else 0 end) as [Healthcare]
,SUM(CASE when H.Sector = 'Human Support Agencies' then 1 else 0 end) as [Human Support Agencies]
,SUM(CASE when H.Sector = 'Law Enforcement' then 1 else 0 end) as [Law Enforcement]
,SUM(CASE when H.Sector = 'Local Government' then 1 else 0 end) as [Local Government]
,SUM(CASE when H.Sector = 'Media' then 1 else 0 end) as [Media]
,SUM(CASE when H.Sector = 'Parent or Guardian' then 1 else 0 end) as [Parent or Guardian]
,SUM(CASE when H.Sector = 'Philanthropic' then 1 else 0 end) as [Philanthropic]
,SUM(CASE when H.Sector = 'Schools' then 1 else 0 end) as [Schools]
,SUM(CASE when H.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 tblOrgActivities A on H.ActivityID = A.ActivityID
cross apply (select AgencyID, ActivityID, count(Regid) as [Tot Individuals for Activity]
,sum(CASE when AgeCurrent >= 11 and AgeCurrent <= 12 then 1 else 0 end) as [Tot Ages 11-12]
,sum(CASE when AgeCurrent >= 13 and AgeCurrent <= 17 then 1 else 0 end) as [Tot Ages 13-17]
,sum(CASE when AgeCurrent >= 18 and AgeCurrent <= 20 then 1 else 0 end) as [Tot Ages 18-20]
,sum(CASE when AgeCurrent >= 21 and AgeCurrent <= 24 then 1 else 0 end) as [Tot Ages 21-24]
from (select distinct agencyid, activityID, regid, agecurrent from CTE_Hours) d
where d.AgencyID = H.AgencyID and d.Activityid = H.ActivityId
group by agencyID, ActivityID) I
group by H.Agency,H.Classification,H.Hours, A.ActivityName,H.ActivityDate, H.Objectives,H.ActivityType,H.ActivityOther,H.Narrative,H.Outcome,H.Duration,H.Frequency,H.Strategy,H.Need,H.NeedOther
Order by H.Agency, H.Classification,A.ActivityName, H.ActivityDate
```

In the scheme of things, it isnt too bad - the number of columns is not insignificant, but it is more how they relate to each other which is the challenging part. And the tables are not all that normalised - so it is challenging to ascertain some aspects of Entity / Attribute relationships.

The code looks fine. Little bit concerned that you are grouping by columns that arent part of the selected columns. That can sometimes cause problems / unexpected results.

So long as those columns are joint and several with the definition of an activity, then it should not impact. For example, you are showing H.Hours - and is that doesnt change within the scope of an activity, then it is essentially just another attribute of an activity - just like ActivityName, or Objectives. But if it can vary within an activity, then it might be creating an extra grouping which might upset the SUM() aggregations.

Bit confusing, but I think it will be OK.

Code looks good.

Cheers,

Mark Wills

Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with premium.
Start your 7-day free trial.

What about activity date ?

Back in a moment or three....