June 19, 2009 at 12:30 am
I am using this when an employee clicks on time out (web, .net). the problem is some employee time in multiple times but following is filling out all the time out (if they click). When user only click once "the time out button" then following works good. hmm. need some idea here guys please...
Here is the business logic.
I have a web form where I am about to put 4 buttons (time in, lunch out, lunch in, time out). When an emploee good enough and click all 4 buttons in a day then system should only create 2 records. (time in and time out goes together in a same record)
1. Employee will come in the morning and click on Time In button.
>>> Good, Simple insert into table
2. Employee will click on Lunch Out button.
>>> need to make sure my UPDATE (not insert) is updating the moring record when employee timed in.
3. Employee will clock in upon arriving from Lunch "Lunch In".
>>> Another insert statement however I need to make sure the prior record has lunch out punched in and also calculate hours and plug it in the first record. If "lunch out" is missing then flag the record (update status column "AUDIT") and display a message (response.write "something")
4. time out. Very critical. an employee may be going home early so he/she is not taking lunch. so "time out" then should check whether employee has a record with time in only and no time out and then update the record with the new time out value. another scenerio, employee took lunch but forgot to lunch out, and came back and just did lunch in. in that case you will have 2 records both with time in value populated and time out value empty. in that case the lunch in record's timeout column will be update.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
alter PROCEDURE [dbo].[sp_timeOut]
-- Add the parameters for the stored procedure here
@emp_id int,
@clocking_date datetime,
@time_out datetime,
@record_status varchar(15),
@rec_operator varchar(30)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
IF EXISTS(
select * from clocking_details
where emp_id=@emp_id and clocking_date = @clocking_date and time_out is null
)
IF(select count(1) from clocking_details
where emp_id=@emp_id and
clocking_date = @clocking_date and time_out is null) =1
update clocking_details
set time_out=@time_out
where (emp_id=@emp_id and clocking_date = @clocking_date and time_out is null)
ELSE
IF(select count(1) from clocking_details
where emp_id=@emp_id and
clocking_date = @clocking_date and time_out is null) >1
update clocking_details
set time_out=@time_out
where DETAIL_NUMBER = (SELECT MAX(DETAIL_NUMBER) FROM CLOCKING_DETAILS
where emp_id=@emp_id and clocking_date = @clocking_date and time_out is null)
RETURN
end
June 19, 2009 at 11:47 am
I created 2 buttons (punch in and punch out) and then everything else was easy and less complex. Pls mark this post as Resolved.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply