Hi,
I am just writing a query to get any employees that are on secondments, leave, etc at a given point in time.
One of the key requirements is to get all the additional data (position record, manager assignment, ect) as of the day when they went on secondment or leave.
For instance, if x goes on leave on 1/01/2025, i want the record for the position or manager as of 01/01/25. I dont want to look at any records that are created after that.
The issue i am facing is that i get duplicates when i join my main query (updated_assignments) to other query (position), but when I investigate position query, I only get one record. I am not too sure what is the reason for that.
I have attached my SQL code for your reference. Also, if there is a better way to write this, please let me know.
Any help would be highly appreciated!
Thanks,
Sim
Code Snippet (add any code snippets that support your topic, if applicable):
WITH Assignment AS (
SELECT asig.,
SUM(
CASE
WHEN asig.prev_status_id IS NULL
OR asig.prev_status_id <> asig.assignment_status_type_id THEN 1
ELSE 0
END
) OVER (
PARTITION BY asig.assignment_id
ORDER BY asig.start_date
) AS group_number / Grouping the events together*/
FROM (
SELECT paa.assignment_id,
paa.person_id,
paa.assignment_number,
paa.organization_id,
paa.primary_flag,
paa.Employment_Category,
paa.grade_id,
paa.assignment_status_type,
paa.assignment_status_type_id,
paa.position_id,
paa.action_occurrence_id,
paa.reason_code AS change_reason,
paa.action_code,
paa.person_type_id,
TRUNC(paa.effective_start_date) AS start_date,
TRUNC(paa.effective_end_date) AS effective_end_date,
TRUNC(paa.projected_assignment_end) AS projected_end_date,
CASE
WHEN pas.assignment_status_code IN ('ACC_SCD', 'ACC_SCD_OUT') THEN TRUNC(paa.****_attribute_date1)
WHEN pas.assignment_status_code IN ('ACC_PLA', 'ACC_LOA') THEN TRUNC(paa.****_attribute_date2)
END AS Projected_return_date,
LAG(paa.assignment_status_type_id) OVER (
PARTITION BY paa.assignment_id
ORDER BY paa.effective_start_date
) AS prev_status_id,
pas.assignment_status_code,
pas.user_status
FROM per_all_assignments_m paa
INNER JOIN per_assignment_status_types_vl pas ON pas.assignment_status_type_id = paa.assignment_status_type_id
WHERE paa.assignment_type IN ('E', 'C')
AND paa.effective_latest_change = 'Y'
AND paa.assignment_status_type IN ('ACTIVE', 'SUSPENDED')
) asig
WHERE asig.assignment_status_code IN ('SCD', 'SCDOUT', 'PLA', 'LOA')
ORDER BY asig.assignment_id,
asig.start_date
),
Modified_start_dates AS (
SELECT a.person_id,
a.assignment_id,
a.group_number,
MIN(a.start_date) AS actual_start_date /* Getting the actual start date for the event /
FROM assignment a
GROUP BY a.person_id,
a.assignment_id,
a.group_number
),
Lastest_Assignment AS (
SELECT b.person_id,
b.assignment_id,
b.group_number,
MAX(b.start_date) AS last_updated_date /Getting the latest updated date for the event/
FROM assignment b
GROUP BY b.person_id,
b.assignment_id,
b.group_number
),
Updated_assignment AS (
SELECT asign.,
ppn.full_name AS Employee_Name,
ppl.person_number AS Employee_Number,
paw.value AS FTE,
ppt.user_person_type AS Person_Type,
pas.manager_id,
pas.manager_assignment_id,
CASE
WHEN TRUNC(asign.actual_start_date) >= TRUNC(SYSDATE) THEN 'Upcoming Changes'
ELSE 'Historical Changes'
END AS event_history
FROM (
SELECT ga.,
msd.actual_start_date,
la.last_updated_date
FROM assignment ga
INNER JOIN Modified_start_dates msd ON ga.assignment_id = msd.assignment_id
AND ga.person_id = msd.person_id
AND ga.group_number = msd.group_number
INNER JOIN Lastest_Assignment la ON ga.assignment_id = la.assignment_id
AND ga.person_id = la.person_id
AND ga.group_number = la.group_number
AND ga.start_date = la.last_updated_date
) asign
INNER JOIN per_assignment_supervisors_f pas ON pas.assignment_id = asign.assignment_id
AND pas.person_id = asign.person_id
INNER JOIN per_person_names_f ppn ON asign.person_id = ppn.person_id
INNER JOIN per_people_f ppl ON ppl.person_id = asign.person_id
INNER JOIN per_assign_work_measures_f paw ON asign.assignment_id = paw.assignment_id
INNER JOIN per_person_types_vl ppt ON ppt.person_type_id = asign.person_type_id
WHERE ppn.name_type = 'GLOBAL'
AND paw.unit = 'FTE'
AND ppt.user_person_type NOT IN ('Dummy Worker')
AND asign.start_date BETWEEN pas.effective_start_date AND pas.effective_end_date
AND asign.start_date BETWEEN ppn.effective_start_date AND ppn.effective_end_date
AND asign.start_date BETWEEN ppl.effective_start_date AND ppl.effective_end_date
AND asign.start_date BETWEEN paw.effective_start_date AND paw.effective_end_date
AND asign.actual_start_date BETWEEN NVL(:DATE_FROM, TO_DATE('01/01/1900', 'dd/MM/yyyy')) AND NVL(:DATE_TO, TO_DATE('01/01/2100', 'dd/MM/yyyy'))
),
),
Position AS (
SELECT p.
FROM (
SELECT pos.position_id,
pos.Position_Code,
pos.Name,
pos.full_part_time,
pos.active_status,
pos.position_type,
pos.FTE,
pos.max_persons,
pos.organization_id,
pos.location_id,
pos.effective_start_date ,
pos.effective_end_date ,
/*ROW_NUMBER() OVER (
PARTITION BY pos.position_id
ORDER BY pos.effective_start_date DESC
) AS rn */
FROM hr_all_positions_f_vl pos
WHERE EXISTS (
SELECT 1
FROM updated_assignment ua
WHERE ua.position_id = pos.position_id
AND ua.start_date BETWEEN pos.effective_start_date AND pos.effective_end_date
)
) p
INNER JOIN updated_assignment u ON u.position_id = p.position_id
WHERE u.start_date BETWEEN p.effective_start_date AND p.effective_end_date
)
SELECT sa.*,
p.Position_Code,
p.Name AS Position_Name,
p.full_part_time,
p.active_status,
p.position_type,
p.FTE AS position_fte,
p.max_persons
FROM updated_assignment sa
INNER JOIN Position p ON p.position_id = sa.position_id
ORDER BY sa.start_date