oracle SQL查询查找表中截至某个日期的历史数据

hc8w905p 于 2023-03-29 发布在 Oracle

关注(0)|答案(2)|浏览(177)

我必须查找有效日期之前的报告历史数据。有一个日期跟踪表包含以下数据-

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

当我在查询中传递生效日期作为今天的日期时-
查询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

我想现在显示历史输出。即，直到我们传入参数的生效日期。如果我们传递2023年3月1日日期，输出应为

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

最后一行仍然会出现，因为01 st march数据在这个时间范围内。

oracle

来源：https://stackoverflow.com/questions/75827514/sql-query-to-find-the-historical-data-in-a-table-as-of-a-date

2条答案

按热度按时间

owfi6suc1#

您可以使用分析函数来完成此操作，而无需两次查询表：

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;

其中，对于示例数据：

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;

输出：
| 人员编号|名称|职位|
| --------------|--------------|--------------|
| 10个|爱丽丝|管理者|
| 十一|贝蒂|高级助理|
| 十一|贝蒂|协理|
fiddle

赞(0）回复(0）举报 2023-03-29

pod7payv2#

您可以将“as of”查询移动到子查询中，然后获取该子查询中ID所在的所有行。

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
)

赞(0）回复(0）举报 2023-03-29

我来回答

oracle SQL查询查找表中截至某个日期的历史数据

2条答案

相关问题

热门标签

最新问答