Update SQL Query

TECH_NET
TECH_NET used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Reza RadConsultant, Trainer

Commented:
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

Commented:
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

Author

Commented:
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.

Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Reza RadConsultant, Trainer

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

Author

Commented:
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 RadConsultant, Trainer

Commented:
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
Commented:
Hello, I am assuming that ur ID and createdate fields are defaults.
Please try the following sp at testing environment

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
                  END
                  SET @loopCounter = @loopCounter - 1
                  SET @SeqCounter = @SeqCounter + 1
            END
GO


Author

Commented:
I am getting the following error

Incorrect syntax near 'END'.

The Last END in the loop

Author

Commented:
Should the IF Loop have an END.
Reza RadConsultant, Trainer

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

Commented:
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

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial