We help IT Professionals succeed at work.

SQL code to Evaluating Attendance weeks

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

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.
Do you have a table that tracks their attendance?  If so, does it have (or can you add) a column for ReceivedTShirt?
 
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

Open in new window

Senior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
How about using the idea in your previous question
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

Author

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?
Mike McCrackenSenior 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

Author

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.