asked on

SQL query that pulls all records when a criteria is met

We are a school district that sends out 3, 5 and 7 day absense letters to parents of our students. Each time a student is absent a record is written to the SQL database that includes the studentID, classID, date, and reason code.

I need to write a query that pulls all records for a student if a there has been a record written to the database past a specified date. In other words, if a Parent has received a 3-day letter and the student hasn't been absent since that 3-day letter query was run, I don't want the query to pull in that student's data.

We are actually doing the letters using Crystal Reports 10 but if I can figure out the SQL syntax, I think I'll be good to go.

Any assistance will be greatly appreciated.

Eric Bowser

Lowfatspread

Hi bowsere,
its not clear what your data looks like

can you provide some sample data

also what does classid refer to ?
is it a lesson type indicator so that an absent student could have many abcence records per day?

how do you know which students/parents have/had letters ... for separated parents do both families get letters?

what do you do about weekends/holidays?
confirm what 3,5,7 day absence actually means
(ie that length of absence or that period without a confirmation from a parent...)

hth

Cheers!

Scott Pletcher

I, too, am not 100% sure of your requirements, but something like below should be close, assuming that one class absence counts as a full absence for purposes of a letter.

So would you want to report a student that had a 4th absence but not a 5th? Or are you trying just to determine students who need the letters described? I'll assume the latter, that is, letters only for the first occurence of the 3rd, 5th and 7th absences.

Have *not* tested this yet.

DECLARE @startDate DATETIME
DECLARE @previousDate DATETIME --date of prev. letter, if any

SET @startDate = '09/05/2005'
SET @previousDate = '10/05/2005'

SELECT currAbs.studentID, COUNT(currAbs.studentID) AS [currAbsCount]
FROM (
      SELECT DISTINCT studentID, date
      FROM absences
      WHERE date > @previousDate
) AS currAbs
LEFT OUTER JOIN (
      SELECT studentID, COUNT(*) AS [prevAbsCount]
      FROM (
            SELECT DISTINCT studentID, date
            FROM absences
            WHERE date >= @startDate AND date <= @previousDate
      ) AS abs1
      GROUP BY studentID
) AS prevAbs ON prevAbs.studentID = currAbs.studentID
WHERE prevAbs.prevAbsCount + currAbs.currAbsCount IN (3, 5)
OR (prevAbs.prevAbsCount + currAbs.currAbsCount >= 7 AND prevAbs.prevAbsCount < 7)

Btw, do you have a table that tells you which students have already received letters and based on what dates for how many absences?

Scott Pletcher

OK, I've cleaned up the code quite a bit and done a quick, simple test on it.

create table absences (
      studentID INT,
      date datetime,
      classID varchar(30),
      reasonCode varchar(30)
      )
truncate table absences
set nocount on
insert into absences values(1, '2005-10-01', 'class1', 'reason1')
insert into absences values(1, '2005-10-02', 'class2', 'reason2')
insert into absences values(1, '2005-10-03', 'class3', 'reason3')
insert into absences values(1, '2005-10-24', 'class4', 'reason4')
insert into absences values(1, '2005-10-25', 'class5', 'reason5')
set nocount off

DECLARE @startDate DATETIME
DECLARE @previousDate DATETIME --date of prev. letter, if any

SET @startDate = '09/05/2005'
SET @previousDate = '10/05/2005'

SELECT studentID,
      CASE WHEN totalAbsences IN (3, 5) THEN totalAbsences ELSE 7 END AS [Letter Type]
FROM (
      SELECT studentID,
            SUM(CASE WHEN date >= @startDate AND date <= @previousDate THEN 1 ELSE 0 END)
                  AS prevAbsences,
            SUM(1) AS totalAbsences
      FROM (
            SELECT DISTINCT studentID, date
            FROM absences
            WHERE date >= @startDate
      ) AS abs1
      GROUP BY studentID
      HAVING (SUM(1) IN (3, 5))
      OR (SUM(1) >= 7 AND
            SUM(CASE WHEN date >= @startDate AND date <= @previousDate THEN 1 ELSE 0 END) < 7)
) AS totalAbs

ASKER CERTIFIED SOLUTION

DarthMod

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial