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

2007-12-26

因為找不到類似的東西所以自已寫了一個(總覺得又在亂打造輪子…)
把計算上班日的程式放到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 = 860 –1天上班小時
set @s_Minute = 960 –早上點上班
set @s_Minute2 = 1860 –下午點下班
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 >新增查詢>貼入程式碼

成功以後會出現在