-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 01/28/07 15:18, garry saddington wrote: > On Sun, 2007-01-28 at 09:57 -0600, Ron Johnson wrote: >> On 01/28/07 07:05, garry saddington wrote: [snip] >> 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? I'd use the T_CALENDAR table, modified for your purposes. (It's a "static" that we create on every database.) We populate it with 22 years of dates. You'll have to write a small procedure to do it. CREATE TABLE T_CALENDAR ( DATE_ANSI DATE, YEAR_NUM SMALLINT, MONTH_NUM SMALLINT, DAY_OF_MONTH SMALLINT, DAY_OF_WEEK SMALLINT, JULIAN_DAY SMALLINT, DAY_OF_WEEK SMALLINT, IS_SCHOOL_DAY BOOL, SCHOOL_YEAR SMALLINT, -- "2006" for the 2006/07 school year SCHOOL_MONTH SMALLINT); -- 1 for August, 2 for September, etc Then, join T_CALENDAR to ATTENDANCE, WHERE DATE_ANSI = ENTERED AND DAY_OF_WEEK = 1 AND IS_SCHOOL_DAY = TRUE AND SCHOOL_YEAR = 2006; Making that join into a view and then, as Joris suggested, connect it to a spreadsheet. -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFFvRmAS9HxQb37XmcRAvpcAKDQ7kjs9/rMb39w5JYRRTl65mYoKQCfVVUm NTv6r6Kzu8T5D+SS8vxwFjs= =VDXa -----END PGP SIGNATURE-----
