0

I am developing an employee leave management system and

My table structure with data is :

+-----------+---------+----------+----------+------------+--------------+
|   EmpID   |   CL    |     PL   | ISActive | LastUpdate |  NextUpdate  |
+-----------+---------+----------+----------+------------+--------------+
|   E001    |   5     |     3    |    1     | 2015-01-01 |  2015-02-01  |
+-----------+---------+----------+----------+------------+--------------+
|   E002    |   3     |     7    |    1     | 2015-01-01 |  2015-02-01  |
+-----------+---------+----------+----------+------------+--------------+
|   E003    |   0     |     1    |    1     | 2015-01-01 |  2015-02-01  |
+-----------+---------+----------+----------+------------+--------------+
|   E004    |   5     |     1    |    0     | 2015-01-01 |  2015-02-01  |
+-----------+---------+----------+----------+------------+--------------+

I want every active employee to get 2 CL on the 1st of every month and 1 SL every 45 days automatically. I have been told it is possible to do this using a cursor, but i don't know.

Is there any other efficient way to do this?

1

I also agree with @James Anderson you would have to use SQL Agent for better result.

If you don't know how to setup a SQL Job then, Please see this link for your reference how to set a SQL Job.

| improve this answer | |
1

I don't think a cursor is good solution to this as it would have to be running 24 / 7 and you would need to check it was running. A better solution would be to have a SQL Agent job that runs at midnight each night. The Agent job would call a stored procedure that checks the IsActive flag, LastUpdate field and NextUpdate field for each employee and then increments the mentioned values for each employee.

The stored procedure could send an email after each run to show you how many records were updated. It could also incorporate error handling to alert you if there was a failure for some reason. This way you can be sure the process is running correctly.

| improve this answer | |
  • thank you i like your approach and i can able to create a sp upon my requirement, but how to set up SQL Agent to call the sp – Tufan Chand Jul 14 '15 at 8:29
  • You can use SSMS to create a new job with 1 step with EXEC sp_YourStoredProcedure. You then need to add a schedule and a notification to alert you of any failures in the SP. – James Anderson Jul 14 '15 at 8:32
0

Let do with job in sql agent its easier.

  • first job: create store procedure for update 1ct on the 1st of every month then excute sp job do on 1st of month
  • seconde job: create sp check last update + 45 day for update 1ct. you can set this job do everday
| improve this answer | |
  • Please consider providing a somewhat more complete answer to the question. Your current answer is just a slight recommendation and does not provide an adequate solution. Please read the article How do I write a good answer? to get an idea of what people are looking for. Thank you for participating in the community. – John aka hot2use Aug 6 '18 at 7:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.