hsiung.博格 ERP軟體

範例：

select 統計.編號,member.代號,member.姓名,統計.事假,統計.病假,統計.休假,統計.喪假,統計.婚假,統計.產假,統計.公假,統計.差假,(isnull(統計.事假,0)+isnull(統計.病假,0)+isnull(統計.休假,0)+isnull(統計.喪假,0)+isnull(統計.婚假,0)+isnull(統計.產假,0)+isnull(統計.公假,0)+isnull(統計.差假,0)) as 總日數,(abs(isnull(統計.事假,0)-7)+(isnull(統計.事假,0)-7))/2 as 超事,(abs(isnull(統計.病假,0)-isnull(leaveallowed.核住院,0))+(isnull(統計.病假,0)-isnull(leaveallowed.核住院,0)))/2 as 超病,(abs(isnull(統計.休假,0)-isnull(leaveallowed.核休假,0)-isnull(leaveallowed.核特休,0))+(isnull(統計.休假,0)-isnull(leaveallowed.核休假,0)-isnull(leaveallowed.核特休,0)))/2 as 超休,leaveallowed.核住院,leaveallowed.核休假,leaveallowed.核特休

from (

select 編號,[1] as 事假,[2] as 病假,[3] as 休假,[4] as 喪假,[5] as 婚假,[6] as 產假,[7] as 公假,[8] as 差假

from (

select 假別,(天數+時數/8) as 總天數,編號from leaverec where convert(int,起日) >= @day1 and convert(int,起日) <= @day2) liming

pivot

(sum (總天數)

for 假別in ([1],[2],[3],[4],[5],[6],[7],[8])

)as pvt

) as 統計

inner join member on 統計.編號=member.編號left join leaveallowed on (member.編號=leaveallowed.編號and leaveallowed.學年=@schyear) order by 統計.編號