troubleshooting Question

Additional parameters needed for this JOIN query

Avatar of al4629740
al4629740Flag for United States of America asked on
Microsoft SQL ServerSQL
18 Comments1 Solution110 ViewsLast Modified:
I have the following SQL code attached.  Everything works well with this code but there are a few additional parameters that I need in the output.  The current output lists each activity by rows.  Within those rows I need a column that show how many of that particular type of activity there is and how many unduplicated individuals that attended for that period.  For example

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


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


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

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

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

group by H.Agency,H.Classification, A.ActivityName,H.ActivityDate, H.Objectives, H.Advocate, H.AdvocacyType
Order by 1,2,3
 
tblOrgRegistrations--1-.xlsx
tblOrgActivities--1-.xlsx
tblOrgHours.xlsx
ASKER CERTIFIED SOLUTION
Mark Wills
Topic Advisor
Join our community to see this answer!
Unlock 1 Answer and 18 Comments.
Start Free Trial
Learn from the best

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

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 18 Comments.
Try for 7 days

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

-Mike Kapnisakis, Warner Bros