distinct count across different activities

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

Open in new window

al4629740Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

Mark WillsTopic AdvisorCommented:
So, for the range of records chosen to report on (in the first part of CTE_Hours) we want total for age groups regardless of activity

What about activity date ?

Back in a moment or three....
Mark WillsTopic AdvisorCommented:
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_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

Open in new window

al4629740Author Commented:
Hey Mark,

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
Determine the Perfect Price for Your IT Services

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!

Mark WillsTopic AdvisorCommented:
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
Mark WillsTopic AdvisorCommented:
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_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

Open in new window

al4629740Author Commented:
Mark,  it doesn't seem to work on that last one.

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.
Mark WillsTopic AdvisorCommented:
>> [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_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

Open in new window

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
al4629740Author Commented:
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_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

Open in new window

Mark WillsTopic AdvisorCommented:
OK,

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 ?
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_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

Open in new window

al4629740Author Commented:
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_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

Open in new window

Mark WillsTopic AdvisorCommented:
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 free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.