We help IT Professionals succeed at work.

# SQL code to Evaluating Attendance weeks

on
I have a daycamp that has kids attending camp for multiple weeks.  On the first week that they attend camp, they get a t-shirt.  So, I need to calculate their first week of camp for all weeks in order to give them a shirt.

Child 1 is attending week 1,2,3,4,5

On Week 1, T-shirt = True
On Week 2-5, T-Shirt = False

child 2 is attending week 3, 5, 7

On Week 3, T-shirt = True
On Week 5, T-shirt = False
On week 7, T-Shirt = False

I need to give a report each week that shows the kids who need to get a shirt.

Sounds like a highschool word problem.

This is reporting in Crystal Reports, but I'm thinking I can do the evaluation in SQL and then pass the results to crystal?
Comment
Watch Question

## View Solution Only

Senior DBA

Commented:
The basic solution (knowing no other details) would look something like this:

SELECT
ChildName
FROM (
SELECT ChildName,
Min(WeekAttending) FirstWeek
FROM MyTable
Group By ChildName
) ChildsFirstWeek
WHERE FirstWeek = 1

In the inner query:

SELECT ChildName,
Min(WeekAttending) FirstWeek
FROM MyTable
Group By ChildName

We get a virtual table of all of the children, and their first week attending.

In the outer portion of the query, we limit the children returned to those with their first week matching our selected week.

How you will implement this depends on your coding skills - a stored procedure with the week number passed in, for example - and the actual data format, but this is a simple, understandable way to approach a question of this type.  There are other solutions, but most are not as understandable.

Commented:
Do you have a table that tracks their attendance?  If so, does it have (or can you add) a column for ReceivedTShirt?

Commented:
bhess1:
Your method would work well . . if every child's first week was Week1.  However, if children can, for instance, attend weeks 2,3,4, and 5 instead of weeks 1,2,3,4,and 5, then you need to parameterize this in a stored proc or define some means of knowing what the current week is.
I've taken the stored proc approach in the attached code:

``````CREATE PROCEDURE [dbo].[usp_CheckForTShirts]
@CurrentWeek	INT
AS
BEGIN

SELECT ChildName
FROM
( SELECT ChildName,
MIN(WeekAttending) FirstWeek
FROM   MyTable
GROUP BY ChildName
) ChildsFirstWeek
WHERE  FirstWeek = @CurrentWeek;

END
``````
Senior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
http://www.experts-exchange.com/Database/Reporting_/Crystal_Reports/Q_26204379.html?cid=1576

You can suppress the lines where the t-shirt field is blank

mlmcc

Commented:
Mlmcc...good memory...one problem...user wants a report each week for the week of camp they are in.  If he chooses the week number as a parameter, how can I calc the first week if its not in the result set?
Senior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
You really can't.

You could use SQL in a stored procedure to gather the information or use the report with all the data but only show the week in question.

mlmcc

Commented:
Thanks once again....I was thinking that I could create an array in Crystal to  do the same as a simple Min() statement on the sql side.