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.

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

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

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 ?

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
```

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.

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
```

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

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.

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
```

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

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.

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'

Open in new window