Projede Toplam Çalışma Saati

Merhabalar ,
Bir rapor hazırlamak için, mesai saatleri içinde çalışılan zamanı bulan bir fonksiyona ihtiyaç duydum. İlgili fonksiyon hafta sonlarını , öğlen aralarını ve tüm resmi tatilleri çıkarıp toplam kaç dakika çalışılmış ise onu getirecek bir özellik barındırmalı idi. Internet te aradığım kadarı ile isteklerime bu şekilde çok fazla cevap veren bir fonksiyon bulamadım.Bu nedenle kendim yazdım ve bunu sizlerle paylaşmak istedim.

Öncelikle ilgili veritabanında Holidays Tablosu oluşturdum ve alanları da
HolidayID int , (primary key)
OfficialHolidayDate datetime , -- hangi gün resmi tatil ise
IsAllDayHoliday bit , -- Tüm gün mü tatil yoksa yarım gün mü
FinishTime char (5) -- eğer yarım gün ise mesai kaçta bitiyor

Bu tabloya ilgili resmi tatilleri eklemeniz gerekmektedir.

Declare
@WorkBeginDate Datetime, -- ilgili projenin başlama zamanı
@WorkEndDate DateTime, -- ilgili projenin bitiş zamanı
@BeginDayMorning char(5), -- mesainin sabah başlama saati
@EndDayMorning Char(5), -- mesainin öğlen bitiş zamanı
@BeginDayAfternoon char(5), -- mesainin öğleden sonra başlama zamanı
@EndDayAfternoon Char(5), -- mesainin öğleden sonra bitiş zamanı
@ExcludeWeekend Bit -- hafta sonları çıkarılacak mı ?

-- ilk değerler veriliyor. Fonksiyona parametre olarak ta geçebilirsiniz. Burada örnek olması için default değerler verilmiştir.
SET @WorkBeginDate = '20080104 08:00'
SET @WorkEndDate = '20080105 14:30'

SET @BeginDayMorning = '08:00'
SET @EndDayMorning = '12:00'
SET @BeginDayAfternoon = '13:00'
SET @EndDayAfternoon = '17:30'
SET @ExcludeWeekend = 1

DECLARE
@BeginDateBeginDay Datetime, -- projenin başlama zamanında mesainin başlangıcı
@BeginDateEndDay Datetime, -- projenin başlama zamanında mesainin bitisi
@EndDateBeginDay Datetime, -- projenin bitiş gününde mesainin bitisi
@WorkDayLength INT, -- bir günde çalışılan toplam dakika
@Minutes INT -- toplamda bulacak olduğumuz süre

--- Günlük Toplam Çalışma Saati Dakika olarak öğlen arası dahil

SET @WorkDayLength = DATEDIFF(mi,'20080101 ' + @BeginDayMorning,'20080101 ' + @EndDayAfternoon) - 60
SET @BeginDateBeginDay = CONVERT(Varchar,@WorkBeginDate,101) + ' ' + @BeginDayMorning -- normal mesai başlama o gün için
SET @BeginDateEndDay = CONVERT(Varchar,@WorkBeginDate,101) + ' ' + @EndDayAfternoon -- normal mesai bitiş o gün için
SET @EndDateBeginDay = CONVERT(VARCHAR,@WorkEndDate,101)+ ' ' + @BeginDayMorning -- bu da işin bittiği son gün mesai başlangıcı

IF ( SELECT COUNT(*) FROM Holidays
WHERE ( CONVERT(Varchar,@BeginDateEndDay,101) = OfficialHolidayDate
and IsAllDayHoliday = 1 ) ) > 0
SELECT @Minutes = 0

-- EĞER normal mesai bitiş o gün için büyükse işin bitme zamanından bu durumda
-- @BeginDateEndDay enddate ' e eşit olmalı
ELSE IF @BeginDateEndDay > @WorkEndDate
Begin
-- eğer iş öğlen arası açılıp öğlen arası bitti ise
IF @WorkBeginDate between CONVERT(Varchar,@WorkBeginDate,101) + ' ' +
@EndDayMorning and CONVERT(Varchar,@WorkBeginDate,101) + ' ' +
@BeginDayAfternoon and @WorkEndDate between
CONVERT(Varchar,@WorkBeginDate,101) + ' ' + @EndDayMorning and
CONVERT(Varchar,@WorkBeginDate,101) + ' ' + @BeginDayAfternoon
SELECT @Minutes = 0
-- iş o gün sabah çalışma saatleri arasında bitmiş mi
else if @WorkBeginDate between CONVERT(Varchar,@WorkBeginDate,101) + ' ' + @BeginDayMorning and CONVERT(Varchar,@WorkBeginDate,101) + ' ' +
@EndDayMorning and @WorkEndDate between
CONVERT(Varchar,@WorkBeginDate,101) + ' ' + @BeginDayMorning and
CONVERT(Varchar,@WorkBeginDate,101) + ' ' + @EndDayMorning
SELECT @Minutes = DATEDIFF(mi,@WorkBeginDate , CONVERT(Varchar,@WorkEndDate,101) + ' ' + @EndDayMorning )

