我有一段时间没有做 Oracle 了，但您正在研究两个组件：

1. 当前快照
2. 固定的历史快照

这会为给定的硬编码日期生成快照。我没有 Oracle 来检查变量是如何工作的，所以你必须自己做日期变量部分。

注意：

• 我假设一个 Cust_ID 一次只能有一个状态
• 现实世界的数据比这更复杂，而且总是存在边缘情况
• 如果 Cust 没有当前状态，则不会有行
• 刚刚注意到您的日期重叠。这是一个问题，因为客户同时处于两种状态

您可以加入一个日历表来为所有日期运行它，但这可能会非常消耗性能，并且您通常只想每天生成以添加到现有表中。

下面是从小提琴复制的代码

设置代码

CREATE TABLE t
    (FactID int,Cust_ID int,Status varchar2(1),EffectiveDate DATE,EndDate DATE)
;

INSERT ALL 
    INTO t (FactID,Cust_ID,Status,EffectiveDate,EndDate)
         VALUES (1,1,'A',TIMESTAMP'2021-05-20 08:52:29.000',TIMESTAMP'2021-05-21 03:08:22.000')
    INTO t (FactID,EndDate)
         VALUES (2,'B',TIMESTAMP'2021-05-21 03:08:22.000',TIMESTAMP'2021-05-24 02:47:28.000')
    INTO t (FactID,EndDate)
         VALUES (3,'C',TIMESTAMP'2021-05-24 02:47:28.000',TIMESTAMP'2021-05-24 04:15:45.000')
    INTO t (FactID,EndDate)
         VALUES (4,TIMESTAMP'2021-05-24 04:15:45.000',TIMESTAMP'2021-05-24 08:05:09.000')
    INTO t (FactID,EndDate)
         VALUES (5,'D',TIMESTAMP'2021-05-24 08:05:09.000',TIMESTAMP'9000-12-31 00:00:00.000')         

SELECT * FROM dual
;

查询

SELECT
T.Cust_ID,DATE '2021-05-25' ReportDate,T.Status,T.EffectiveDate,T.EndDate,H.A_SDATE,H.A_EDATE,H.B_SDATE,H.B_EDATE,H.C_SDATE,H.C_EDATE
FROM
(
    -- Todays snapshot
    SELECT Cust_ID,EndDate
    FROM t 
    WHERE DATE '2021-05-25' BETWEEN EffectiveDate AND EndDate 
) T
LEFT OUTER JOIN
(
-- Static capture of all states
    SELECT Cust_ID,MIN(CASE WHEN Status = 'A' THEN EffectiveDate ELSE NULL END) A_SDATE,MAX(CASE WHEN Status = 'A' THEN LEAST(DATE '2021-07-10',EndDate) ELSE NULL END) A_EDATE,MIN(CASE WHEN Status = 'B' THEN EffectiveDate ELSE NULL END) B_SDATE,MAX(CASE WHEN Status = 'B' THEN LEAST(DATE '2021-05-25',EndDate) ELSE NULL END) B_EDATE,MIN(CASE WHEN Status = 'C' THEN EffectiveDate ELSE NULL END) C_SDATE,MAX(CASE WHEN Status = 'C' THEN LEAST(DATE '2021-05-25',EndDate) ELSE NULL END) C_EDATE

    FROM t 
    -- Exclude state changes after the process date
    WHERE EffectiveDate < DATE '2021-05-25'
    GROUP BY Cust_ID
) H
ON T.Cust_ID = H.Cust_ID

首先，我衷心感谢所有试图帮助我的人。我以某种方式设法用一些复杂的逻辑来完成这个几乎不可能的任务（但它有效）。我尝试提供内联注释来解释推导。特别提及@Wernfried Domscheit，他编写了 PIVOT 逻辑并删除了答案，这在很大程度上帮助了我。

WITH
    date_ranges
