January 27, 2014 at 11:27 pm
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 ClockDateSession1StartTimeSession1EndTimeActualSession1StartTimeActualSession1EndTimeSession2StartTimeSession2EndTimeActualSession2StartTimeActualSession2EndTime
E1 28-Jan-201408:00:0012:00:0007:50:0012:15:0017:00:0021:00:0017:15:00 20:55:00
E2 28-Jan-201408:00:0012:00:0008:30:00NULL17:00:0021:00:0016:15:0021:55:00
E3 28-Jan-201408:00:0012:00:00NULL 11:34:0017:00:0021:00:0016:15:00 21:55:00
E4 28-Jan-201408:00:0012:00:0008:30:00 11:34:0017:00:0021:00:00NULL 21:55:00
E5 28-Jan-201410:00:0014:00:0010:35:0014:44:0019:00:0023:00:0018:55:00 NULL
kindly give suggestions on the best way to do it ,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
[font="Courier New"]-- Alban Lijo <SQL Rookie> :-)[/font]
January 28, 2014 at 1:31 am
I don't want to do spam, but there is something misty in your table relations for me.
You said you found a solution with cursor approach, but i can not find any relation between "ShiftDetails" and "INOUTData" tables.
Assume you have only this datas in your database:
INSERT INOUTData ( EmpID, CLockDate, [INTIME], [OUTTIME] ) VALUES ( 'E1', '28-Jan-2014', '17:15:00', '20:55:00' )
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')
In your cursor approach how will you detect data in "INOUTData" are for 'Break Shift2' while you dont have 'Break Shift2' id as a foreign key in your "INOUTData" table?
I suggest do a little change in your "INOUTData" table structure and save "ShiftDetails" records ids in "INOUTData", then things would be more easier.
___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.
January 28, 2014 at 1:41 am
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
[font="Courier New"]-- Alban Lijo <SQL Rookie> :-)[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply