asked on

Update SQL Query

I have a table which stored the Attendance Logs.

When an employee applies for a leave, i wish to update the table ATTENDANCE with a ATTENDANCE_STATUS_ID =4.

The input provided to the Stored Procedure are:

EMP_ID,
START_DATE_OF_LEAVE,
END_DATE_OF_LEAVE
ATTENDANCE_STATUS_ID

What i wish to achieve is that i need to insert the records to the table ATTENDANCE if no entry is made for the employee on that particular date( any date that falls between the START_DATE_LEAVE AND END_DATE_OF_:LEAVE).

and Update if an entry already exist.

ATTENDANCE
===========================
ID int,
EMP_ID int,
ATTENDANCE_STATUS_ID int
DATE_OF_ATTENDANCE datetime,
CREATE_DATE datetime

Reza Rad

declare @isexists int

select @isexists=count(*) from attendance where ID=@ID
and date_of_attendance between @start_date_leave and @end_date_leave

if(@isexists=0)
begin
' insert statement
end
else
begin
'update statement
end

ong-hh

SELECT * FROM ATTENDANCE WHERE
DATEDIFF(d, START_DATE_LEAVE, DATE_OF_ATTENDANCE) >= 0 and
DATEDIFF(d, END_DATE_OF_LEAVE, DATE_OF_ATTENDANCE) <= 0

IF @@ROWCOUNT > 0
PRINT 'Warning: Update if an entry already exist';
GO

TECH_NET

ASKER

Can you provide me how the insert script would be. Should i be looping through the date range
ie if the leave date of the employee is between Jan 1 thru Jan 10, how do i post the data for all the dates ithat fall between these leave date.

Reza Rad

I can 't understand what you mean exactly? explain more,
if you paste sample data in your table, that will be helpful too

TECH_NET

ASKER

To furthe illustrate, let me explain:

Employee Sam is requesting for leave from Jan 1 thru Jan 10,

I wish to insert records to the table ATTENDANCE with the attendance_status_id column value of 4.
for all dates in the range jan 1 thr Jan 10

something like this...
INSERT INTO ATTENDANCE
(
ATTENDENCE_STATUS_ID,
EMP_ID,
CREATE_DATE
)
SELECT
@ATTENDANCE_STATUS_ID
FROM
ATTENDANCE
WHERE
date_of_attendance between @start_date_leave and @end_date_leave

Reza Rad

so your insert statement seems correct, just try it(i changed it a little):
INSERT INTO ATTENDANCE
(
ATTENDENCE_STATUS_ID,
EMP_ID,
CREATE_DATE
)
SELECT
@ATTENDANCE_STATUS_ID,
EMP_ID,
CREATE_DATE
FROM
ATTENDANCE
WHERE
date_of_attendance between @start_date_leave and @end_date_leave

ASKER CERTIFIED SOLUTION

Sara bhai

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

TECH_NET

ASKER

I am getting the following error

Incorrect syntax near 'END'.

The Last END in the loop

TECH_NET

ASKER

Should the IF Loop have an END.

Reza Rad

why you want to loop and insert then
just try insert statement in my last post, that will insert multiple records!

Sara bhai

Ok try this, ingore that END and put () with inserted values.

CREATE PROC sp_InsertEmpLeaveStatus
@EMP_ID INT,
@START_DATE_OF_LEAVE DATETIME,
@END_DATE_OF_LEAVE DATETIME,
@ATTENDANCE_STATUS_ID INT
AS
      DECLARE @loopCounter INT
      DECLARE @SeqCounter INT
      SET @SeqCounter = 0
      SET @loopCounter = DATEDIFF(d, @START_DATE_OF_LEAVE,@END_DATE_OF_LEAVE)
      WHILE @loopCounter >= 0
            BEGIN
                  IF EXISTS (SELECT * FROM ATTENDANCE WHERE DATE_OF_ATTENDANCE = @START_DATE_OF_LEAVE + @SeqCounter AND EMP_ID = @EMP_ID)
                        UPDATE ATTENDANCE
                        SET ATTENDANCE_STATUS_ID = @ATTENDANCE_STATUS_ID
                        WHERE DATE_OF_ATTENDANCE = @START_DATE_OF_LEAVE + @SeqCounter AND EMP_ID = @EMP_ID
                  ELSE
                        INSERT INTO ATTENDANCE (EMP_ID, DATE_OF_ATTENDANCE, ATTENDANCE_STATUS_ID )
                        VALUES      ( @EMP_ID , @START_DATE_OF_LEAVE + @SeqCounter ,@ATTENDANCE_STATUS_ID)

                  SET @loopCounter = @loopCounter - 1
                  SET @SeqCounter = @SeqCounter + 1
            END
GO

Sara bhai

Loop statement is required for entered each date attedance in table.
Table have single date attendance records.