Refectio

工作備忘錄
紀錄這一次所寫的SQL語法以及Excel Vlookup注意事項
About Excel Function
Vlookup所選擇的Table Array不能超過65536免得底下的資料會變成#N/A

About SQL
#1
select a.dept_key,sum(convert(int,session))
from
(
select session,left(dept_no,2) as dept_key,dept_no from token_usage
where dept_no <> '#N/A'
)as a
group by a.dept_key
需注意欄位的屬性，免得無法總計欄位中的數值
#2
select a.Dep_key,sum(a.token)
from
(
select left(Dept_No, 2) as Dep_key,Dept_no,Count(*) as token
from token_asset
where ID_NO <> 'null' and Dept_No <> 'null'
group by Dept_No
) as a group by a.Dep_key

Long-term plan:
1.Reporting by Schedule
2.auto send report to director level
3.constructing......