create view V_週間出欠 as
select
AB_年度,AB_学生,AB_年月日,AB_時限,AB_FLG,AB_科目
,KA_区分
,datepart(week,convert(datetime,AB_年月日,12)-105) as 週
,
case AB_FLG
when '0' then 'Y'
else NULL
end as 出席
,
case AB_FLG
when '1' then 'Y'
else NULL
end as 欠席
,
case AB_FLG
when '2' then 'Y'
when '3' then 'Y'
else NULL
end as 遅刻早退
,
case AB_FLG
when '4' then 'Y'
else NULL
end as 公欠
,
case AB_FLG
when '5' then 'Y'
else NULL
end as 調整
from TR_出席,TR_科目
where
AB_年度 = KA_年度
and AB_科目 = KA_コード
UNION による集計用データを調整した VIEW の作成
create view V_週間出欠2 as
select *
from V_週間出欠
union all
select *
from V_週間出欠
where
KA_区分 = '1'
対象年度用 個人別、週別集計 VIEW の作成
create view V_週間出欠3 as
select
AB_学生
,週
,count(*) as コマ数
,count(出席) as 出席
,count(欠席) as 欠席
,count(遅刻早退) as 遅刻早退
,count(公欠) as 公欠
,count(調整) as 調整
from V_週間出欠2
where AB_年度 = '2003'
group by AB_学生,週
個人累計を 週 = 0 で UNION 追加
create view V_週間出欠4 as
select * from V_週間出欠3
union all
select
AB_学生
,0 as 週
,count(*) as コマ数
,count(出席) as 出席
,count(欠席) as 欠席
,count(遅刻早退) as 遅刻早退
,count(公欠) as 公欠
,count(調整) as 調整
from V_週間出欠2
where AB_年度 = '2003'
and 週 >= 1
group by AB_学生
該当年度の全ての学生の目的データ
select V_週間出欠4.*,氏名,フリガナ
from V_週間出欠4,T_学生
where
T_学生.年度 = '2003'
and AB_学生 = コード
order by AB_学生,週
