Help Building SQL Statement

Gary
01-14-2004, 03:53 PM
You've helped greatly in the past, how about again?

Here's the table structure:

tblStudents
student_id
student_name

tblEvents
event_id
event_date
event_minutes

tblHistory
ref_num
student_id
event_id
minutes_missed

1. tblStudents holds student names.
2. tblEvents holds info about events (date and length in minutes)
3. tblHistory records what student attended what event and how much they missed.

Result set needed is:


event_date, student_name, minutes_missed, SUM(minutes_missed)

Note: SUM[minutes_missed) would be up to & including that event_date

What I have so far:

SELECT fname as Student, event_date, minutes_missed
FROM tblhistory, tblstudents, tblevents
WHERE tblhistory.student_id = tblstudents.student_id and tblhistory.event_id=tblevents.event_id;

Any assistance would be greatly appreciated.

--Gary

rajeeshun
01-15-2004, 04:20 AM
...You are asking us to do your home work !!!!

Gary
01-15-2004, 06:42 AM
...You are asking us to do your home work !!!!
absolutely not -- I have a full-time job at Sprint, but told the high assistant band director of the school where my kids attend and my wife teaches, that I'd offer to help her. She been keeping track of the band minutes by hand for several years and it's very time consuming and prone to calculating errors.

I'm only asking assistance with the SQL statement. In my 'day job' at Sprint, I code CTI application (computer-telephony) for call centers at Sprint, but always have access to a DBA (usually Oracle) for assistance in writing SQL statement. I asked him about the table design, but didn't want to bother him too much for additional help.

--Gary

Gary
03-29-2004, 07:51 PM
Okay sometime ago I asked for your assistance and several helps were provided,even one accusation that I was asking for help to do my homework. I'm way past being a 'formal' student these days, just trying to help our local high school assistant band director making her task less tedious.

Short summary: She keeps track of all marching band rehearsals, minutes possible and the minutes missed by each student -- by hand. She then tabulates the totals and posts them on a bulletin board -- again all hand written. I offered to help by writing an application.

Entering student name, event dates and possible minutes, not a problem. And even entering the data into a history table also, not a problem. The problem was developing a sql statement that returned something like the following:
http://www.bluerivercofc.org/images/minutes_missed.jpg

The SQL is as follows:
SELECT tblHistory.ref_num,
(tblStudents.lname + ', ' + tblStudents.fname),
(SELECT tblHistory.minutes_missed
FROM tblHistory INNER JOIN tblEvents ON tblEvents.event_id = tblHistory.event_id
WHERE tblEvents.event_date = #03/15/2004# AND
tblHistory.student_id = 4)
& ' (' & (select sum(minutes_missed)
from tblHistory, tblEvents
where tblHistory.event_id = tblEvents.event_id and
tblEvents.event_date <= #03/15/2004# and
student_id = 4) & ')',
(SELECT tblHistory.minutes_missed
FROM tblHistory INNER JOIN tblEvents ON tblEvents.event_id = tblHistory.event_id
WHERE tblEvents.event_date = #03/16/2004# AND
tblHistory.student_id = 4)
& ' (' & (select sum(minutes_missed)
from tblHistory, tblEvents
where tblHistory.event_id = tblEvents.event_id and
tblEvents.event_date <= #03/16/2004# and
student_id = 4) & ')',
(SELECT tblHistory.minutes_missed
FROM tblHistory INNER JOIN tblEvents ON tblEvents.event_id = tblHistory.event_id
WHERE tblEvents.event_date = #03/17/2004# AND
tblHistory.student_id = 4)
& ' (' & (select sum(minutes_missed)
from tblHistory, tblEvents
where tblHistory.event_id = tblEvents.event_id and
tblEvents.event_date <= #03/17/2004# and
student_id = 4) & ')',
(SELECT tblHistory.minutes_missed
FROM tblHistory INNER JOIN tblEvents ON tblEvents.event_id = tblHistory.event_id
WHERE tblEvents.event_date = #03/18/2004# AND
tblHistory.student_id = 4)
& ' (' & (select sum(minutes_missed)
from tblHistory, tblEvents
where tblHistory.event_id = tblEvents.event_id and
tblEvents.event_date <= #03/18/2004# and
student_id = 4) & ')',
(SELECT tblHistory.minutes_missed
FROM tblHistory INNER JOIN tblEvents ON tblEvents.event_id = tblHistory.event_id
WHERE tblEvents.event_date = #03/19/2004# AND
tblHistory.student_id = 4)
& ' (' & (select sum(minutes_missed)
from tblHistory, tblEvents
where tblHistory.event_id = tblEvents.event_id and
tblEvents.event_date <= #03/19/2004# and
student_id = 4) & ')'
FROM tblStudents INNER JOIN
(tblEvents INNER JOIN tblHistory ON tblEvents.event_id = tblHistory.event_id)
ON tblStudents.student_id = tblHistory.student_id
WHERE tblStudents.student_id = tblStudents.student_id AND
tblHistory.event_id = tblEvents.event_id AND
tblEvents.event_date = #03/15/2004# AND
tblStudents.student_id = 4
ORDER BY tblStudents.lname, tblStudents.fname;

I broke it apart into separate variables, then concatenated them at the end to maintain reability. The dates are calculated from the date selected by the user in a datepicker. That also what I used to update the column headers on the listview.

If you want to try yourself, I've attached the database file.

--Gary

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum