SELECT DISTINCT
users.su01 as 工號,
users.SU02 as 姓名,
substring(users.su03,29,6) as 部門,
MONTH(dateadd(month,-1,getdate())) as 月份,
day(dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))) as '月天數/天',
isnull(sum(a.days),0) as 年假,
isnull(sum(b.days),0) as 帶薪病假,
isnull(sum(c.days),0) as 婚假,
isnull(sum(d.days),0) as 喪假,
isnull(sum(e.days),0) as 產檢假,
isnull(sum(f.days),0) as 陪產假,
isnull(sum(g.days),0) as 探親假,
isnull(sum(h.days),0) as 補休,
isnull(sum(i.days),0) as 工傷,
isnull(sum(j.days),0) as 產假,
isnull(sum(k.days),0) as 病假,
isnull(sum(l.days),0) as 事假,
isnull(sum(n.days),0) as 曠工,
isnull(sum(m.days),0) as 無薪假,
30-isnull(a.days,0)-isnull(b.days,0)-isnull(c.days,0)-isnull(d.days,0)-isnull(e.days,0)-isnull(f.days,0)-isnull(g.days,0)-isnull(h.days,0)-isnull(i.days,0)-isnull(j.days,0)-isnull(k.days,0)-isnull(l.days,0)-isnull(n.days,0)-isnull(m.days,0) as 實際出勤,
30-isnull(a.days,0)-isnull(b.days,0)-isnull(c.days,0)-isnull(d.days,0)-isnull(e.days,0)-isnull(f.days,0)-isnull(g.days,0)-isnull(h.days,0)-isnull(i.days,0)-isnull(j.days,0)-isnull(k.days,0)-isnull(l.days,0)-isnull(n.days,0)-isnull(m.days,0)+isnull(a.days,0)+isnull(b.days,0)+isnull(e.days,0)+isnull(g.days,0)+isnull(h.days,0)+isnull(d.days,0)+isnull(i.days,0)+isnull(f.days,0)+isnull(c.days,0) as 計薪天數
FROM FE_BASE5..SYS_USERS
join
(SELECT
su01,
su02,
su03
FROM FE_BASE5..SYS_USERS)
users on users.su01=FE_BASE5..SYS_USERS.su01
left join
(SELECT su02 name,a1.fn16 days FROM FE_BASE5..SYS_USERS
left join
(SELECT * FROM FE_APP5..FT_1_8D06B1699979D8FC5C01
WHERE SPFLAG>'0' and fn07='年假'and fn05>='2020-12-01'and fn10<='2020-12-31')
a1 on a1.fn04=FE_BASE5..SYS_USERS.su00
where fn05>='2020-12-01'and fn10<='2020-12-31')
a on a.name=FE_BASE5..SYS_USERS.su02
left join
(SELECT su02 name,b1.fn16 days FROM FE_BASE5..SYS_USERS
left join
(SELECT * FROM FE_APP5..FT_1_8D06B1699979D8FC5C01
WHERE SPFLAG>'0' and fn07='帶薪病假'and fn05>='2020-12-01'and fn10<='2020-12-31')
b1 on b1.fn04=FE_BASE5..SYS_USERS.su00
where fn05>='2020-12-01'and fn10<='2020-12-31')
b on b.name=FE_BASE5..SYS_USERS.su02
left join
(SELECT su02 name,c1.fn16 days FROM FE_BASE5..SYS_USERS
left join
(SELECT * FROM FE_APP5..FT_1_8D06B1699979D8FC5C01
WHERE SPFLAG>'0' and fn07='婚假'and fn05>='2020-12-01'and fn10<='2020-12-31')
c1 on c1.fn04=FE_BASE5..SYS_USERS.su00
where fn05>='2020-12-01'and fn10<='2020-12-31')
c on c.name=FE_BASE5..SYS_USERS.su02
left join
(SELECT su02 name,d1.fn16 days FROM FE_BASE5..SYS_USERS
left join
(SELECT * FROM FE_APP5..FT_1_8D06B1699979D8FC5C01
WHERE SPFLAG>'0' and fn07='喪假'and fn05>='2020-12-01'and fn10<='2020-12-31')
d1 on d1.fn04=FE_BASE5..SYS_USERS.su00
where fn05>='2020-12-01'and fn10<='2020-12-31')
d on d.name=FE_BASE5..SYS_USERS.su02
left join
(SELECT su02 name,e1.fn16 days FROM FE_BASE5..SYS_USERS
left join
(SELECT * FROM FE_APP5..FT_1_8D06B1699979D8FC5C01
WHERE SPFLAG>'0' and fn07='產檢假'and fn05>='2020-12-01'and fn10<='2020-12-31')
e1 on e1.fn04=FE_BASE5..SYS_USERS.su00
where fn05>='2020-12-01'and fn10<='2020-12-31')
e on e.name=FE_BASE5..SYS_USERS.su02
left join
(SELECT su02 name,f1.fn16 days FROM FE_BASE5..SYS_USERS
left join
(SELECT * FROM FE_APP5..FT_1_8D06B1699979D8FC5C01
WHERE SPFLAG>'0' and fn07='陪產假'and fn05>='2020-12-01'and fn10<='2020-12-31')
f1 on f1.fn04=FE_BASE5..SYS_USERS.su00
where fn05>='2020-12-01'and fn10<='2020-12-31')
f on f.name=FE_BASE5..SYS_USERS.su02
left join
(SELECT su02 name,g1.fn16 days FROM FE_BASE5..SYS_USERS
left join
(SELECT * FROM FE_APP5..FT_1_8D06B1699979D8FC5C01
WHERE SPFLAG>'0' and fn07='探親假'and fn05>='2020-12-01'and fn10<='2020-12-31')
g1 on g1.fn04=FE_BASE5..SYS_USERS.su00
where fn05>='2020-12-01'and fn10<='2020-12-31')
g on g.name=FE_BASE5..SYS_USERS.su02
left join
(SELECT su02 name,h1.fn16 days FROM FE_BASE5..SYS_USERS
left join
(SELECT * FROM FE_APP5..FT_1_8D06B1699979D8FC5C01
WHERE SPFLAG>'0' and fn07='補休'and fn05>='2020-12-01'and fn10<='2020-12-31')
h1 on h1.fn04=FE_BASE5..SYS_USERS.su00
where fn05>='2020-12-01'and fn10<='2020-12-31')
h on h.name=FE_BASE5..SYS_USERS.su02
left join
(SELECT su02 name,i1.fn16 days FROM FE_BASE5..SYS_USERS
left join
(SELECT * FROM FE_APP5..FT_1_8D06B1699979D8FC5C01
WHERE SPFLAG>'0' and fn07='工傷'and fn05>='2020-12-01'and fn10<='2020-12-31')
i1 on i1.fn04=FE_BASE5..SYS_USERS.su00
where fn05>='2020-12-01'and fn10<='2020-12-31')
i on i.name=FE_BASE5..SYS_USERS.su02
left join
(SELECT su02 name,j1.fn16 days FROM FE_BASE5..SYS_USERS
left join
(SELECT * FROM FE_APP5..FT_1_8D06B1699979D8FC5C01
WHERE SPFLAG>'0' and fn07='產假'and fn05>='2020-12-01'and fn10<='2020-12-31')
j1 on j1.fn04=FE_BASE5..SYS_USERS.su00
where fn05>='2020-12-01'and fn10<='2020-12-31')
j on j.name=FE_BASE5..SYS_USERS.su02
left join
(SELECT su02 name,k1.fn16 days FROM FE_BASE5..SYS_USERS
left join
(SELECT * FROM FE_APP5..FT_1_8D06B1699979D8FC5C01
WHERE SPFLAG>'0' and fn07='病假'and fn05>='2020-12-01'and fn10<='2020-12-31')
k1 on k1.fn04=FE_BASE5..SYS_USERS.su00
where fn05>='2020-12-01'and fn10<='2020-12-31')
k on k.name=FE_BASE5..SYS_USERS.su02
left join
(SELECT su02 name,l1.fn16 days FROM FE_BASE5..SYS_USERS
left join
(SELECT * FROM FE_APP5..FT_1_8D06B1699979D8FC5C01
WHERE SPFLAG>'0' and fn07='事假'and fn05>='2020-12-01'and fn10<='2020-12-31')
l1 on l1.fn04=FE_BASE5..SYS_USERS.su00
where fn05>='2020-12-01'and fn10<='2020-12-31')
l on l.name=FE_BASE5..SYS_USERS.su02
left join
(SELECT su02 name,n1.fn16 days FROM FE_BASE5..SYS_USERS
left join
(SELECT * FROM FE_APP5..FT_1_8D06B1699979D8FC5C01
WHERE SPFLAG>'0' and fn07='曠工'and fn05>='2020-12-01'and fn10<='2020-12-31')
n1 on n1.fn04=FE_BASE5..SYS_USERS.su00
where fn05>='2020-12-01'and fn10<='2020-12-31')
n on n.name=FE_BASE5..SYS_USERS.su02
left join
(SELECT su02 name,m1.fn16 days FROM FE_BASE5..SYS_USERS
left join
(SELECT * FROM FE_APP5..FT_1_8D06B1699979D8FC5C01
WHERE SPFLAG>'0' and fn07='無薪假'and fn05>='2020-12-01'and fn10<='2020-12-31')
m1 on m1.fn04=FE_BASE5..SYS_USERS.su00
where fn05>='2020-12-01'and fn10<='2020-12-31')
m on m.name=FE_BASE5..SYS_USERS.su02
where users.su01!='009' and
users.su01!='673' and
users.su01!='test'and
users.su01!='001' and
users.su01!='011'and
users.su01!='jim'and
substring(users.su03,15,13)='東莞市效迅實業投資有限公司'
group by users.su01,
users.su02,
users.su03,
a.days,
b.days,
c.days,
d.days,
e.days,
f.days,
g.days,
h.days,
i.days,
j.days,
k.days,
l.days,
n.days,
m.days
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/246685.html
標籤：疑難問題