Sql script help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Sql script help

Author

Topic

rsql
Starting Member

6 Posts

Posted - 2011-10-12 : 10:51:06

Here is the script I use to get the list of students who are absent 3/6/9/12 periods since the start of school year.when I run this report everyday it gives me list of all the students(who had already been notified+ new ones ) All I want is only new students everyday.

Ex:If jonny had missed period 02 3 times since the start and period 07 3 times since the start and we send the letter yesterday then today I only want to see Jonny if he has a different period with 3 absences or period 02 or 07 if it hits 6 absences.

Here is the actual script:

SELECT p.personID,
p.studentNumber, i.lastName,
i.firstName, i.middleName, e.grade,
pd.name AS periodName,
COUNT(a.attendanceID) AS attendanceCount
--select *
FROM Period AS pd INNER JOIN
Attendance AS a WITH (NOLOCK) ON a.periodID = pd.periodID AND a.date >= '08/24/2011' AND a.date <= '10/07/2011' LEFT OUTER JOIN
AttendanceExcuse AS x WITH (NOLOCK)ON x.excuseID = a.excuseID AND x.calendarID = a.calendarID and x.code not in ('PX','OS','IA')RIGHT OUTER JOIN
Person AS p INNER JOIN
[Identity] AS i ON p.currentIdentityID = i.identityID AND p.personID = i.personID INNER JOIN
Enrollment AS e ON p.personID = e.personID AND (e.noShow IS NULL OR e.noShow = 0) ON e.calendarID = a.calendarID AND p.personID = a.personID AND
COALESCE (x.status, a.status) IN ('A')AND a.excuseID IN (2169)AND
CASE WHEN x.excuse IS NOT NULL
THEN x.excuse ELSE a.excuse END IN ('U')
WHERE (e.calendarID = 526) AND
(e.structureID = 517) AND
(p.personID IN
(SELECT DISTINCT
student.personID AS [student.personID]
FROM v_AdHocStudent AS student INNER JOIN
v_SchoolCurrent AS sch ON sch.schoolID = student.schoolID
WHERE (1 = 1) AND (student.calendarID = 526)
AND (student.districtID = 883)
AND (student.endYear = 2012)
AND (COALESCE (student.activeToday, 0) = 1)
--(student.teamName = @name)
AND (sch.name = 'SCHOOL')))
and (e.startdate BETWEEN COALESCE (e.startDate,'08/24/2011') AND COALESCE (e.endDate,'10/07/2011'))
GROUP BY p.personID, p.stateID, p.studentNumber, i.lastName,i.firstName, i.middleName, i.suffix, e.grade, pd.seq, pd.name
HAVING (1 = 1) AND (COUNT(a.attendanceID)= 3) OR
(COUNT(a.attendanceID) = 6) OR
(COUNT(a.attendanceID) = 9) OR
(COUNT(a.attendanceID) = 12)
ORDER BY p.personID,pd.name

Any help will ne appreciated.Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-12 : 12:12:39

you need to put results you get each day in a table and then next day you need to check against this table to see if absence is for different period(s) by means of left join and return only ones having null values (no matches) in that table. you cant generate it on the fly as you need the already reported data somewhere to be stored.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rsql
Starting Member

6 Posts

Posted - 2011-10-12 : 12:49:09

thanks for the reply.That helps.should I do this way?

