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_Hoursinner join tblOrgRegistrations R on H.Regid = R.RegIDinner join tblOrgActivities A on H.ActivityID = A.ActivityIDinner 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.NeedOtherOrder by H.Agency, H.Classification,A.ActivityName, H.ActivityDate

"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.

This is getting very confusing now - the seperate total columns are overlapping so it is very difficult to determine results.

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_Hoursinner join tblOrgRegistrations R on H.Regid = R.RegIDinner join tblOrgActivities A on H.ActivityID = A.ActivityIDinner 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.AdvocacyTypeOrder 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]

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

They wont align - that is the problem I was speaking about - it uses entirely different criteria being regardless of activity (and assumed activitydate)

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

OR DO YOU WANT IT BY agency and distinct Activity regardless of ActivityDate ? That way it will be a breakdown by AGE for [Tot Individuals for Activity]...

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_Hoursinner join tblOrgRegistrations R on H.Regid = R.RegIDinner join tblOrgActivities A on H.ActivityID = A.ActivityIDinner 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.AdvocacyTypeOrder 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.

>> [Tot Individuals for Activity] is a distinct count of all individuals for that activity across all the dates, correct?

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_Hoursinner join tblOrgActivities A on H.ActivityID = A.ActivityIDcross 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.AdvocacyTypeOrder by 1,2,3

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

By George I think you got it! Its working. There is only one thing, if you could. The original code I posted was different from the code you used for these examples. There are so many new changes to the this code, would you be able to transplant those changes into the code below or tell me what you changed?

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_Hoursinner join tblOrgRegistrations R on H.Regid = R.RegIDinner join tblOrgActivities A on H.ActivityID = A.ActivityIDinner 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.NeedOtherOrder by H.Agency, H.Classification,A.ActivityName, H.ActivityDate

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 ?

al4629740Author Commented:

Yes, it makes sense, but takes time to digest. Have you ever seen queries this big?

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_Hoursinner join tblOrgActivities A on H.ActivityID = A.ActivityIDcross 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.NeedOtherOrder by H.Agency, H.Classification,A.ActivityName, H.ActivityDate

Sorry this was the updated code. I had to change the age groups. This is where I notice you changed R.AgeCurrent to H.AgeCurrent

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_Hoursinner join tblOrgActivities A on H.ActivityID = A.ActivityIDcross 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.NeedOtherOrder by H.Agency, H.Classification,A.ActivityName, H.ActivityDate

Yes I have seen bigger - and multi-faceted - and considerably more complex. And that's just in here :)

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

al4629740Author Commented:

Ok. Thank you for the insight

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for freeEdge Out The Competitionfor your dream job with proven skills and certifications.Get started todayStand Outas the employee with proven skills.Start learning today for freeMove Your Career Forwardwith certification training in the latest technologies.Start your trial today

What about activity date ?

Back in a moment or three....