How can I get the desired output from my attendance table using JOIN statements?

I have an attendance table in SQL Server 2012 named tblOrgHours. (attached)  This is where I log all my attendance for different agencies in tracking how many hours each activity has been performed each day by different kids.  The ActivityID in this table is relational with tblOrgActivity. (attached)  The RegID in that same table is relational with the student information found in table tblOrgRegistrations. (attached)  I need to create an sql query that will output all the activity that has happened in a month.  So,for example, I need the output for January to show all the unique activities that have occurred each day, line by line, for a given agency.  Each line would represent an activity that has taken place for a given date in that month and the number of kids for that particular activity, along with specified age ranges and gender.   To illustrate this is an example below of what the output headings would need to look like.  I also attached a non accurate sample Output of what I am trying to achieve.  

Agency, ActivityName, ActivityDate, # of individuals that had > 0 hours that day, # of youth ages 11-13, #of youth ages 14-18, # of females, # of males


I'm hoping this will also help me utilize JOIN statements better as I am very novice to SQL.  Please ask me any follow up questions and I will try my best to answer.  I would have pasted some of these tables here in this question, but I'm not sure how to do it so it looks clean.
Output-Example.xlsx
tblOrgRegistrations.xlsx
tblOrgActivities.xlsx
tblOrgHours.xlsx
al4629740Asked:
Who is Participating?
 
Mark WillsTopic AdvisorCommented:
Well, that is good to hear :)

The other scenario is quite involved - perfectly do-able, but more involved.

So, we go back to a Common Table Expression - we will get distinct tblOrgHours - and then we can join on that result set. Still a single query, just starts with a 'with' and ends with the 'order by'
With CTE_Hours as
( 
  select distinct AgencyID, Agency, ActivityID, RegID, cast(ActivityDate as Date) ActivityDate
  from tblOrgHours 
  where [Hours] > 0
) 
select  H.Agency
       ,A.ActivityName
       ,H.ActivityDate
       ,Count(H.RegID) as [# individuals]
       ,SUM(CASE when R.AgeCurrent < 14 then 1 else 0 end) as [Ages 11-13]
       ,SUM(CASE when R.AgeCurrent > 13 then 1 else 0 end) as [Ages 14-18]
       ,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]

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 18

group by H.Agency,A.ActivityName,H.ActivityDate 
Order by 1,2,3
 

Open in new window

0
 
Mark WillsTopic AdvisorCommented:
Most of the details appear to be in tblOrgHours
where it can be joined to tblOrgRegistrations to get male/female and age using REGID
and can be joined to tblOrgActivities to get Activity name using ACTIVITYID
and we can get AgencyID from tblOrgHours

Is there a tblOrgAgencies ? (think there is sufficient information in tblOrgHours

So, the number of kids is the number (count) of different REGID's for a given activity + day combo

In tblOrgHours there are 3 columns [Hours], [ParticipantHours] and [VolunteerHours] is there one of these 3 that best represents the kids activity ie Hours > 0 ?
0
 
Mark WillsTopic AdvisorCommented:
Have assumed [Hours] but can be easily changed...
Have cast(activitydate as date) and might not be necessary....

Have a look at :
select  H.Agency
	    ,A.ActivityName
	    ,cast(H.ActivityDate as Date) ActivityDate
	    ,Count(H.RegID) as [# individuals]
	    ,SUM(CASE when R.AgeCurrent < 14 then 1 else 0 end) as [Ages 11-13]
	    ,SUM(CASE when R.AgeCurrent > 13 then 1 else 0 end) as [Ages 14-18]
	    ,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]

from tblOrgHours H
inner join tblOrgRegistrations R on H.Regid = R.RegID
inner join tblOrgActivities A on H.ActivityID = A.ActivityID

where H.[Hours] > 0
and R.AgeCurrent between 11 and 18

group by H.Agency,A.ActivityName,cast(H.ActivityDate as Date) 
Order by 1,2,3

Open in new window

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:
Yes the number of kids would be represented by number of RegID for that activity for that day.

Yes, the hours would be represented by just the Hours and not Participant,Volunteer Hours

I tested the output and it sure seems to be working well.  When I get home later I will look at it in more detail to verify.  Thanks Mark, you sure know your stuff.
0
 
al4629740Author Commented:
Mark,

I found an issue with the output related to duplicates.  I ran the following code to do a test by changing the age range along with isolating one agency.

select  H.Agency
	    ,A.ActivityName
	    ,cast(H.ActivityDate as Date) ActivityDate
	    ,Count(H.RegID) as [# individuals]
	    ,SUM(CASE when R.AgeCurrent < 14 then 1 else 0 end) as [Ages 11-13]
	    ,SUM(CASE when R.AgeCurrent > 13 then 1 else 0 end) as [Ages 14-18]
	    ,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]

from tblOrgHours H
inner join tblOrgRegistrations R on H.Regid = R.RegID
inner join tblOrgActivities A on H.ActivityID = A.ActivityID

where H.[Hours] > 0
and R.AgeCurrent between 0 and 9999999
and h.Fiscal = 2018
and h.Agency = 'Fathers Who Care'

group by H.Agency,A.ActivityName,cast(H.ActivityDate as Date) 
Order by 1,2,3

Open in new window


Notice in the attachment the output I received from your code and then listed is what is actually in the system.  While your code is correct, it allow duplicates.  So for example, V. Jones should only have been counted as once that day instead of twice.  As a result that also skews the number of kids in the age categories also.  Does that make sense?
Agency---Fathers-Who-Care-Attendance.xls
0
 
Mark WillsTopic AdvisorCommented:
Yep, I see that. Could be one of two things...

either we need to count(distinct h.regid) ie count only 1 instance of regid  per agency / activity / day
or
we need to account for overlapping times so that the 2hours from 9 to 11 is included and the 1 hour from 9 to 10 is excluded.
then
what would happen if there were two non overlapping times - say 9:00 to 10:00 and then later in the day 14:00 to 15:00

if we ever plan to count hours, then we need to resolve the overlapping times.

if it is purely distinct regid's then we wont need to worry about anything more granular than day.
0
 
al4629740Author Commented:
For the sake of this situation, let’s use purely distinct regids for that  activity, day, agency
0
 
al4629740Author Commented:
Correct, we don’t need to get any more granular than a day
0
 
al4629740Author Commented:
Mark, job well done.  Thank you again.  

I made some mods to get more stats so please take a gander at my syntax to see if all is well.  

With CTE_Hours as
( 
  select distinct AgencyID, Agency, ActivityID, RegID, cast(ActivityDate as Date) ActivityDate, Fiscal
  from tblOrgHours 
  where [Hours] > 0
) 
select  H.Agency
       ,A.ActivityName
       ,H.ActivityDate
       ,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.Gender = 'Male' then 1 else 0 end) as [Male]
       ,SUM(CASE when R.Gender = 'Female' then 1 else 0 end) as [Female]

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 = 'Cicero Area Project'
And H.Fiscal = 2018

group by H.Agency,A.ActivityName,H.ActivityDate 
Order by 1,2,3

Open in new window

1
 
Mark WillsTopic AdvisorCommented:
New stats look good as far as syntax is concerned.  Well done.

And very happy you have a working solution.

By the way, that first part where we select from tblOrgHours to generate CTE_Hours is where we would need to make the change in order to accommodate those overlapping times if you want to investigate further....

Cheers,
Mark Wills
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.