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

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_Hoursinner join tblOrgRegistrations R on H.Regid = R.RegIDinner join tblOrgActivities A on H.ActivityID = A.ActivityIDwhere R.AgeCurrent between 11 and 999And h.Agency = 'Administrator'And H.Fiscal = 2018And 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.AdvocacyTypeOrder by 1,2,3

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

I don't understand what you mean by duplicate and unduplicated counts. I write a sample query to show for All agencies how many basketball and how many volleyball are played and when they were played:

create table #tblOrgHours (
AgencyID INT
, ActivityName Varchar(200)
, ActivityDate Date
)

;With CTE_Hours as
(select distinct
AgencyID
, ActivityName
, ActivityDate
-- , ROW_NUMBER() OVER (PARTITION BY AgencyID,ActivityName ORDER BY ActivityDate ASC) rn
from #tblOrgHours
where 1=1)

, ActivityName_Count as
(select
ActivityName
, Count(cast(AgencyID As Varchar(30)) + cast(ActivityDate As Varchar(30))) As Count_ActivityDate
from #tblOrgHours
where 1=1
group by ActivityName
)

-- This will display for ALL agencies how many basketball (and valley ball) are played and on which date they were played.
select CTE_Hours.ActivityName
,CTE_Hours.ActivityDate
,ActivityName_Count.Count_ActivityDate As 'total No. of Play'
-- ,rn
from CTE_Hours
inner join ActivityName_Count
on ActivityName_Count.ActivityName = CTE_Hours.ActivityName
where 1=1

drop table #tblOrgHours

- Xiao

0

al4629740Author Commented:

Hello Xiao,

Thanks for your answer. However, the code I pasted earlier is the code that needs to be modified for this purpose if possible. The example output I was stating was what I needed added onto the current output.

0

al4629740Author Commented:

FYI, In the example I gave the Unduplicated count of 10 which was relative to the 3 basketball classes. Duplicated would be a count relative to each actual class. Thanks.

0

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!

The current query column for #individuals : Count(H.RegID) as [# individuals] remains as is...
Add a new column to reflect the different people for the activity (for the current "where" clause)

The "duplicated" annotation means that the column will show the same value for the activity for as many rows as exists for that activity - I think that part of the requirement is a bit confusing.

I have yet to load your full definitions, but you can try :

With CTE_Hours as( select distinct H.AgencyID, H.Agency, H.Classification, H.Objectives, H.Advocate, H.AdvocacyType, 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 [Hours] > 0 And Agency = 'Administrator' And Fiscal = 2018 And ActivityDate >= '20170101' And ActivityDate < '20180101') select H.Agency ,A.ActivityName ,H.Classification ,H.ActivityDate ,H.Objectives ,H.Advocate ,H.AdvocacyType,max(I.[Tot Individuals for Activity]) [Tot Individuals for Activity] ,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 Agency,Classification,Objectives,Advocate,AdvocacyType,ActivityID, count(Regid) as [Tot Individuals for Activity] from CTE_Hours group by Agency,Classification,Objectives,Advocate,AdvocacyType,Activityid) I on I.Agency = H.Agency and I.Activityid = A.ActivityId and I.Classification = H.Classification and I.Objectives = H.Objectives and I.Advocate = H.Advocate and I.AdvocacyType = H.AdvocacyTypegroup by H.Agency,H.Classification, A.ActivityName,H.ActivityDate, H.Objectives, H.Advocate, H.AdvocacyTypeOrder by 1,2,3

Notice how I moved the WHERE clauses all the way up to CTE_Hours - always best to limit the criteria first

Once I have loaded the spreadsheets, and tested, I will get back to you with any other changes (if any).

Cheers,
Mark Wills

P.S. I dont think we need to be so pedantic with the I subquery - we might only need AgencyID and ActivityID - but will test and get back to you

0

al4629740Author Commented:

Thanks Mark. I tried the current code and the output appears to be off. Not all the activities showed up on this one. Let me know if you need me to clarify anything in specific.

A few updates needed to the spreadsheets....
1) Spreadsheets do not match table definitions used in the query - tblorghours and tblorgregistrations
2) Data doesnt match - activityID in tblorghours doesnt have tblactivity entries

Had to do a bit of fiddling, and in looking at the data, think we only need ActivityID and AgencyID for our 'I' subquery

And probably the reason why some activities didnt show (the inner join on the 'I' subquery)...

So, please have a look at :

