Does this problem require a loop?
-
The query below retrieves data pertaining to students attendance. At present it does one school(trkuniq) at a time. What I'm looking for is a way to loop through the TRACK table and for a given year, use each trkuniq in my query. In the end, I want to turn my script into a stored procedure and supply on the DATE parameter and get attendance for all schools in the district.
TRACK.TRKUNIQ = @TRACK (my procedure)
In the TRACK table, the schoolc and schyear represent a unique trkuniq.
[track]
[trkuniq] [int] NOT NULL,
[trackc] [varchar](3) NOT NULL,
[descript] [varchar](32) NOT NULL,
[schoolc] [varchar](5) NOT NULL,
[schyear] [decimal](4, 0) NOT NULL,
--------------------------------------------------------------------------------------------------------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
declare @track int,
@date smalldatetime
set @track = 87
set @date = '09/14/2007'
SET NOCOUNT ON
IF(SELECT ISNULL(OBJECT_ID('TEMPDB..#MEMX'),0)) > 0 BEGIN DROP TABLE #MEMX END
IF(SELECT ISNULL(OBJECT_ID('TEMPDB..#DATTX'),0)) > 0 BEGIN DROP TABLE #DATTX END
CREATE TABLE #MEMX (STUUNIQ INT, MEMBERSHIP INT)
CREATE TABLE #DATTX (STUUNIQ INT, ATTENDANCE INT)
INSERT INTO #MEMX
EXEC SISP_SISTRKMEM @TRACK,@DATE
INSERT INTO #DATTX
EXEC SISP_SISTRKATT2 @TRACK,@DATE
SELECT
SC.schoolc,
SC.schname,
TR.schyear,
SD.suniq,
SD.ethnicc,
SD.lastname,
SD.firstname,
ME.membership,
ISNULL(DA.attendance,0) AS daysabsent
FROM STUDEMO SD
JOIN STUSTAT ST ON ST.suniq = SD.suniq AND ST.Trkuniq = @TRACK
INNER JOIN #MEMX ME ON ST.stuuniq = ME.stuuniq
LEFT JOIN #DATTX DA ON ST.stuuniq = DA.stuuniq
JOIN TRACK TR ON TR.trkuniq = ST.Trkuniq
JOIN SCHOOL SC ON SC.schoolc = TR.schoolc
ORDER BY SD.lastname, SD.firstname
SET NOCOUNT OFF
-
Are you looking for something like this:
School1 #enrolled #InAttendance
??
-
Yes
School, Membership_Days, Total_Attendance_days
Membership is the total number of school days to-date. I calculate the Total_Attendance_days by subtracting the daysabsent.
-
You shouldn't need a loop. Look at writing the query using group by.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply