OnsiteSupport
asked on
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?
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?
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:
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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
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
ASKER
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.
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.