-- öğleden sonra bitmiş ise
else if @WorkBeginDate between CONVERT(Varchar,@WorkBeginDate,101) + ' ' + @EndDayMorning and
CONVERT(Varchar,@WorkBeginDate,101) + ' ' + @EndDayAfternoon
SELECT @Minutes = DATEDIFF(mi,CONVERT(Varchar,@WorkBeginDate,101) + ' ' + @EndDayMorning ,@WorkEndDate) - 60
else
SELECT @Minutes =
DATEDIFF(mi,@WorkBeginDate,@WorkEndDate) - 60

SET @BeginDateEndDay = @WorkEndDate
end
else
begin
SELECT @Minutes =
DATEDIFF(mi,@WorkBeginDate,@BeginDateEndDay) – 60
-- öğlen arası da var
end

IF @BeginDateEndDay < @WorkEndDate
BEGIN
DECLARE @Counter INT
SET @Counter = 1
IF @ExcludeWeekend = 1
BEGIN
WHILE DateAdd(dd,@Counter,@BeginDateBeginDay)
< @EndDateBeginDay
BEGIN
--- yarım gün tatil mi
IF ( SELECT COUNT(*)
FROM Holidays
WHERE
(
DateAdd(dd,@Counter,
CONVERT(Varchar,@WorkBeginDate,101) ) =
OfficialHolidayDate
and IsAllDayHoliday = 0 )
) > 0
BEGIN
SELECT @Minutes =
@Minutes +
case
when CONVERT(char(5),FinishTime)
>= @EndDayMorning
-- öğleden sonranın bitiş saatinde ise
then
(
DATEDIFF(mi,
CONVERT(Varchar,OfficialHolidayDate ,101) + ' '
+ @BeginDayMorning ,CONVERT(Varchar,OfficialHolidayDate ,101) + ' ' + FinishTime)
) - 60
ELSE
(
DATEDIFF(mi,
CONVERT(Varchar,OfficialHolidayDate ,101) + ' '
+ @BeginDayMorning ,CONVERT(Varchar,OfficialHolidayDate ,101) + ' ' + FinishTime)
)
END
FROM Holidays
WHERE DateAdd(dd,@Counter,CONVERT(Varchar,@WorkBeginDate,101) ) = OfficialHolidayDate and IsAllDayHoliday = 0
End
else
if ( SELECT COUNT(*)
FROM Holidays
WHERE ( DateAdd(dd,@Counter,CONVERT(Varchar,@WorkBeginDate,101) ) = OfficialHolidayDate
and IsAllDayHoliday = 1 ) ) = 0 and (DATEPART(dw,DateAdd(dd,@Counter,@WorkBeginDate)) IN (2,3,4,5,6))
begin
SELECT @Minutes = @Minutes + (1 * @WorkDayLength)
end
set @Counter = @Counter + 1
end
END
ELSE
BEGIN
SELECT @Minutes = @Minutes + (( DATEDIFF(dd,@BeginDateBeginDay,@EndDateBeginDay) ) * @WorkDayLength) -- öğlen arası çıkmış şekilde
--SELECT @Minutes = @Minutes + (( DATEDIFF(dd,@BeginDateBeginDay,@EndDateBeginDay) - 1 ) * @WorkDayLength)
END

-- Yapılan işin son günü
if ( SELECT COUNT(*) FROM Holidays
WHERE ( CONVERT(Varchar,@WorkEndDate,101) = OfficialHolidayDate
and IsAllDayHoliday = 1 ) ) > 0
SELECT @Minutes = @Minutes + 0
else if CONVERT(Varchar,@WorkEndDate,101) + ' ' + @BeginDayAfternoon <= @WorkEndDate
and (DATEPART(dw, @WorkEndDate ) IN (2,3,4,5,6))
SELECT @Minutes = @Minutes + DATEDIFF(mi,@EndDateBeginDay,@WorkEndDate) - 60 -- öğle arası çıkarılıyor
-- eğer öğleden sonra bitmiş ise
else if (DATEPART(dw, @WorkEndDate ) IN (2,3,4,5,6))
SELECT @Minutes = @Minutes + DATEDIFF(mi,@EndDateBeginDay,CONVERT(Varchar,@WorkEndDate,101) + ' ' + @EndDayMorning )
end

SELECT @Minutes/60/24, 'Gün' , 'Saat', @Minutes/60, 'Dakika ' ,@Minutes

ilgili dosyayı buradan indirebilirsiniz.