-- Generate dates 
    AS
        (SELECT ROWNUM,TO_DATE ('21-05-2021','dd-mm-yyyy') + ROWNUM - 1.00001 reportdate
           FROM all_objects
          WHERE ROWNUM <= 6),-- Mock up source records
    transactions (factid,cust_id,status,effectivedate,enddate)
    AS
        (SELECT 1,TO_DATE ('20/05/2021 8:52:29 PM','DD/MM/YYYY HH12:MI:SS AM'),TO_DATE ('21/05/2021 3:08:22 PM','DD/MM/YYYY HH12:MI:SS AM') FROM DUAL
         UNION ALL
         SELECT 2,TO_DATE ('24/05/2021 2:47:28 PM','DD/MM/YYYY HH12:MI:SS AM') FROM DUAL
         UNION ALL
         SELECT 3,TO_DATE ('24/05/2021 4:15:45 PM','DD/MM/YYYY HH12:MI:SS AM') FROM DUAL
         UNION ALL
         SELECT 4,TO_DATE ('24/05/2021 8:05:09 PM','DD/MM/YYYY HH12:MI:SS AM') FROM DUAL
         UNION ALL
         SELECT 5,TO_DATE ('31/12/9000','DD/MM/YYYY') FROM DUAL),dataset
-- Apply cross join to get report date into transactions
-- Could've been much better; time crunched
    AS
        (SELECT DISTINCT reportdate,status     AS eodstatus,enddate
           FROM transactions CROSS JOIN date_ranges),dataset1
-- Ignore start and end dates if they are older than the reporting date
    AS
        (  SELECT reportdate,eodstatus,CASE
                      WHEN reportdate > effectivedate THEN effectivedate
                      ELSE TO_DATE ('31/12/9000','DD/MM/YYYY')
                  END    AS effectivedate,CASE
                      WHEN reportdate > enddate THEN enddate
                      ELSE TO_DATE ('31/12/9000','DD/MM/YYYY')
                  END    AS enddate
             FROM dataset
            WHERE reportdate > effectivedate),dataset2
-- Grab the min of start and max of end for all reporting days
    AS
        (  SELECT reportdate,eodstatus               AS status,MIN (effectivedate)     effectivedate,MAX (enddate)           enddate
             FROM dataset1
         GROUP BY reportdate,eodstatus),dataset_new
-- Apply PIVOT to capture the start and end date per known statues and replacing NULLs with high open end dates
    AS
        (  SELECT reportdate,COALESCE ('A','D')                           AS status,NVL (a_sdate,'DD/MM/YYYY'))    a_sdate,NVL (a_edate,'DD/MM/YYYY'))    a_edate,NVL (b_sdate,'DD/MM/YYYY'))    b_sdate,NVL (b_edate,'DD/MM/YYYY'))    b_edate,NVL (c_sdate,'DD/MM/YYYY'))    c_sdate,NVL (c_edate,'DD/MM/YYYY'))    c_edate,NVL (d_sdate,'DD/MM/YYYY'))    d_sdate,NVL (d_edate,'DD/MM/YYYY'))    d_edate
             FROM dataset2
                  PIVOT (MIN (effectivedate) AS "SDATE",MAX (enddate) AS "EDATE"
                        FOR status
                        IN ('A' AS "A",'B' AS "B",'C' AS "C",'D' AS "D"))
         ORDER BY reportdate),date_manipulations
-- Merging multiple entries into one record a day
    AS
        (  SELECT reportdate,MIN (a_sdate)     a_sdate,MIN (a_edate)     a_edate,MIN (b_sdate)     b_sdate,MIN (b_edate)     b_edate,MIN (c_sdate)     c_sdate,MIN (c_edate)     c_edate,MIN (d_sdate)     d_sdate,MIN (d_edate)     d_edate
             FROM dataset_new
         GROUP BY reportdate,cust_id
         ORDER BY 1)
-- JOIN with transaction to report the original status 
SELECT a.*,b.status
  FROM date_manipulations a JOIN transactions b ON reportdate BETWEEN effectivedate AND enddate;