1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83
| CASE WHEN DATEDIFF(hh,@vStartDate ,@vEndDate)<= 24 AND DATEPART(DAY,@vStartDate) != DATEPART(DAY,@vEndDate) THEN
DATEDIFF(hh,@vStartDate ,@vEndDate)- 15.5
ELSE
(DATEDIFF(d,@vStartDate,@vEndDate) --Get the number of days between start and end dates
- DATEDIFF(wk,@vStartDate,@vEndDate) * 2 -- for each week, subtract 2 days (by default a week occurs between sat and sunday on sql server)
- CASE
--WHEN DATENAME(dw, @vStartDate) <> 'Saturday' AND DATENAME(dw, @vEndDate) = 'Saturday'
-- THEN 1 --subtract 1 day if the end date falls on a saturday and the startdate is a weekday, or sunday
WHEN DATENAME(dw, @vStartDate) = 'Sunday' AND DATENAME(dw, @vEndDate) = 'Sunday'
THEN 1
WHEN DATENAME(dw, @vStartDate) = 'Saturday' AND DATENAME(dw, @vEndDate) = 'Saturday'
THEN 1
WHEN DATENAME(dw, @vStartDate) = 'Saturday' AND DATENAME(dw, @vEndDate) = 'Sunday'
THEN 0
WHEN DATENAME(dw, @vStartDate) = 'Sunday' AND DATENAME(dw, @vEndDate) = 'Saturday'
THEN 2
WHEN DATENAME(dw, @vStartDate) = 'Saturday'
THEN 0
WHEN DATENAME(dw, @vEndDate) = 'Sunday'
THEN -1
WHEN DATENAME(dw, @vStartDate) = 'Sunday'
THEN 1
WHEN DATENAME(dw, @vEndDate) = 'Saturday'
THEN 1
ELSE 0
END
- (SELECT COUNT(DATEPART(weekday,datum))
FROM W_FAC_VAKANTIEDAGEN
WHERE DATUM BETWEEN @vStartDate AND @vEndDate
)
)*8.5
- CASE
WHEN DATENAME(dw, @vStartDate) <> 'Sunday' AND DATENAME(dw, @vStartDate) <> 'Saturday'
THEN (DATEPART(hh,@vStartDate) - 8.5)
ELSE 0
END
- CASE
WHEN DATENAME(dw, @vEndDate) = 'Saturday'
THEN -8.5 --ads the 8.5h from friday
WHEN DATENAME(dw, @vEndDate) = 'Sunday'
THEN 0
ELSE
-(DATEPART(hh,@vEndDate)-8.5) --if weekday, calculates the hours worked during the last day
END
- CASE
WHEN DATENAME(dw, @vStartDate) = 'Saturday' OR DATENAME(dw, @vStartDate) = 'Sunday'
THEN 0
WHEN DATEPART(hh,@vStartDate) <= 8 AND DATEPART(n,@vStartDate) <=30
THEN (8.5 - DATEPART(hh,@vStartDate)) --if start time <8h30 then add the difference (ex: start: 6h then it adds 2h30)
ELSE 0
END
- CASE
WHEN DATENAME(dw, @vEndDate) = 'Saturday' OR DATENAME(dw, @vEndDate) = 'Sunday'
THEN 0
WHEN DATEPART(hh,@vEndDate) > 17
THEN (DATEPART(hh,@vEndDate) - 17)
ELSE 0
END
- CASE
WHEN EXISTS (SELECT *
FROM W_FAC_VAKANTIEDAGEN
WHERE YEAR(DATUM) = YEAR(@vStartDate) AND MONTH(DATUM) = MONTH(@vStartDate) AND DAY(DATUM) = DAY(@vStartDate)
AND DATEPART(weekday, @vStartDate) >1 AND DATEPART(weekday, @vStartDate) <7
)
THEN
CASE
WHEN YEAR(@vEndDate) = YEAR(@vStartDate) AND MONTH(@vEndDate) = MONTH(@vStartDate) AND DAY(@vEndDate) = DAY(@vStartDate)
THEN (DATEPART(hh,@vEndDate) - DATEPART(hh,@vStartDate))-1
ELSE
CASE
WHEN DATEPART(hh,@vStartDate) < 17
THEN (17 - DATEPART(hh,@vStartDate))
ELSE 0
END
END
ELSE 0
END
END |
Partager