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 >新增查詢>貼入程式碼

成功以後會出現在

沒有留言: