日付の連番を作成したい

Question

実現したいこと

以下の様なテーブルがあったとき、

employee_no	start_date	end_date
11111	2023/1/3	2023/1/3
22222	2023/1/3	2023/1/3
33333	2023/1/3	2023/1/5

以下のように並べかえをしたい

effective_date	employee_no
2023/1/3	11111
2023/1/3	22222
2023/1/3	33333
2023/1/4	33333
2023/1/5	33333

該当のソースコード

SELECT TO_DATE(start_date,'YYYY/MM/DD' HH24:MI:SS')+LEVEL -1 AS effective_date,employee_no FROM EMPLOYEE CONNECT BY TO_DATE(start_date,'YYYY/MM/DD' HH24:MI:SS')+LEVEL -1 <=TO_DATE(end_date,'YYYY/MM/DD' HH24:MI:SS'); 

試したこと

上記結果は、以下のようになり、想定どおりになりません。
11111,1/3
22222,1/3
33333,1/3
33333,1/4
33333,1/4
33333,1/4
33333,1/5
33333,1/5
33333,1/5

ちなみに、以下レコードのみであれば、

employee_no	start_date	end_date
33333	2023/1/3	2023/1/5

出力結果は上手く出力されます。
1/3,33333
1/4,33333
1/5,33333

Answer 1

Oracle SQL 連番や連続した日付を取得 を参考に。

Answer 2

すでに回答がございますが、僕も考えてみましたので投稿させてください。
今回の課題に対しては目的からずれてしまい、必要なかったかもしれませんが…。

拝見した課題文で元のデータが「employee_no」を軸にしており、
集計目的のデータは「effective_date」が軸にしたいのではないかと推察しまして
カレンダー起点での集計でクエリを組んでみました。

カレンダー起点で集計することで、
保持しているデータセットの日時データの有無に左右されずにカレンダーの生成でき、
集計結果に日付の歯抜けが発生することを防ぐことができます。

何かのご参考になれば幸いです。

with cal as (
    select 
        DATE_ADD('2023-01-02', INTERVAL ROWNUM DAY) as effective_date
    from (
        select 
            ROW_NUMBER() over(order by employee_no) as ROWNUM
        from 
           employee
    ) as r
    order by effective_date 
    limit 3 /* カレンダーを「N」日間分と指定する */
)
select
    cal.effective_date
    ,em.employee_no
from 
    cal
left join 
   employee 
    on cal.effective_date between employee.start_date and employee.end_date
order by cal.effective_date, employee.employee_no
;

Answer 3

実現方法はさまざまあると思いますが、WITHによる再帰クエリを利用する方法が個人的にはもっともシンプルかと思います。

WITH R (effective_date, end_date, employee_no) AS (
    SELECT start_date, end_date, employee_no FROM EMPLOYEE
  UNION ALL
    SELECT effective_date + INTERVAL '1' DAY, end_date, employee_no 
    FROM R 
    WHERE effective_date < end_date
)
SELECT effective_date, employee_no FROM R

以下は稼働確認に利用したDDLです。

CREATE TABLE EMPLOYEE (
  employee_no CHAR(5) NOT NULL PRIMARY KEY,
  start_date  DATE    NOT NULL,
  end_date    DATE    NOT NULL
);
INSERT INTO EMPLOYEE VALUES ('11111', TO_DATE('2023/01/03', 'YYYY/MM/DD'), TO_DATE('2023/01/03', 'YYYY/MM/DD'));
INSERT INTO EMPLOYEE VALUES ('22222', TO_DATE('2023/01/03', 'YYYY/MM/DD'), TO_DATE('2023/01/03', 'YYYY/MM/DD'));
INSERT INTO EMPLOYEE VALUES ('33333', TO_DATE('2023/01/03', 'YYYY/MM/DD'), TO_DATE('2023/01/05', 'YYYY/MM/DD'));