Need to update a table with In and Out values.

Question

User928219468 posted

Dear All,

We are having one table as below, we need to update In and Out based on employee number and Officepunch , i.e first record of table will be "In" based on employee number and Officepunch.

For Example:

RDISPL10 2017-04-19 06:55:30.000 IN

RDISPL10 2017-04-20 06:53:02.000 IN

RDISPL10 2017-04-20 06:53:02.000 IN

RDISPL10 2017-04-21 06:54:19.000 OUT

Original Table is as below.

EmployeeNo	OfficePunch	INOUT
RDISPL10	2017-04-18 15:30:39.000	NULL
RDISPL10	2017-04-19 06:55:30.000	NULL
RDISPL10	2017-04-20 06:53:02.000	NULL
RDISPL10	2017-04-20 16:44:06.000	NULL
RDISPL10	2017-04-21 06:54:19.000	NULL
RDISPL10	2017-04-21 16:54:23.000	NULL
RDISPL10	2017-04-22 06:51:23.000	NULL
RDISPL10	2017-04-22 17:32:35.000	NULL
RDISPL10	2017-04-23 06:51:23.000	NULL

Kindly help in meeting above requirement.

Answer 1

User347430248 posted

Hi office_punch,

Instead of updating the value, I think you need to change your logic and you need to set the value for INOUT at insertion time.

Here, I assume that there is only two entries in table. One for IN and other for OUT.

At the time of insertion, You can check that whether there is any entry exist for this particular employ for today's date or not.

If there is no entry exist then you can insert the INOUT value as IN.

Same way, again check for the particular employee for today's date. If there is any entry exist then insert the new record with INOUT value as OUT.

You can use IF..ELSE in your query.

ELSE (IF...ELSE) (Transact-SQL)

IF...ELSE (Transact-SQL)

As per my understanding this is the suitable way to handle this issue.

Regards

Deepak