- THE EXTRA OUTPUT THAT THE LAST WORKER HIRED ADDS UPDATE
- THE EXTRA OUTPUT THAT THE LAST WORKER HIRED ADDS FULL
SELECT x.emp_id, e.name, MAX(x.region) AS region, MAX(x.country) AS country, MAX(x.city) AS city, x.effective_dateįROM ( - 1. Compress the sparse matrix to merge records sharing common (emp_id + effective_date) Any of these new "employee" records could have a NULL value in one of the region/country/city fields Union with a set of pseudo employee records generated from the updates table
SELECT emp_id, name, region, country, city, effective_date
THE EXTRA OUTPUT THAT THE LAST WORKER HIRED ADDS FULL
It returns the full set of effective dated records: WITH combined_records AS ( Insert into employee_updates values(3, 'London', 'Toronto', to_date('', 'YYYY-MM-DD'), 'city') Insert into employee_updates values(3, 'UK', 'Canada', to_date('', 'YYYY-MM-DD'), 'country') Insert into employee_updates values(3, 'Europe', 'Canada', to_date('', 'YYYY-MM-DD'), 'region') Insert into employee values(3, 'jane', 'Europe', 'UK', 'London',to_date('', 'YYYY-MM-DD')) Insert into employee_updates values(1, 'mumbai', 'munich', to_date('', 'YYYY-MM-DD'), 'city') Insert into employee_updates values(1, 'india', 'germany', to_date('', 'YYYY-MM-DD'), 'country') Insert into employee_updates values(1, 'asia', 'germany', to_date('', 'YYYY-MM-DD'), 'region') Insert into employee values(2, 'rick', 'americas', 'us', 'ny',to_date('', 'YYYY-MM-DD')) Insert into employee values(1, 'james', 'asia', 'india', 'mumbai',to_date('', 'YYYY-MM-DD')) (Though I left them in my sample.)Ĭreate table employee(emp_id int, name varchar(64), region varchar(64), country varchar(64), city varchar(64), effective_date date)Ĭreate table employee_updates(emp_id int, old varchar(64), new varchar(64), effective_date date, what varchar(64)) I should add, I agree w/ Glenn about not using the reserved words when and new in your column names. In my case, you get the same output as before. When (u.max_country_date > e.effective_date)įrom. When (u.max_city_date > e.effective_date) We can accomplish that with this: select *įrom ( select empid, when, what from updates We start off by getting the most recent change for any single field. This is a rewrite of that answer, going back, so that each field that is being changed is considered individually, rather than jointly.Īll sql is demonstrated at sqlfiddle: !4/ca4f4/6
THE EXTRA OUTPUT THAT THE LAST WORKER HIRED ADDS UPDATE
It was only concerned with applying the most recent update onto the original effective_date values. The first solution did not consider issues that arise from individual fields changing at different times. X.employee_id, e.firstname, x.effective_dateĪND effective_date <= TO_DATE('', 'YYYY-MM-DD') ) I will be needing this report very frequently, so is it better idea to have result of this query as a view which then can be queried for reports?įollowing is the query i have tried so far as answered by Glenn WITH eff_emps AS (Įmp_id, firstname, region_id, country_id, city, effective_dateĬAST(MAX(x.region_id) as number) AS region_id,ĬAST(MAX(x.country_id) as number) AS country_id,ĬASE WHEN COLUMN_NAME = 'REGION_ID' THEN NEW_VALUE ELSE NULL END AS region_id ,ĬASE WHEN COLUMN_NAME = 'COUNTRY_ID' THEN NEW_VALUE ELSE NULL END AS country_id ,ĬASE WHEN COLUMN_NAME = 'CITY' THEN NEW_VALUE ELSE NULL END AS city It might also be possible that some employees may not have any updates then their current record should be shown. Like region, country and city, I have few other fields which may have updates for the employee. In this case all the employees with the updates before 1oth may should be shown with their new latest values. I need to pull out a report to see what will be employee details as on 10th may. So from 1st may his region, country and city will change. Employee james currently work in india but from 1st may he will be transfered to germany. While the employee update table holds the updates which are going to happen to the employee in some future date.Į.g.
Employee table always holds the current details of any employees. I have a employee table and employee update table as shown in the following image.
0 kommentar(er)
