al4629740
asked on
How to change the breakout based on grouping
Currently I have the following query that lists each activity on a separate line. What modifications do I need to make in order to have just one line that tells all the SUMs for the specified time. It no longer needs to group the ActivityName, Classifciation, activityDate, Objectives, Hours, Activitycount, Narrative, Outcome, Strategy.
;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.Fiscal = 2018 ) 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],MAX(I.[Tot Ages 11-12]) as [Tot Ages 11-12],max(I.[Tot Ages 13-17]) as [Tot Ages 13-17],max(I.[Tot Ages 18-20]) as [Tot Ages 18-20],max(I.[Tot Ages 21-24]) as [Tot Ages 21-24]
,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 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 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 A.ActivityName = 'Community Committee' And 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
ASKER
No that doesn't do it. The output had many rows. The output should be one row providing the totals
Can you please provide some sample records to understand your result set and expectation better..
So, like a total line ?
For all the SUM columns ? and MAX ? Any others ?
For all the SUM columns ? and MAX ? Any others ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi al4629740,
How are you going with this ?
Is there more you need ?
How are you going with this ?
Is there more you need ?
ASKER
Sorry for not responding as I had some personal issues preventing me.
Thanks Mark. This seems to be the solution as you provided.
Thanks Mark. This seems to be the solution as you provided.
No Worries. Hope your issues can also achieve a Good Answer :)
And always family first - you need to take care of personal matters first and foremost.
Kind Regards,
Mark Wills
And always family first - you need to take care of personal matters first and foremost.
Kind Regards,
Mark Wills
ASKER
Thanks Mark. You are so right.
Open in new window