How Do I Find In Punch And Out Punch

Posted by Surajemo under Sql Server on 6/21/2014 | Points: 10 | Views : 764 | Status : [Member] | Replies : 3
Good Day,

I am developing a Attendance System i am stuck in a problem
In My System Every Employee has assigned a shift and Work Span how many hours he can work .
let's Say Shift is General Shift Timings are
Start Time 09:00 ,Break Out Time 13:00,Break In Time 14:00 End time is 18:00
And the Work span is 34 hours for that Employee

Now on 21-Jun-2014 he comes on

Time Flag
09:00 In
13:00 Out
14:00 In
18:00 Out
19:00 In
Now again on 22-Jun-2014 he goes home on say
Time Flag
02:00 Out
09:00 In
13:00 Out
14:00 In
18:00 Out


Now if we add work span (34 hours ) to 21-Jan-2014 09:00 it will become 22-Jun-2014 19:00
Now this employee can work next day also


The problem which i am facing is I want
21-Jun-2014 his In Time is 09:00 And Out Time is
22-Jun-2014 02:00
so whatever he as worked between 21-Jun-2014 09:00 and 22-Jun-2014 02:00
Will go in 21-Jun-2014


And his next day punch will be for
22-Jun-2014 09:00
Out punch will be
22-Jun-2014 18:00


I have problem in finding In Time and out time




Responses

Posted by: Sheonarayan on: 6/22/2014 [Administrator] HonoraryPlatinum | Points: 25

Up
0
Down
Hi Suraj...

Thanks for asking question. In this scenario, you will need to work with DateTime type variables that will automatically deduct and add time and the date differences that you are facing in your case will be taken care.

Read this article http://www.dotnetfunda.com/articles/show/1508/working-with-datetime-data-type-in-csharp on DotNetFunda.com that may help.

Thanks

Thanks

Regards,
Sheo Narayan
http://www.dotnetfunda.com

Surajemo, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Surajemo on: 6/22/2014 [Member] Starter | Points: 25

Up
0
Down
:)
I have written a stored procedure for that i am able to add time and find difference no problem i am not able to find out punch the logic part

Surajemo, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Surajemo on: 6/23/2014 [Member] Starter | Points: 25

Up
0
Down
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

Login to post response