Additional parameters needed for this JOIN query

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
 

Open in new window

tblOrgRegistrations--1-.xlsx
tblOrgActivities--1-.xlsx
tblOrgHours.xlsx
al4629740Asked:
Who is Participating?
 
Mark WillsTopic AdvisorCommented:
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_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, A.ActivityName,H.ActivityDate, H.Objectives, H.Advocate, H.AdvocacyType
Order by 1,2,3

Open in new window

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
 
xiao jinshouCommented:
Hi Al4629740,

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-02')
   ,(1,'Basketball','2017-01-04')
   ,(1,'Basketball','2017-01-08')
   ,(1,'Basketball','2017-01-09')
   ,(3,'Basketball','2017-01-04')
   ,(3,'Basketball','2017-02-05')
   ,(3,'Basketball','2017-03-07')

   ,(1,'Valleyball','2017-04-02')
   ,(1,'Valleyball','2017-04-04')
   ,(1,'Valleyball','2017-04-08')
   ,(1,'Valleyball','2017-07-09')
   ,(2,'Valleyball','2017-07-04')
   ,(2,'Valleyball','2017-07-05')


;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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
 
Mark WillsTopic AdvisorCommented:
I remember helping out with this before :)

To clarify....  

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_Hours
inner join tblOrgRegistrations R on H.Regid = R.RegID
inner join tblOrgActivities A on H.ActivityID = A.ActivityID
inner 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.AdvocacyType

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

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.
0
 
Mark WillsTopic AdvisorCommented:
OK,

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_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(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.AdvocacyType
Order by 1,2,3

Open in new window

0
 
al4629740Author Commented:
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?
0
 
Mark WillsTopic AdvisorCommented:
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
0
 
xiao jinshouCommented:
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
                           )

 INSERT INTO #tblOrgHours Values
     (1,'Basketball','2017-01-02')
    ,(1,'Basketball','2017-01-04')
    ,(1,'Basketball','2017-01-04')
    ,(1,'Basketball','2017-01-08')
    ,(1,'Basketball','2017-01-09')
    ,(3,'Basketball','2017-01-04')
    ,(3,'Basketball','2017-02-05')
    ,(3,'Basketball','2017-03-07')

    ,(1,'Volleyball','2017-04-02')
    ,(1,'Volleyball','2017-04-04')
    ,(1,'Volleyball','2017-04-08')
    ,(1,'Volleyball','2017-07-09')
    ,(1,'Volleyball','2017-04-08')
    ,(1,'Volleyball','2017-07-09')
    ,(2,'Volleyball','2017-07-04')
    ,(2,'Volleyball','2017-07-05')


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

Truthfully, I now need an unduplicated column also for regid and call it [Unduplicated Count]
tblOrgHours.xlsx
Output-from-code-from-Mark.xlsx
0
 
al4629740Author Commented:
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
0
 
Mark WillsTopic AdvisorCommented:
Phew, that's a relief...

And are you asking a new Question :)

Should be able to get that [ActivityName] counter a bit easier using a window function OVER()
,count(A.ActivityName) over (partition by H.Agency,A.ActivityName order by A.activityName) as [Tot Activities for Agency]

Open in new window

Because all the elements for that are already in the Group By

And thanks for the spreadsheets.

Cheers,
Mark Wills
0
 
al4629740Author Commented:
Why yes I am.  Which means I should have posted that. Let me ask properly with a new question so we do this right.
0
 
al4629740Author Commented:
Great answer as always
0
 
Mark WillsTopic AdvisorCommented:
Thanks :) And as always, very happy it is working for you, and always a privilege to help :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.