I am farely new to SP's and below is a time and attendance system. If someone clocks in then clocking time is set to getdate() and status is 1 to indicate he was clocked in. When they punch out again clocking out time is set to getdate() and status =2 which indicates he is clocked out.What I want this SP to do is to check if clockin time is later than 08h00. if they clock in at 07h45 it should default to 08h00 but if he comes in at 08h30 is should use that specific value. The same principle for clocking out if they clock out 17h20 it should default to 17h00 but if the clock out at 16h10 it should use this value. Thx for your time.ALTER PROCEDURE [dbo].[spClocking]( @staffNo varchar(50))ASBEGIN Declare @Status varchar(50), @ClockIn datetime, @ClockOut datetime, @Minutes decimal, @ClockID int SET NOCOUNT ON; select @ClockID = (Select top 1 ClockingID from Clocking where StaffID = @StaffNo order by ClockingID desc) Select @Status = (Select Status from Clocking where ClockingID = @ClockID) -- Insert statements for procedure here IF @Status = 1 BEGIN select @ClockIn = (Select ClockTime_In from Clocking where ClockingID = @ClockID) select @ClockOut = getDate() select @Minutes = datediff(minute, @ClockIn, @ClockOut) update Clocking set Status = 2, ClockTime_Out = @ClockOut, Minutes = @Minutes where ClockingID = @ClockID END ELSE BEGIN select @ClockIn = getDate() select @Status = 1 insert into Clocking (StaffID, Status, ClockTime_In) values (@staffNo, @Status, @ClockIn) ENDEND
Manipulating SP for Certain times(Dates)
Posted - 2010-10-29 : 09:05:32
