Scribd
Upload
34 views1 page

sg67F SQL

The document contains an SQL update statement that modifies employee records, specifically updating fields such as first name, date of birth, and termination dates. It includes logic to calculate the number of worked days based on various conditions related to employment dates. Additionally, it references employee contracts to derive the necessary date information for the updates.

Uploaded by

Michael
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
34 views1 page

sg67F SQL

The document contains an SQL update statement that modifies employee records, specifically updating fields such as first name, date of birth, and termination dates. It includes logic to calculate the number of worked days based on various conditions related to employment dates. Additionally, it references employee contracts to derive the necessary date information for the updates.

Uploaded by

Michael
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 1

UPDATE

emp.first_name,
emp.DATE_OF_BIRTH,
(select min(eco.date_from) from EMPLOYEE_CONTRACT eco where eco.emp_emp_id =
emp.emp_id and DJTY_DJTY_ID=1) date_from,
(select max(eco.TERMINATION_DATE) from EMPLOYEE_CONTRACT eco where
eco.emp_emp_id = emp.emp_id and DJTY_DJTY_ID=1 and IS_LATEST=1) TERMINATION_DATE,
emp.REF_PFRON_TYPE_ID,

when date_from_eq = 0 and date_termination_eq = 0 then extract(day from


LAST_DAY(to_date('2017-10-01','yyyy-mm-dd')))
when date_from_eq = 0 and date_termination_eq = 1 then extract(day from
TERMINATION_DATE)
else nvl(extract(day from TERMINATION_DATE),extract(day from
LAST_DAY(to_date('2017-10-01','yyyy-mm-dd')))) - extract(day from date_from) + 1
end as worked_days,
case
when date_from_eq = 0 and date_
cus.last_name || ' ' || cus.first_name flname,
emp.last_name,
emp.first_name,
emp.DATE_OF_BIRTH,
(select min(eco.date_from) from EMPLOYEE_CONTRACT eco where eco.emp_emp_id =
emp.emp_id and DJTY_DJTY_ID=1) date_from,
(select max(eco.TERMINATION_DATE) from EMPLOYEE_CONTRACT eco where
eco.emp_emp_id = emp.emp_id and DJTY_DJTY_ID=1 and IS_LATEST=1) TERMINATION_DATE,
emp.REF_PFRON_TYPE_ID,

when date_from_eq = 0 and date_termination_eq = 0 then extract(day from


LAST_DAY(to_date('2017-10-01','yyyy-mm-dd')))
when date_from_eq = 0 and date_termination_eq = 1 then extract(day from
TERMINATION_DATE)
else nvl(extract(day from TERMINATION_DATE),extract(day from
LAST_DAY(to_date('2017-10-01','yyyy-mm-dd')))) - extract(day from date_from) + 1
end as worked_days,
case
when date_from_eq = 0 and date_m-dd') between trunc(date_from,'MM') and
nvl(TERMINATION_DATE,to_date('2999-01-01','yyyy-mm-dd'))

You might also like