因應萬惡的一例一休 (完全無相關)
其實是 特休被立法要求一律要轉發成現金給員工，
所以很多公司開始嚴格要控管員工休假狀況，
鼎新人資系統 將休假切成三個部分(特休、加班轉補休[調休]、其他休假 [病假][事假][公差] ….)，就分開來整理弄成三個VIEW
再把三個VIEW union 起來 ，丟到BI上跑分析報表。
可以清楚看出來 年度 應休、已休、未休
逐月已休假狀況 ， 如果請太少假的再自動發mail 通知本人、主管。 提醒預先安排相關休假
(很貼心吧 !)
TWALPlanInfo_V
/*特休*/
Create View TWALPlanInfo_V as
select
[Employee].[Code]as [EmpNo],
[TWALPlanInfo].[year] as [統計年度] ,
[假別] = ‘特休’,
(TWALPlanInfo.ThisYearAmount-TWALPlanInfo.BalanceLastYear) as [本年可休時數]
,(TWALPlanInfo.ActualAmount-TWALPlanInfo.BalanceActual) as [本年已休時數]
,(TWALPlanInfo.RemainderAmount-TWALPlanInfo.BalanceRemainder) as [本年未休時數] ,
(select sum(Amount) FROM TWALRegInfo
where Month (TWALRegInfo.Date) = ‘1’ and [TWALRegInfo].[EmployeeId] = [Employee].[EmployeeId] and Year (TWALRegInfo.Date) = [TWALPlanInfo].[year] )
as [1月] ,
(select sum(Amount) FROM TWALRegInfo
where Month (TWALRegInfo.Date) = ‘2’ and [TWALRegInfo].[EmployeeId] = [Employee].[EmployeeId] and Year (TWALRegInfo.Date) = [TWALPlanInfo].[year] )
as [2月] ,
(select sum(Amount) FROM TWALRegInfo
where Month (TWALRegInfo.Date) = ‘3’ and [TWALRegInfo].[EmployeeId] = [Employee].[EmployeeId] and Year (TWALRegInfo.Date) = [TWALPlanInfo].[year] )
as [3月] ,
(select sum(Amount) FROM TWALRegInfo
where Month (TWALRegInfo.Date) = ‘4’ and [TWALRegInfo].[EmployeeId] = [Employee].[EmployeeId] and Year (TWALRegInfo.Date) = [TWALPlanInfo].[year] )
as [4月] ,
(select sum(Amount) FROM TWALRegInfo
where Month (TWALRegInfo.Date) = ‘5’ and [TWALRegInfo].[EmployeeId] = [Employee].[EmployeeId] and Year (TWALRegInfo.Date) = [TWALPlanInfo].[year] )
as [5月] ,
(select sum(Amount) FROM TWALRegInfo
where Month (TWALRegInfo.Date) = ‘6’ and [TWALRegInfo].[EmployeeId] = [Employee].[EmployeeId] and Year (TWALRegInfo.Date) = [TWALPlanInfo].[year] )
as [6月] ,
(select sum(Amount) FROM TWALRegInfo
where Month (TWALRegInfo.Date) = ‘7’ and [TWALRegInfo].[EmployeeId] = [Employee].[EmployeeId] and Year (TWALRegInfo.Date) = [TWALPlanInfo].[year] )
as [7月] ,
(select sum(Amount) FROM TWALRegInfo
where Month (TWALRegInfo.Date) = ‘8’ and [TWALRegInfo].[EmployeeId] = [Employee].[EmployeeId] and Year (TWALRegInfo.Date) = [TWALPlanInfo].[year] )
as [8月] ,
(select sum(Amount) FROM TWALRegInfo
where Month (TWALRegInfo.Date) = ‘9’ and [TWALRegInfo].[EmployeeId] = [Employee].[EmployeeId] and Year (TWALRegInfo.Date) = [TWALPlanInfo].[year] )
as [9月] ,
(select sum(Amount) FROM TWALRegInfo
where Month (TWALRegInfo.Date) = ’10’ and [TWALRegInfo].[EmployeeId] = [Employee].[EmployeeId] and Year (TWALRegInfo.Date) = [TWALPlanInfo].[year] )
as [10月] ,
(select sum(Amount) FROM TWALRegInfo
where Month (TWALRegInfo.Date) = ’11’ and [TWALRegInfo].[EmployeeId] = [Employee].[EmployeeId] and Year (TWALRegInfo.Date) = [TWALPlanInfo].[year] )
as [11月] ,
(select sum(Amount) FROM TWALRegInfo
where Month (TWALRegInfo.Date) = ’12’ and [TWALRegInfo].[EmployeeId] = [Employee].[EmployeeId] and Year (TWALRegInfo.Date) = [TWALPlanInfo].[year] )
as [12月]
from TWALPlanInfo
LEFT JOIN [Employee] ON [Employee].[EmployeeId]=TWALPlanInfo.[EmployeeId]
where 1=1
and [TWALPlanInfo].[year] is Not Null
AttendanceOTResult_V
/*調休*/
Create View AttendanceOTResult_V as
SELECT
[AttendanceOTAdjust_AttendanceOTResult_AttendanceOTResultd_Employee_EmployeeId].[Code] AS EmpNo,
Year([AttendanceOTAdjust].[AdjustDate]) as [統計年度],
[假別]= ‘調休’,
SUM([AttendanceOTAdjust].[AdjustHours]) AS [本年可休時數],
SUM([AttendanceOTAdjust].[ActualAdjustHours]) AS [本年已休時數] ,
SUM([AttendanceOTAdjust].[AdjustHours]) – SUM([AttendanceOTAdjust].[ActualAdjustHours]) AS [本年未休時數] ,
( SELECT SUM([Hours]) FROM [AttendanceOTRestDaily] where Month([AttendanceOTRestDaily].[Date]) = ‘1’
and Year([AttendanceOTRestDaily].[Date]) = Year([AttendanceOTAdjust].[AdjustDate])
and [AttendanceOTRestDaily].[EmployeeId] = [AttendanceOTAdjust_AttendanceOTResult_AttendanceOTResultd_Employee_EmployeeId].[EmployeeId] )
as [1月] ,
( SELECT SUM([Hours]) FROM [AttendanceOTRestDaily] where Month([AttendanceOTRestDaily].[Date]) = ‘2’
and Year([AttendanceOTRestDaily].[Date]) = Year([AttendanceOTAdjust].[AdjustDate])
and [AttendanceOTRestDaily].[EmployeeId] = [AttendanceOTAdjust_AttendanceOTResult_AttendanceOTResultd_Employee_EmployeeId].[EmployeeId] )
as [2月] ,
( SELECT SUM([Hours]) FROM [AttendanceOTRestDaily] where Month([AttendanceOTRestDaily].[Date]) = ‘3’
and Year([AttendanceOTRestDaily].[Date]) = Year([AttendanceOTAdjust].[AdjustDate])
and [AttendanceOTRestDaily].[EmployeeId] = [AttendanceOTAdjust_AttendanceOTResult_AttendanceOTResultd_Employee_EmployeeId].[EmployeeId] )
as [3月] ,
( SELECT SUM([Hours]) FROM [AttendanceOTRestDaily] where Month([AttendanceOTRestDaily].[Date]) = ‘4’
and Year([AttendanceOTRestDaily].[Date]) = Year([AttendanceOTAdjust].[AdjustDate])
and [AttendanceOTRestDaily].[EmployeeId] = [AttendanceOTAdjust_AttendanceOTResult_AttendanceOTResultd_Employee_EmployeeId].[EmployeeId] )
as [4月] ,
( SELECT SUM([Hours]) FROM [AttendanceOTRestDaily] where Month([AttendanceOTRestDaily].[Date]) = ‘5’
and Year([AttendanceOTRestDaily].[Date]) = Year([AttendanceOTAdjust].[AdjustDate])
and [AttendanceOTRestDaily].[EmployeeId] = [AttendanceOTAdjust_AttendanceOTResult_AttendanceOTResultd_Employee_EmployeeId].[EmployeeId] )
as [5月] ,
( SELECT SUM([Hours]) FROM [AttendanceOTRestDaily] where Month([AttendanceOTRestDaily].[Date]) = ‘6’
and Year([AttendanceOTRestDaily].[Date]) = Year([AttendanceOTAdjust].[AdjustDate])
and [AttendanceOTRestDaily].[EmployeeId] = [AttendanceOTAdjust_AttendanceOTResult_AttendanceOTResultd_Employee_EmployeeId].[EmployeeId] )
as [6月] ,
( SELECT SUM([Hours]) FROM [AttendanceOTRestDaily] where Month([AttendanceOTRestDaily].[Date]) = ‘7’
and Year([AttendanceOTRestDaily].[Date]) = Year([AttendanceOTAdjust].[AdjustDate])
and [AttendanceOTRestDaily].[EmployeeId] = [AttendanceOTAdjust_AttendanceOTResult_AttendanceOTResultd_Employee_EmployeeId].[EmployeeId] )
as [7月] ,
( SELECT SUM([Hours]) FROM [AttendanceOTRestDaily] where Month([AttendanceOTRestDaily].[Date]) = ‘8’
and Year([AttendanceOTRestDaily].[Date]) = Year([AttendanceOTAdjust].[AdjustDate])
and [AttendanceOTRestDaily].[EmployeeId] = [AttendanceOTAdjust_AttendanceOTResult_AttendanceOTResultd_Employee_EmployeeId].[EmployeeId] )
as [8月] ,
( SELECT SUM([Hours]) FROM [AttendanceOTRestDaily] where Month([AttendanceOTRestDaily].[Date]) = ‘9’
and Year([AttendanceOTRestDaily].[Date]) = Year([AttendanceOTAdjust].[AdjustDate])
and [AttendanceOTRestDaily].[EmployeeId] = [AttendanceOTAdjust_AttendanceOTResult_AttendanceOTResultd_Employee_EmployeeId].[EmployeeId] )
as [9月] ,
( SELECT SUM([Hours]) FROM [AttendanceOTRestDaily] where Month([AttendanceOTRestDaily].[Date]) = ’10’
and Year([AttendanceOTRestDaily].[Date]) = Year([AttendanceOTAdjust].[AdjustDate])
and [AttendanceOTRestDaily].[EmployeeId] = [AttendanceOTAdjust_AttendanceOTResult_AttendanceOTResultd_Employee_EmployeeId].[EmployeeId] )
as [10月] ,
( SELECT SUM([Hours]) FROM [AttendanceOTRestDaily] where Month([AttendanceOTRestDaily].[Date]) = ’11’
and Year([AttendanceOTRestDaily].[Date]) = Year([AttendanceOTAdjust].[AdjustDate])
and [AttendanceOTRestDaily].[EmployeeId] = [AttendanceOTAdjust_AttendanceOTResult_AttendanceOTResultd_Employee_EmployeeId].[EmployeeId] )
as [11月] ,
( SELECT SUM([Hours]) FROM [AttendanceOTRestDaily] where Month([AttendanceOTRestDaily].[Date]) = ’12’
and Year([AttendanceOTRestDaily].[Date]) = Year([AttendanceOTAdjust].[AdjustDate])
and [AttendanceOTRestDaily].[EmployeeId] = [AttendanceOTAdjust_AttendanceOTResult_AttendanceOTResultd_Employee_EmployeeId].[EmployeeId] )
as [12月]
FROM [AttendanceOTAdjust] AS [AttendanceOTAdjust]
LEFT JOIN [AttendanceOTResult] AS [AttendanceOTAdjust_AttendanceOTResult_AttendanceOTResultd] ON [AttendanceOTAdjust].[AttendanceOTResultd]=[AttendanceOTAdjust_AttendanceOTResult_AttendanceOTResultd].[AttendanceOTResultId]
LEFT JOIN [Employee] AS [AttendanceOTAdjust_AttendanceOTResult_AttendanceOTResultd_Employee_EmployeeId] ON [AttendanceOTAdjust_AttendanceOTResult_AttendanceOTResultd].[EmployeeId]=[AttendanceOTAdjust_AttendanceOTResult_AttendanceOTResultd_Employee_EmployeeId].[EmployeeId]
where
1=1
AND ( [AttendanceOTAdjust_AttendanceOTResult_AttendanceOTResultd].[IsPlanAdjust] = ‘1’ OR
[AttendanceOTAdjust].[NotChange] = ‘1’ )
GROUP BY [AttendanceOTAdjust_AttendanceOTResult_AttendanceOTResultd_Employee_EmployeeId].[Code],
[AttendanceOTAdjust_AttendanceOTResult_AttendanceOTResultd_Employee_EmployeeId].[CnName],
[AttendanceOTAdjust_AttendanceOTResult_AttendanceOTResultd_Employee_EmployeeId].[EmployeeId] ,
Year([AttendanceOTAdjust].[AdjustDate])
AttendanceLeaveInfo_V
/*其餘假別*/
USE [HRMDB]
GO
/****** 物件: View [dbo].[AttendanceLeaveInfo_V] 指令碼日期: 04/20/2017 09:09:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER view [dbo].[AttendanceLeaveInfo_V] as
SELECT
Employee.Code as [EmpNo],
Year(AttendanceLeaveInfo.Date) as [統計年度] ,
AttendanceType.Name as [假別],
sum([AttendanceLeaveInfo].[Hours]) as [本年可休時數] ,
sum([AttendanceLeaveInfo].[Hours]) as [本年已休時數] ,
[本年未休時數] = 0 ,
(select sum(Hours) FROM AttendanceLeaveInfo as AT
where Month (AT.Date) = ‘1’ and [AT].[Attendancetypeid] = [AttendanceLeaveInfo].[Attendancetypeid] and [AT].[EmployeeId] = [Employee].[EmployeeId] and Year (AT.Date) = Year(AttendanceLeaveInfo.Date) )
as [1月] ,
(select sum(Hours) FROM AttendanceLeaveInfo as AT
where Month (AT.Date) = ‘2’ and [AT].[Attendancetypeid] = [AttendanceLeaveInfo].[Attendancetypeid] and [AT].[EmployeeId] = [Employee].[EmployeeId] and Year (AT.Date) = Year(AttendanceLeaveInfo.Date) )
as [2月] ,
(select sum(Hours) FROM AttendanceLeaveInfo as AT
where Month (AT.Date) = ‘3’ and [AT].[Attendancetypeid] = [AttendanceLeaveInfo].[Attendancetypeid] and [AT].[EmployeeId] = [Employee].[EmployeeId] and Year (AT.Date) = Year(AttendanceLeaveInfo.Date) )
as [3月] ,
(select sum(Hours) FROM AttendanceLeaveInfo as AT
where Month (AT.Date) = ‘4’ and [AT].[Attendancetypeid] = [AttendanceLeaveInfo].[Attendancetypeid] and [AT].[EmployeeId] = [Employee].[EmployeeId] and Year (AT.Date) = Year(AttendanceLeaveInfo.Date) )
as [4月] ,
(select sum(Hours) FROM AttendanceLeaveInfo as AT
where Month (AT.Date) = ‘5’ and [AT].[Attendancetypeid] = [AttendanceLeaveInfo].[Attendancetypeid] and [AT].[EmployeeId] = [Employee].[EmployeeId] and Year (AT.Date) = Year(AttendanceLeaveInfo.Date) )
as [5月] ,
(select sum(Hours) FROM AttendanceLeaveInfo as AT
where Month (AT.Date) = ‘6’ and [AT].[Attendancetypeid] = [AttendanceLeaveInfo].[Attendancetypeid] and [AT].[EmployeeId] = [Employee].[EmployeeId] and Year (AT.Date) = Year(AttendanceLeaveInfo.Date) )
as [6月] ,
(select sum(Hours) FROM AttendanceLeaveInfo as AT
where Month (AT.Date) = ‘7’ and [AT].[Attendancetypeid] = [AttendanceLeaveInfo].[Attendancetypeid] and [AT].[EmployeeId] = [Employee].[EmployeeId] and Year (AT.Date) = Year(AttendanceLeaveInfo.Date) )
as [7月] ,
(select sum(Hours) FROM AttendanceLeaveInfo as AT
where Month (AT.Date) = ‘8’ and [AT].[Attendancetypeid] = [AttendanceLeaveInfo].[Attendancetypeid] and [AT].[EmployeeId] = [Employee].[EmployeeId] and Year (AT.Date) = Year(AttendanceLeaveInfo.Date) )
as [8月] ,
(select sum(Hours) FROM AttendanceLeaveInfo as AT
where Month (AT.Date) = ‘9’ and [AT].[Attendancetypeid] = [AttendanceLeaveInfo].[Attendancetypeid] and [AT].[EmployeeId] = [Employee].[EmployeeId] and Year (AT.Date) = Year(AttendanceLeaveInfo.Date) )
as [9月] ,
(select sum(Hours) FROM AttendanceLeaveInfo as AT
where Month (AT.Date) = ’10’ and [AT].[Attendancetypeid] = [AttendanceLeaveInfo].[Attendancetypeid] and [AT].[EmployeeId] = [Employee].[EmployeeId] and Year (AT.Date) = Year(AttendanceLeaveInfo.Date) )
as [10月] ,
(select sum(Hours) FROM AttendanceLeaveInfo as AT
where Month (AT.Date) = ’11’ and [AT].[Attendancetypeid] = [AttendanceLeaveInfo].[Attendancetypeid] and [AT].[EmployeeId] = [Employee].[EmployeeId] and Year (AT.Date) = Year(AttendanceLeaveInfo.Date) )
as [11月] ,
(select sum(Hours) FROM AttendanceLeaveInfo as AT
where Month (AT.Date) = ’12’ and [AT].[Attendancetypeid] = [AttendanceLeaveInfo].[Attendancetypeid] and [AT].[EmployeeId] = [Employee].[EmployeeId] and Year (AT.Date) = Year(AttendanceLeaveInfo.Date) )
as [12月]
FROM AttendanceLeaveInfo join AttendanceLeave on AttendanceLeaveInfo.AttendanceLeaveId = AttendanceLeave.AttendanceLeaveId
left join AttendanceType on AttendanceLeaveInfo.Attendancetypeid = AttendanceType.Attendancetypeid
LEFT JOIN [Employee] ON AttendanceLeaveInfo.[EmployeeId]=[Employee].[EmployeeId]
where 1=1
–and Year(AttendanceLeaveInfo.Date) = ‘2017’
and AttendanceLeave.StateId=’PlanState_003′ and AttendanceLeave.ApproveResultId=’OperatorResult_001′
group by AttendanceType.Name , Employee.CnName , Employee.Code , [Employee].[EmployeeId] , AttendanceLeaveInfo.Attendancetypeid , Year(AttendanceLeaveInfo.Date)