Derive Attendance Data from CLOCK IN and OUT

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Derive Attendance Data from CLOCK IN and OUT

Author

Topic

vlijo
Starting Member

2 Posts

Posted - 2014-01-28 : 01:08:16

I am trying to develop an attendance application which calculates the shift details as per the Clock IN OUT Data , the following are the table details

CREATE TABLE [dbo].[INOUTData](
	EmpID VARCHAR(10),
	CLockDate Date  NULL,
	[INTIME]  Time NULL,
	[OUTTIME]  Time NULL,
) ON [PRIMARY]


GO
 
INSERT INOUTData (   EmpID,   CLockDate,   [INTIME], [OUTTIME]  ) VALUES (  'E1',   '28-Jan-2014', '07:50:00',  '10:15:00' )
INSERT INOUTData (   EmpID,   CLockDate,   [INTIME], [OUTTIME]  ) VALUES (  'E1',   '28-Jan-2014', '10:25:00',  '12:15:00' )
INSERT INOUTData (   EmpID,   CLockDate,   [INTIME], [OUTTIME]  ) VALUES (  'E1',   '28-Jan-2014', '17:15:00',  '20:55:00' )

INSERT INOUTData (   EmpID,   CLockDate,   [INTIME], [OUTTIME]  ) VALUES (  'E2',   '28-Jan-2014', '08:30:00',  NULL )  -- First Session OUT punch missed
INSERT INOUTData (   EmpID,   CLockDate,   [INTIME], [OUTTIME]  ) VALUES (  'E2',   '28-Jan-2014', '16:15:00',  '21:55:00' )

INSERT INOUTData (   EmpID,   CLockDate,   [INTIME], [OUTTIME]  ) VALUES (  'E3',   '28-Jan-2014', NULL,  '11:34:00' )  -- First Session IN punch missed
INSERT INOUTData (   EmpID,   CLockDate,   [INTIME], [OUTTIME]  ) VALUES (  'E3',   '28-Jan-2014', '16:15:00',  '19:55:00' )
INSERT INOUTData (   EmpID,   CLockDate,   [INTIME], [OUTTIME]  ) VALUES (  'E3',   '28-Jan-2014', '20:05:00',  '21:55:00' )

INSERT INOUTData (   EmpID,   CLockDate,   [INTIME], [OUTTIME]  ) VALUES (  'E4',   '28-Jan-2014', '08:30:00',  '11:34:00' ) 
INSERT INOUTData (   EmpID,   CLockDate,   [INTIME], [OUTTIME]  ) VALUES (  'E4',   '28-Jan-2014', NULL,  '21:55:00' ) -- Second Session IN punch missed

INSERT INOUTData (   EmpID,   CLockDate,   [INTIME], [OUTTIME]  ) VALUES (  'E5',   '28-Jan-2014', '10:35:00',  '13:44:00' ) 
INSERT INOUTData (   EmpID,   CLockDate,   [INTIME], [OUTTIME]  ) VALUES (  'E5',   '28-Jan-2014',  '18:55:00' , NULL ) -- Second Session OUT punch missed


Create Table ShiftDetails
(
	ShiftId INT ,
	ShiftName VARCHAR(20),
	Session1StartTime  TIme ,
	Session1EndTime  TIme ,
	Session2StartTime  TIme ,
	Session2EndTime TIme 
)

Insert into ShiftDetails values (1,'Break Shift', '08:00:00', '12:00:00', '17:00:00', '21:00:00')
Insert into ShiftDetails values (2,'Break Shift2', '10:00:00', '14:00:00', '19:00:00', '23:00:00')

 


-- I am trying to get the data as follows combining these two tables

EMpID   ClockDate		Session1StartTime	Session1EndTime		ActualSession1StartTime		ActualSession1EndTime	Session2StartTime	Session2EndTime		ActualSession2StartTime		ActualSession2EndTime 
E1      28-Jan-2014		08:00:00			12:00:00			07:50:00		12:15:00		17:00:00	21:00:00			17:15:00         20:55:00										
E2      28-Jan-2014		08:00:00			12:00:00			08:30:00		NULL			17:00:00	21:00:00			16:15:00					21:55:00											
E3      28-Jan-2014		08:00:00			12:00:00			NULL		        11:34:00		17:00:00	21:00:00			16:15:00 	       21:55:00												
E4      28-Jan-2014		08:00:00			12:00:00			08:30:00	         11:34:00		17:00:00	21:00:00			NULL	       21:55:00												
E5      28-Jan-2014		10:00:00			14:00:00			10:35:00		14:44:00		19:00:00	23:00:00			18:55:00	       NULL

kindly give suggestions , right now i am using a stored procedure which traverses through each record and does the job.

Is there any way to do it with Pivot, or queries instead of cursors

-- Lijo

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-01-28 : 06:17:32

How do you intent to identify the INOUTData record is for which ShiftDetails ?

KH
[spoiler]Time is always against us[/spoiler]

vlijo
Starting Member

2 Posts

Posted - 2014-01-28 : 06:49:32

In my procedure the Shift details are automatically determined by the First IN or the First OUT encountered for the day, the nearest Shift is taken into consideration

for eg, 'E5', '28-Jan-2014', '10:35:00' the nearest shift is Break Shift2
'E4', '28-Jan-2014', '08:30:00', the nearest shift is Break Shift
'E3', '28-Jan-2014', NULL, '11:34:00' , there is no IN punch so it will determine the Shift as per the OUT Punch

quote:
Originally posted by khtan

How do you intent to identify the INOUTData record is for which ShiftDetails ?

KH
[spoiler]Time is always against us[/spoiler]

-- Lijo

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources