12月 26, 2007

計算上班時間(扣除六、日,中午休息時間)in SQLServer2005

因為找不到類似的東西所以自已寫了一個(總覺得又在亂打造輪子...)
把計算上班日的程式放到SQL處理,好處是效能好,可以直接用SQL做SUM、AVG等運算
想扣國定假日的話,可以把日期存在table再去比對,這部份我就沒寫了,有大大寫好的話
請分享給我,謝謝,哈哈

函數名稱
GetWorkMinute(開始時間,結束時間)

傳回值
扣除星期六、日,午休,上班八小時以外的分鐘數


使用方式:


範例一

SELECT dbo.GetWorkMinute('2007-12-19 17:17','2007-12-20 11:59') as worktime



傳回:222

*呼叫自訂函數時要打全名不可省略dbo





程式碼:





 

--登入 DB
USE DB名稱;
GO
--如果有叫做dbo.GetWorkMinute的函數就移除它
IF OBJECT_ID (N'dbo.GetWorkMinute', N'FN') IS NOT NULL
DROP FUNCTION dbo.GetWorkMinute;
GO
CREATE FUNCTION GetWorkMinute
(
@startDate DATETIME ,
@endDate DATETIME
)
RETURNS int
AS
--程式開始的區塊,類似C、php的大括號
Begin
--定義變數的保留字declare
declare @DayMinute int,@s_Minute int,@s_Minute2 int,@i int,@x DATETIME,@resultMin int
--值帶給變數的時候前面要加set
set @DayMinute = 8*60 --1天上班小時
set @s_Minute = 9*60 --早上點上班
set @s_Minute2 = 18*60 --下午點下班
declare @weekdays int,@totalDay int,@bgMin int,@endMin int
set @i = 0
if @startDate is not null and @endDate is not null
set @weekdays = 0
--計算申請日期和建置完成日期差幾天
set @totalDay=DateDiff(day,@startDate,@endDate)
--開始時間換算為分鐘
set @bgMin=(DatePart(hour,@startDate)*60+DatePart(minute,@startDate))
--建置完成時間換算成分鐘
set @endMin=(DatePart(hour,@endDate)*60+DatePart(minute,@endDate))
--取申請日期和建置日期中有幾個假日
while @i<=DateDiff(day,@startDate,@endDate)
begin
set @x=DateAdd(day,@i,@startDate)
if DatePart(weekday,@x)=1 or DatePart(weekday,@x)=7
set @weekdays=@weekdays+1
set @i=@i+1
end
--計算總花費時間
--若申請時間、建置時間相隔超過一天
-- (公司下班時間- 申請時間) + (建置完成時間- 公司上班時間) + 中間相隔天數*8 *60 -日期區間中的六、日
--否則
-- 建置時間- 開始時間
if @totalDay>=1
--跨天
begin
set @resultMin= abs(@s_Minute2-@bgMin)+abs(@endMin-@s_Minute)+((@totalDay-1)*@DayMinute)-(@weekdays*@DayMinute)
end
else
--同一天
begin
set @resultMin=abs(@endMin-@bgMin)
end
--判斷有沒有經過中午,有的話扣掉中午休息時間
if @totalDay>=1
--跨天
begin
if DatePart(hour,@startDate) <12
set @resultMin=@resultMin-60
if DatePart(hour,@endDate) >12
set @resultMin=@resultMin-60
end
else
begin
--同一天
if DatePart(hour,@startDate) <12 and DatePart(hour,@endDate) >12
set @resultMin=@resultMin-60
end
return @resultMin
END




如何在SQL server中設定自訂函數






打開SQL server Management >新增查詢>貼入程式碼



成功以後會出現在



1 則留言:

Akito Sun 提到...
作者已經移除這則留言。