Re: counting query

-----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-----