Need to update a table with In and Out values. RRS feed

  • 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.

    Thursday, April 26, 2018 5:12 AM

All replies

  • 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.



    Friday, April 27, 2018 3:37 AM