-2

I have a table to record staffs enter and exit times. My Table:

CREATE TABLE [dbo].[tbl_attendanceSheet] (
    [Id]           INT            IDENTITY (1, 1) NOT NULL,
    [memberCode]   NVARCHAR (20)  NULL,
    [name]         NVARCHAR (20)  NULL,
    [date]         date  NULL,
    [clockin]      time  NULL,
    [clockout]     time  NULL,
    [delay]        time  NULL,
    [HouresWorked] time  NULL,
    [desc]         NVARCHAR (150) NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

I want to implement this logic:

IF ClockIn exists and ClockOut exists, Insert the value into ClockIn(new row).
IF ClockIn doesnt exist and ClockOut doesnt exist, Insert the value into ClockIn.
IF ClockIn exists and ClockOut doesnt exist, Insert the value into ClockOut.

I can implement inserting clock in and clock out without checking each other, my problem is how to check if one exists and if so, the other one should be updated if necessary.

What I tried myself:

CREATE PROCEDURE InputClock
@QmemberCode nvarchar(20),
@InputName nvarchar(20),
@InputDate Date,
@InputTime time
as

IF NOT EXISTS (SELECT TOP 1 clockin FROM tbl_attendanceSheet WHERE memberCode=@QmemberCode ORDER BY ClockIn DESC) AND NOT EXISTS (SELECT TOP 1 clockout FROM tbl_attendanceSheet WHERE memberCode=@QmemberCode ORDER BY ClockOut DESC)
BEGIN
   Insert Into tbl_attendanceSheet(memberCode,name,date,clockIn) Values (@QmemberCode,@InputName,@InputDate,@InputTime)
END
ELSE IF EXISTS (SELECT TOP 1 clockin FROM tbl_attendanceSheet WHERE memberCode=@QmemberCode ORDER BY ClockIn DESC) AND NOT EXISTS (SELECT TOP 1 clockout FROM tbl_attendanceSheet WHERE memberCode=@QmemberCode ORDER BY ClockOut DESC)
BEGIN
    Insert Into tbl_attendanceSheet(memberCode,name,date,clockout) Values (@QmemberCode,@InputName,@InputDate,@InputTime)
END

It doesnt work because, when clockIn column has a value and clockOut column doesnt it does not enter the time value in the clockOut column.

Why do I need this logic? Because there are several employees who would clock in, in the morning in any order and would not clock out at night in the same order. So the system should figure it out who has clockedIn before, to Clock it out and who has not done anything before to clock it in.

what I want presented in another way: lets assume this is my table:

[memberCode] | [name] | [ClockIn]| [ClockOut]

In the morning Alex comes and clocks in

[memberCode] | [name] | [ClockIn]| [ClockOut]
    121          Alex     09:00:00

Jaimy comes and clocks in

[memberCode] | [name] | [ClockIn]| [ClockOut]
    121          Alex     09:00:00  
    122          jamy     09:00:20

Neo comes and clocks in

[memberCode] | [name] | [ClockIn]| [ClockOut]
    121          alex     09:00:00  
    122          jamy     09:00:20
    123          Neo      09:00:25

Neo Leaves:

[memberCode] | [name] | [ClockIn]| [ClockOut]
    121          alex     09:00:00  
    122          jamy     09:00:20
    123          Neo      09:00:25   13:00:00

Neo Comes

[memberCode] | [name] | [ClockIn]| [ClockOut]
    121          Alex     09:00:00  
    122          Jamy     09:00:20
    123          Neo      09:00:25   13:00:00
    123          Neo      18:00:00

At night, Jamie leaves:

[memberCode] | [name] | [ClockIn]| [ClockOut]
    121          Alex     09:00:00  
    122          Jamy     09:00:20   22:00:00
    123          Neo      09:00:25   13:00:00
    123          Neo      18:00:00

Neo Leaves:

[memberCode] | [name] | [ClockIn]| [ClockOut]
    121          Alex     09:00:00  
    122          Jamy     09:00:20   22:00:00
    123          Neo      09:00:25   13:00:00
    123          Neo      18:00:00   23:00:00

Alex Leaves:

[memberCode] | [name] | [ClockIn]| [ClockOut]
    121          Alex     09:00:00   00:00:00
    122          Jamy     09:00:20   22:00:00
    123          Neo      09:00:25   13:00:00
    123          Neo      18:00:00   23:00:00

Ofcource I need a date column as well but I omited it due to simplicity. This is my idea for an attendance sheet. If you want to add something to make it better, I would appreciate it.

Daniel_Ranjbar
  • 161
  • 11
  • What should happen if both ClockIn and ClockOut are already set? – Klaus Gütter Dec 16 '18 at 06:35
  • @KlausGütter it should start a new row with that name, and record the time in clockIn column. – Daniel_Ranjbar Dec 16 '18 at 06:36
  • 1
    Can you please tell us what you already tried and where you got stuck? – Klaus Gütter Dec 16 '18 at 06:39
  • @KlausGütter because I dont know the command on how to check previous records, Im continuing to think... – Daniel_Ranjbar Dec 16 '18 at 06:41
  • 2
    I would do the entire thing in SQL, which is pretty easy - the logic is either update an existing record for that employee, or create a new one. You should, however, take into consideration what happens when an employee clocks in, forget to clock out, and then clock in again the next day. If you have overnight shifts this can get harder. Still, this can be solved using a single stored procedure. – Zohar Peled Dec 16 '18 at 06:43
  • @ZoharPeled would you please post what you mean as an answer? I dont want know how exactly updating will include the checking I mention. But i think youre taking me somewhere close to my answer. – Daniel_Ranjbar Dec 16 '18 at 06:46
  • 1
    `SELECT TOP 1 * FROM Table WHERE EmployeeID=@id ORDER BY ClockIn DESC` would return the most recent record (or none if this is the first clock in ever of this employee). – Klaus Gütter Dec 16 '18 at 06:47
  • @KlausGütter this is a good idea too. Would you please include the "entring the data in database" part in your code too? I want to see the whole code. – Daniel_Ranjbar Dec 16 '18 at 06:52
  • This is just a simple UPDATE or INSERT – Klaus Gütter Dec 16 '18 at 06:55
  • @KlausGütter i want to know how to implement the condition part. You just selected the last record for that employee. You didnt write any thing for if he has clockin and clock out and... I want to see your whole code to figure that out. – Daniel_Ranjbar Dec 16 '18 at 06:59
  • If you could [edit] your question to include sample data as [DDL](https://en.wikipedia.org/wiki/Data_definition_language) + [DML](https://en.wikipedia.org/wiki/Data_manipulation_language, your current attempt and your desired results. I would be more than happy to write a complete answer. Please also add the tag for the SQL Server version you are using (2012, 2014...2017). – Zohar Peled Dec 16 '18 at 07:01
  • @ZoharPeled okay i will edit my question right now but im with my phone (and simcard internet) and not pc because my adsl-internet is cut so I have to write the sample datas. – Daniel_Ranjbar Dec 16 '18 at 07:07
  • @ZoharPeled it Is completely edited as you mentioned. thank you for your time – Daniel_Ranjbar Dec 16 '18 at 07:47
  • Sorry, by the time I've got back the question was closed. Voted to reopen, but it needs 4 more votes. If it gets reopened, feel free to ping me so that I could answer it. – Zohar Peled Dec 16 '18 at 09:37
  • This post already have 3 reopen votes, it only needs two more. If you can [edit] it to be more clear that your problem is actually quite narrow (meaning, only the clock out part), I'm pretty sure it will get re-open quickly enough. As a personal rule, I'm happy to post answers here but wont send them via personal communication. Sorry. – Zohar Peled Dec 17 '18 at 06:15
  • @ZoharPeled I didnt notice that. your right Im sorry. Okay I will edit it to be more clear now. – Daniel_Ranjbar Dec 17 '18 at 06:21
  • @ZoharPeled the Question is open again hurray :) would you please help me out on this? – Daniel_Ranjbar Dec 18 '18 at 05:42

1 Answers1

1

The solution I suggest is to first try to update, and then, if the update effected no records, insert a new record.
I'm going on the assumption that the data sent to the stored procedure is only relevant to the date that is being sent using the @InputDate parameter.

Here is the procedure I suggest:

CREATE PROCEDURE InputClock
    @QmemberCode nvarchar(20),
    @InputName nvarchar(20),
    @InputDate Date,
    @InputTime time
AS

UPDATE [dbo].[tbl_attendanceSheet]
SET [clockout] = @InputTime
WHERE memberCode = @QmemberCode
AND [date] = @InputDate
AND [clockin] IS NOT NULL
AND [clockout] IS NULL

IF @@ROWCOUNT = 0 
BEGIN

    INSERT INTO tbl_attendanceSheet(memberCode,name,date,clockIn)
    VALUES (@QmemberCode,  @InputName, @InputDate, @InputTime)

END

For more information, read Aaron Bertrand's answer to Using a if condition in an insert SQL Server

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121