Its a Engine that will run everyday and it will calculate the working hours for that day so the procedure start'like this
DECLARE @PreviousDate DATE=Dateadd(day, -1, @Date);
DECLARE @PrvWorkSpanTime AS TIME(7);
DECLARE @tblDates AS TABLE
(
pkid INT IDENTITY(1, 1),
workingdate DATE
)
INSERT INTO @tblDates
SELECT @PreviousDate
UNION ALL
SELECT @Date;
--loop over the dates and do the calculation for current day and previous day also because wee need to --handle night shift also
WHILE @WorkingDateCount >= @Counter
BEGIN
--get the one by one date
SET @Date =(SELECT workingdate
FROM @tblDates AS io
WHERE io.pkid = @Counter)
--get the shift according to shift schedule
SET @Shift_Via_ShiftSchedule =(SELECT TOP 1 fk_shiftid
FROM dbo.tblemployeeshiftdates
WHERE sa_date = @Date
AND fk_employeeid = @EmpId)
--default out time this will to get the out time
DECLARE @DefaultOutTime AS TIME(7)
--GET WORK SPAN HOURS FOR THAT EMPLOYEE
SET @WorkSpan=dbo.Fn_getworkspanhours(@EmpId);
DECLARE @prDATE AS DATE=Dateadd(day, -1, @Date);
--getting the previous day out time
EXEC @return_value = [dbo].[Get_previousdayouttime]
@Date = @prDATE,
@EmpID = @EmpID,
@FinalOutTime = @FinalOutTime output
--GET THE FIRST IN PUNCH
IF @Counter = 1
OR @Counter = 2
BEGIN
IF @FinalOutTime IS NOT NULL
BEGIN
SET @Intime= (SELECT Min(Cast(apdate AS DATETIME)
+ Cast(aptime AS DATETIME))
FROM [txnAcessPunches].[dbo].[atransaddpunch]
WHERE fkemapemid = @EmpId
AND apdate = @Date
AND ap_flag = 'In'
AND ( Cast(apdate AS DATETIME)
+ Cast(aptime AS DATETIME) ) > CONVERT(DATETIME, @FinalOutTime)); --AND CONVERT(DATETIME, @Date))
END
ELSE
BEGIN
SET @Intime= (SELECT Min(Cast(apdate AS DATETIME)
+ Cast(aptime AS DATETIME))
FROM [txnAcessPunches].[dbo].[atransaddpunch]
WHERE fkemapemid = @EmpId
AND ap_flag = 'In'
AND apdate = @Date)
END
END
EXEC @return_value = [dbo].[Usp_getshift]
@EmpId = @EmpId,
@InTime = @Intime,
@Shift_Via_ShiftSchedule = @Shift_Via_ShiftSchedule,
@ShiftID = @ShiftID output,
@Actual_ShiftInTime = @Actual_ShiftInTime output,
@Actual_ShiftOutTime = @Actual_ShiftOutTime output,
@Actual_BreakInTime = @Actual_BreakInTime output,
@Actual_BreakOutTime = @Actual_BreakOutTime output,
@Actual_BreakDeduct = @Actual_BreakDeduct output,
@Actual_ShiftHalfDayHours = @Actual_ShiftHalfDayHours output,
@Actual_ShiftFullDayHours = @Actual_ShiftFullDayHours output,
@Date =@Date;
--APPWND DATE AND TIME TO ACTUAL SHIFT IN AND OUT TIME
SET @Actual_ShiftInTime= Cast(@Date AS DATETIME)
+ Cast(@Actual_ShiftInTime AS DATETIME);
SET @Actual_ShiftOutTime= Cast(@Date AS DATETIME)
+ Cast(@Actual_ShiftOutTime AS DATETIME);
--concatenate date and time
SET @ShiftDateTime_In = Cast(@Date AS DATETIME)
+ Cast(@Actual_ShiftInTime AS DATETIME)
IF @GlobalIntime IS NOT NULL
BEGIN
--IF GLOBAL TIME IS BETWEEN SHIFT START TIME AND 12 AM THEN CONSIDER TODAYS DATE AS FINAL OUT TIME ELSE ADD ONE DAY
IF CONVERT(TIME, @GlobalIntime) BETWEEN CONVERT(TIME, @Actual_ShiftInTime) AND CONVERT(TIME, '23:59')
BEGIN
SET @FinalOutTime= Cast(@Date AS DATETIME)
+ Cast(@GlobalIntime AS DATETIME);
END
ELSE
BEGIN
SET @FinalOutTime= Cast(Dateadd(day, 1, @Date) AS DATETIME)
+ Cast(@GlobalIntime AS DATETIME);
END
END
ELSE
BEGIN
IF @WorkSpan IS NOT NULL
BEGIN
DECLARE @Hours AS INT;
DECLARE @Minutes AS INT;
BEGIN try
--get the hours and minutes
SET @Hours= Substring (@WorkSpan, 0, 3);
SET @Minutes= Substring (@WorkSpan, 4, 5);
END try
BEGIN catch
SET @Hours=00;
SET @Minutes=00;
END catch;
SET @FinalOutTime=Dateadd(mi, Datepart(mi, @WorkSpan), Dateadd(hh, Datepart(hh, @WorkSpan), @Actual_ShiftInTime));
END
ELSE
BEGIN
--or add default value
SET @FinalOutTime=Dateadd(mi, Datepart(mi, 0), Dateadd(hh, Datepart(hh, 17), @Actual_ShiftInTime));
END
END
Surajemo, if this helps please login to Mark As Answer. | Alert Moderator