With CTE_Hours as( select distinct H.AgencyID, H.Agency, H.Classification, H.Objectives, H.Advocate, H.AdvocacyType, 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 = '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,max(I.[Tot Individuals for Activity]) [Tot Individuals for Activity] ,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(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, A.ActivityName,H.ActivityDate, H.Objectives, H.Advocate, H.AdvocacyTypeOrder by 1,2,3

Getting closer. The column Tot Individuals for this Activity is totaling up the # individuals per activity and this total is a duplication. Is it possible to generate an unduplicated number for the activity?

Not sure what you mean. Does Duplicated mean same regid counted more than once ?

If the new column is nearly there, but is counting duplicated regid's, then try : count(Distinct Regid)

Or,

Update the spreadsheets (correct columns and matching data) and show some corresponding results

0

al4629740Author Commented:

Yes, exactly. If there was a basketball activity for two different days and John Smith was in both of them then the unduplicated count would be 1. The duplicated count would be 2

So, the new column [Tot Individuals for Activity] should be a count of distinct regid's - right ?

Or, are you asking for a count of total regid's for an activity ?

With CTE_Hours as( select distinct H.AgencyID, H.Agency, H.Classification, H.Objectives, H.Advocate, H.AdvocacyType, 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 = '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,max(I.[Tot Individuals for Activity]) [Tot Individuals for Activity] -- total distinct ID's ,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 group by H.Agency,H.Classification, A.ActivityName,H.ActivityDate, H.Objectives, H.Advocate, H.AdvocacyTypeOrder by 1,2,3

Any chance of new spreadsheets with example results ? Otherwise, we just keep guessing at what column and what duplicated means.... And please use column names in your comments - help narrow down which columns are problematic and why...

0

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.

Ok, now I understand the dup and undup count. Because the structure are so different between your method and mine, I cannot turn the query in your way (I think Mark is doing that). Please try the following query to see the results:

create table #tblOrgHours (
AgencyID INT
, ActivityName Varchar(200)
, ActivityDate Date
)

;With CTE_Hours as
(select distinct
AgencyID
, ActivityName
, ActivityDate
-- , ROW_NUMBER() OVER (PARTITION BY AgencyID,ActivityName ORDER BY ActivityDate ASC) rn
from #tblOrgHours
where 1=1)

, ActivityName_Count as
(select
ActivityName
, Count(cast(AgencyID As Varchar(30)) + cast(ActivityDate As Varchar(30))) As Count_ActivityDate
from #tblOrgHours
where 1=1
group by ActivityName
)

, ActivityName_UnDup_Count as
(select
ActivityName
, Count(distinct cast(AgencyID As Varchar(30)) + cast(ActivityDate As Varchar(30))) As Count_UnDup_ActivityDate
from #tblOrgHours
where 1=1
group by ActivityName
)
-- This will display for ALL agencies how many basketball (and valley ball) are played and on which date they were played.
select distinct
CTE_Hours.ActivityName
,CTE_Hours.ActivityDate
,ActivityName_Count.Count_ActivityDate As 'total No. of Play'
,Count_UnDup_ActivityDate As 'Undup No. of Play'
,ActivityName_Count.Count_ActivityDate - Count_UnDup_ActivityDate As 'Dup No. of Play'
-- ,rn
from CTE_Hours
inner join ActivityName_Count
on ActivityName_Count.ActivityName = CTE_Hours.ActivityName
inner join ActivityName_UnDup_Count
on ActivityName_UnDup_Count.ActivityName = CTE_Hours.ActivityName
where 1=1

drop table #tblOrgHours

- Xiao

0

al4629740Author Commented:

The current output column [Tot Individuals for Activity] looks like a duplicated count as you can see from the attached output. Should probabaly rename that to [Duplicated Count] I need both duplicated and unduplicated columns so the current [Tot Individuals for Activity] is needed.

Xiao, yes when it comes to [activityName] and comparing with the [ActivityDate] that is what I am looking for.

0

al4629740Author Commented:

Mark,

After looking at the code you sent, it works and I understood the results incorrectly. The [Tot Individuals for Activity] is an UNDUPLICATED or distinct count for all associated [ActivityID] and that is what I was looking for. Also the [# individuals] is counting all the regids correctly for the full count.

is it possible to also count all distinct [ActivityName] the same way you did with the column [Tot Individuals for Activity]?

I'm trying to do it but line 18 is a bit over my head

Thanks :) And as always, very happy it is working for you, and always a privilege to help :)

0

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

I don't understand what you mean by duplicate and unduplicated counts. I write a sample query to show for All agencies how many basketball and how many volleyball are played and when they were played:

create table #tblOrgHours (

AgencyID INT

, ActivityName Varchar(200)

, ActivityDate Date

)

INSERT INTO #tblOrgHours Values

(1,'Basketball','2017-01-0

,(1,'Basketball','2017-01-

,(1,'Basketball','2017-01-

,(1,'Basketball','2017-01-

,(3,'Basketball','2017-01-

,(3,'Basketball','2017-02-

,(3,'Basketball','2017-03-

,(1,'Valleyball','2017-04-

,(1,'Valleyball','2017-04-

,(1,'Valleyball','2017-04-

,(1,'Valleyball','2017-07-

,(2,'Valleyball','2017-07-

,(2,'Valleyball','2017-07-

;With CTE_Hours as

(select distinct

AgencyID

, ActivityName

, ActivityDate

-- , ROW_NUMBER() OVER (PARTITION BY AgencyID,ActivityName ORDER BY ActivityDate ASC) rn

from #tblOrgHours

where 1=1)

, ActivityName_Count as

(select

ActivityName

, Count(cast(AgencyID As Varchar(30)) + cast(ActivityDate As Varchar(30))) As Count_ActivityDate

from #tblOrgHours

where 1=1

group by ActivityName

)

-- This will display for ALL agencies how many basketball (and valley ball) are played and on which date they were played.

select CTE_Hours.ActivityName

,CTE_Hours.ActivityDate

,ActivityName_Count.Count_

-- ,rn

from CTE_Hours

inner join ActivityName_Count

on ActivityName_Count.Activit

where 1=1

drop table #tblOrgHours

- Xiao