|
|
列名 |
データ型 |
有効桁数 |
転送サイズ |
小数以下 |
NULL 許可 |
既定値 |
1 |
KA_年度 |
VARCHAR |
4 |
4 |
|
NO |
|
2 |
KA_コード |
INT |
10 |
4 |
0 |
NO |
|
3 |
KA_名称 |
VARCHAR |
100 |
100 |
|
YES |
|
4 |
KA_数値1 |
INT |
10 |
4 |
0 |
YES |
|
5 |
KA_数値2 |
INT |
10 |
4 |
0 |
YES |
|
6 |
KA_数値3 |
INT |
10 |
4 |
0 |
YES |
|
7 |
KA_数値4 |
INT |
10 |
4 |
0 |
YES |
|
8 |
KA_担当 |
VARCHAR |
20 |
20 |
|
YES |
|
9 |
KA_正式名称 |
VARCHAR |
100 |
100 |
|
YES |
|
10 |
KA_区分 |
VARCHAR |
1 |
1 |
|
YES |
|
|
|
|
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'
| |
|
|
|
|
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_学生,週
| |
|
|
|
|
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_学生,週
| |
|
|
|