I need you help please with writing a SQL code.
I need to join these 2 table to one table.
Table 1 (orderId, employeeId, name, arrival_time, departare_time, comment, GoodJob ,Reason, event_date)
Table 2 (orderId, GoodJob)
- GoodJob is Yes/No field.
these are what I need to implement:
If there is a record in table 2, take the goodJob firstly from there. If not, take the value from table 1.
Table 1 feeds every day. I would like to have only the max event_date in the final table.
On the final table, when it's 'No' on GoodJob field, leave the Reason field as Null, if it 'yes' take the Reason.
The event_date field is only a date field (YYYY-MM-DD).
I would like to have new field of creation_time. So, if there is 2 different recodes, one with 'No' and other with 'Yes' I could know what is the the most updated one.