SQL Query to find the historical data in a table as of a date

Question

I have to find historical data for a report until the effective date. There is a date tracked table that has the following data -

person_number      start_date       end_date            name            position
10                01/01/2016        31/12/4712         Scott            Manager
11                02/01/2006        25/06/2017        Kirk              Associate
11                26/06/2017        18/03/2023        Kirk              Snr. Associate
11                19/03/2023        31/12/4712        Kirk              DIRECTOR

When I pass the effective date as today's date in the query -

query 1

**select person_number, name, position from abc
where nvl(:p_eff_date,trunc(sysdate)) between start_Date    and end_date**

person_number      start_date       end_date            name            position
10                01/01/2016        31/12/4712         Scott            Manager
11                19/03/2023        31/12/4712        Kirk              DIRECTOR

I want to display the historial output now. i.e. till the effective date that we pass in the parameter. If we pass 01st march 2023 date, the output should be

person_number      start_date       end_date            name            position
10                01/01/2016        31/12/4712         Scott            Manager
11                02/01/2006        25/06/2017        Kirk              Associate
11                26/06/2017        18/03/2023        Kirk              Snr. Associate

The last row will still come because 01st march data is in this time frame. How to tweak the #query 1 to give this result

MT0 · Answer 1 · 2023-03-23T20:16:01.290

You can use an analytic function to do it without querying the table twice:

SELECT person_number,
       name,
       position
FROM   (
  SELECT person_number,
         name,
         position,
         start_date,
         end_date,
         COUNT(
           CASE
           WHEN NVL(:p_eff_date,TRUNC(SYSDATE)) BETWEEN start_Date
                                                    AND end_date
           THEN 1
           END
         ) OVER (PARTITION BY person_number) AS has_match
  FROM   per_all_people
  WHERE  start_date <= NVL(:p_eff_date,TRUNC(SYSDATE))
)
WHERE has_match > 0;

Which, for the sample data:

CREATE TABLE per_all_people (person_number, start_date, end_date, name, position) AS
SELECT 10, DATE '2016-01-01', DATE '4712-12-31', 'Alice', 'Manager'        FROM DUAL UNION ALL
SELECT 11, DATE '2006-01-02', DATE '2017-06-25', 'Betty', 'Associate'      FROM DUAL UNION ALL
SELECT 11, DATE '2017-05-26', DATE '2023-03-18', 'Betty', 'Snr. Associate' FROM DUAL UNION ALL
SELECT 11, DATE '2023-03-19', DATE '4712-12-31', 'Betty', 'Director'       FROM DUAL;

Outputs:

PERSON_NUMBER	NAME	POSITION
10	Alice	Manager
11	Betty	Snr. Associate
11	Betty	Associate

fiddle

You are querying the table twice, in the FROM clause of the outer query — Violet Rosenzweig, Mar 23 '23 at 20:08
@VioletRosenzweig No, check the `EXPLAIN PLAN` for the query and you will see that the table is only queried once. The outer query is merely a filter on the inline view generated by the inner query. There is an explain plan included in the linked fiddle. — MT0, Mar 23 '23 at 20:12

score 0 · Answer 2 · answered Mar 23 '23 at 20:07

0

You could move your "as of" query into a subquery, and then get all of the rows where the ID is in that subquery.

SELECT person_number, start_date, end_date, name, position
FROM per_all_people
WHERE person_number IN (
  SELECT person_number
  FROM per_all_people
  WHERE nvl(:p_eff_date,trunc(sysdate)) BETWEEN start_date AND end_date
)

answered Mar 23 '23 at 20:07

Violet Rosenzweig

486
3
10

This will get future data as well as historical and present data. You need to filter on `AND start_date <= NVL(:p_eff_date,TRUNC(SYSDATE))` in the outer query. – MT0 Mar 23 '23 at 20:16
No, that filter is unnecessary. There should not exist future data for any of the rows returned by the inner query, as we are filtering on sysdate (aka now); If a row is valid now, how would it have a future row as well? – Violet Rosenzweig Mar 23 '23 at 20:19
You filter on the date in the inner query but the outer query has no such filter. If you pass the date parameter `2023-03-01` (as per the OP's example) then the query is not expected to return the future row with the start date `2023-03-19`; however, this query will because the outer query is only filtering on the `person_number` and a person is found by the inner query with matching present data. [fiddle](https://dbfiddle.uk/n75c_drb) – MT0 Mar 23 '23 at 20:24
As far as I can tell, the date will not be passed as a parameter as we are using the current date (systime). Additionally, the asker did not mention that they only want old historical data (from before a specific date). If that is what they want, they can add the filter, but I don't think it is what the question is asking, nor needed for the question if sysdate is always now and not a parameter. – Violet Rosenzweig Mar 24 '23 at 14:04
The OP is using `NVL(:p_eff_date,TRUNC(SYSDATE))` so `SYSDATE` is only used if the bind variable `:p_eff_date` is `NULL` otherwise the user-supplied date is used. – MT0 Mar 24 '23 at 14:55

SQL Query to find the historical data in a table as of a date

2 Answers2