On Sun, 2007-01-28 at 09:57 -0600, Ron Johnson wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On 01/28/07 07:05, garry saddington wrote:
> > I have a table definition such as:
> >
> > CREATE TABLE attendance
> > (
> > attendanceid serial primary key,
> > entered date DEFAULT current_date NOT NULL,
> > absent boolean,
> > authorization text default 'N',
> > timeperiod char(2) check(timeperiod in('AM','PM')),
> > days varchar(10),
> > studentid int,
> > unique(entered,timeperiod,studentid)
> > )
> >
> > Which is used to record school attendance data. I am now trying to write
> > a query to identify trends in absences by counting the days column and
> > returning any student that has repeated absences on certain days. I am
> > struggling to return anything that does not need further manipulation in
> > Python before being useful.
> > Does anyone have any ideas?
>
> When you say "certain days", you mean "days of the week"?
>
> If so, create a view like:
> CREATE VIEW V_DAY_ABSENCES AS
> SELECT ENTERED,
> AUTHORIZATION,
> TIMEPERIOD,
> DAYS,
> STUDENTID,
> DOW(CAST(ENTERED AS TIMESTAMP)) AS WEEKDAY
> FROM ATTENDANCE
> WHERE ABSENT = TRUE;
>
> Then, this query should do what you want:
> SELECT STUDENTID,
> TIMEPERIOD,
> WEEKDAY,
> COUNT(*)
> FROM V_DAY_ABSENSES
> GROUP BY STUDENTID,
> TIMEPERIOD,
> WEEKDAY
> HAVING COUNT(*) > 3;
Thank you, this works great. But I have another problem: Is it possible
to identify absences in consecutive weeks on the same day. EG. If a
pupil has a pattern of having every monday AM off school, how could that
be identified?
Regards
Garry