INSERT INTO wps_period_batch_detail1
SELECT p.personID,
p.studentNumber, i.lastName,
i.firstName, i.middleName, e.grade,
pd.name AS periodName,
COUNT(a.attendanceID) AS attendanceCount ,
convert(char(10),GETDATE(),101) as letterdate
--select *
FROM Period AS pd INNER JOIN
Attendance AS a WITH (NOLOCK) ON a.periodID = pd.periodID AND a.date >= '08/24/2011' AND a.date <= '10/11/2011' LEFT OUTER JOIN
AttendanceExcuse AS x WITH (NOLOCK)ON x.excuseID = a.excuseID AND x.calendarID = a.calendarID and x.code not in ('PX','OS','IA')RIGHT OUTER JOIN
Person AS p INNER JOIN
[Identity] AS i ON p.currentIdentityID = i.identityID AND p.personID = i.personID INNER JOIN
Enrollment AS e ON p.personID = e.personID AND (e.noShow IS NULL OR e.noShow = 0) ON e.calendarID = a.calendarID AND p.personID = a.personID AND
COALESCE (x.status, a.status) IN ('A')AND a.excuseID IN (2169)AND
CASE WHEN x.excuse IS NOT NULL
THEN x.excuse ELSE a.excuse END IN ('U')
WHERE (e.calendarID = 526) AND
(e.structureID = 517) AND
(p.personID IN
(SELECT DISTINCT
student.personID AS [student.personID]
FROM v_AdHocStudent AS student INNER JOIN
v_SchoolCurrent AS sch ON sch.schoolID = student.schoolID
WHERE (1 = 1) AND (student.calendarID = 526)
AND (student.districtID = 883)
AND (student.endYear = 2012)
AND (COALESCE (student.activeToday, 0) = 1)
--(student.teamName = @name)
AND (sch.name = 'SCHOOL')))
--and (e.startdate BETWEEN COALESCE (e.startDate,'08/24/2011') AND COALESCE (e.endDate,'10/07/2011'))
GROUP BY p.personID, p.stateID, p.studentNumber, i.lastName,i.firstName, i.middleName, i.suffix, e.grade, pd.seq, pd.name
HAVING (1 = 1) AND (COUNT(a.attendanceID)= 3) OR
(COUNT(a.attendanceID) = 6) OR
(COUNT(a.attendanceID) = 9) OR
(COUNT(a.attendanceID) = 12)
--ORDER BY p.personID,pd.name
UNION ALL
Insert into wps_period_batch_detail1
(SELECT p.personID,
p.studentNumber, i.lastName,
i.firstName, i.middleName, e.grade,
pd.name AS periodName,
COUNT(a.attendanceID) AS attendanceCount ,
convert(char(10),GETDATE(),101) as letterdate
--select *
FROM Period AS pd INNER JOIN
Attendance AS a WITH (NOLOCK) ON a.periodID = pd.periodID AND a.date >= '08/24/2011' AND a.date <= '10/12/2011' LEFT OUTER JOIN
AttendanceExcuse AS x WITH (NOLOCK)ON x.excuseID = a.excuseID AND x.calendarID = a.calendarID and x.code not in ('PX','OS','IA')RIGHT OUTER JOIN
Person AS p INNER JOIN
[Identity] AS i ON p.currentIdentityID = i.identityID AND p.personID = i.personID INNER JOIN
Enrollment AS e ON p.personID = e.personID AND (e.noShow IS NULL OR e.noShow = 0) ON e.calendarID = a.calendarID AND p.personID = a.personID AND
COALESCE (x.status, a.status) IN ('A')AND a.excuseID IN (2169)AND
CASE WHEN x.excuse IS NOT NULL
THEN x.excuse ELSE a.excuse END IN ('U')
WHERE (e.calendarID = 526) AND
(e.structureID = 517) AND
(p.personID IN
(SELECT DISTINCT
student.personID AS [student.personID]
FROM v_AdHocStudent AS student INNER JOIN
v_SchoolCurrent AS sch ON sch.schoolID = student.schoolID
WHERE (1 = 1) AND (student.calendarID = 526)
AND (student.districtID = 883)
AND (student.endYear = 2012)
AND (COALESCE (student.activeToday, 0) = 1)
--(student.teamName = @name)
AND (sch.name = 'SCHOOL')))
--and (e.startdate BETWEEN COALESCE (e.startDate,'08/24/2011') AND COALESCE (e.endDate,'10/07/2011'))
GROUP BY p.personID, p.stateID, p.studentNumber, i.lastName,i.firstName, i.middleName, i.suffix, e.grade, pd.seq, pd.name
HAVING (1 = 1) AND (COUNT(a.attendanceID)= 3) OR
(COUNT(a.attendanceID) = 6) OR
(COUNT(a.attendanceID) = 9) OR
(COUNT(a.attendanceID) = 12))
ORDER BY p.personID,pd.name

And then How should I write the sql?

Now I have four records for jonny today and finally I don't want to see him in the report as nothing has been changed for him.
9 1234 02 3 10/11/2011
9 1234 07 3 10/11/2011
9 1234 02 3 10/12/2011
9 1234 07 3 10/12/2011

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-12 : 13:31:34

sorry i cant see the LEFT JOIN part which you need for getting only the detail not already in table.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rsql
Starting Member

6 Posts

Posted - 2011-10-12 : 14:36:25

So now the data is in the temp table.

I need to select it a way that it does not give me the old data.

Select a1.*
from wps_period_batch_detail1 a1
left join wps_period_batch_detail1 a2 on a2.personid = a1.personid
where a2.period <> a1.period and a2.totalattendance<> a1.totalattendance
Order by a1.personid

Is that how I should do or please correct me...

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-12 : 14:48:20

[code]
Select a1.*
from wps_period_batch_detail1 a1
left join wps_period_batch_detail1 a2 on a2.personid = a1.personid
and a2.period = a1.period and a2.totalattendance = a1.totalattendance
where a2.period is null
Order by a1.personid
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rsql
Starting Member

6 Posts

Posted - 2011-10-12 : 14:56:57

when will the a2.period be null?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-13 : 01:48:10

that will ne null for records that have newly come in wps_period_batch_detail1 (ie new person records or records for existing persons during uncaptured periods)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rsql
Starting Member

6 Posts

Posted - 2011-10-13 : 10:26:38

Thank you Vishak so much for all your help on this.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-13 : 11:03:42

np
you're wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources