CTE does not work for all cases — Cloud Customer Connect
You're almost there! Please answer a few more questions for access to the Applications content. Complete registration
Interested in joining? Complete your registration by providing Areas of Interest here. Register

CTE does not work for all cases

in BI Publisher Learning Center

I’ve received a request to create a BI Publisher report that meets the following business requirement:

  • Given a date parameter (e.g., 31/12/2024), the report must return all employees hired up to and including that date.
  • For each employee, the report should display the assignment active at that time.
  • If the employee was terminated before the given date, the report should still include them, showing the last valid assignment the person had before termination.

Solution Design:

To address this, I created the following three subqueries (CTEs):

1. N_PER_ALL_PEOPLE

SELECT *
FROM (

  SELECT PAPF.*,

         ROW_NUMBER() OVER (

           PARTITION BY person_id
Tagged:

Howdy, Stranger!

Log In

To view full details, sign in.

Register

Don't have an account? Click here to get started!