I'm studying SQL and just started to use it for business. The client has a database with Oracle system.
As a response a query, I have a table as follows,
/* Defined as START_END_DATE_TABLE*/
Members | Start | End
------------- ------------ -----------
John | 2020-01-07 | 2021-06-10
Smith | 2021-01-12 | 2021-02-24
Brown | 2021-01-24 | 2022-05-01
(Let's suppose types of elements in 'Start' and 'End' columns are already converted to DATE)
Using the table above, I want to get new table with query methods (Oracle SQL) as follows,
Members | DATETIME
------------- ------------
John | 2020-01-07
John | 2020-01-08
... | ...
John | 2021-06-09
John | 2021-06-10
Smith | 2021-01-12
Smith | 2021-01-13
... | ...
Smith | 2021-02-23
Smith | 2021-02-24
Brown | 2021-01-24
Brown | 2021-01-25
... | ...
Brown | 2022-04-30
Brown | 2022-05-01
How can I get the table above using Oracle SQL?
I saw a similar question related to SQL Server, but I could not understand completely. Create a list with dates which are in between start and end dates
I totally appreciate if you tell me simple guides to solve my problem.
CodePudding user response：
From Oracle 12, you can use a LATERAL join to a hierarchical query:
SELECT members,
day
FROM table_name t
CROSS JOIN LATERAL (
SELECT Start_DT LEVEL - 1 AS day
FROM DUAL
CONNECT BY Start_DT LEVEL - 1 <= End_DT
);
You could also use a recursive sub-query factoring clause:
WITH rsqfc (members, day, end_dt) AS (
SELECT * FROM table_name
UNION ALL
SELECT members, day 1, end_dt
FROM rsqfc
WHERE day 1 <= end_dt
)
SEARCH DEPTH FIRST BY members SET order_rn
SELECT members, day
FROM rsqfc
Which, for the sample data:
CREATE TABLE table_name (Members, Start_DT, End_DT) AS
SELECT 'John', DATE '2020-01-07', DATE '2021-06-10' FROM DUAL UNION ALL
SELECT 'Smith', DATE '2021-01-12', DATE '2021-02-24' FROM DUAL UNION ALL
SELECT 'Brown', DATE '2021-01-24', DATE '2022-05-01' FROM DUAL;
Both output:
MEMBERS DAY John 2020-01-07 00:00:00 John 2020-01-08 00:00:00 John 2020-01-09 00:00:00 ... ... John 2021-06-08 00:00:00 John 2021-06-09 00:00:00 John 2021-06-10 00:00:00 Smith 2021-01-12 00:00:00 Smith 2021-01-13 00:00:00 Smith 2021-01-14 00:00:00 ... ... Smith 2021-02-22 00:00:00 Smith 2021-02-23 00:00:00 Smith 2021-02-24 00:00:00 Brown 2021-01-24 00:00:00 Brown 2021-01-25 00:00:00 Brown 2021-01-26 00:00:00 ... ... Brown 2022-04-29 00:00:00 Brown 2022-04-30 00:00:00 Brown 2022-05-01 00:00:00
db<>fiddle here
CodePudding user response：
If your looking for a GENERIC function to generate dates here is something I put together.
CREATE OR REPLACE TYPE nt_date IS TABLE OF DATE;
/
CREATE OR REPLACE FUNCTION generate_dates_pipelined(
p_from IN DATE,
p_to IN DATE
)
RETURN nt_date PIPELINED DETERMINISTIC
IS
v_start DATE := TRUNC(LEAST(p_from, p_to));
v_end DATE := TRUNC(GREATEST(p_from, p_to));
BEGIN
LOOP
PIPE ROW (v_start);
EXIT WHEN v_start >= v_end;
v_start := v_start INTERVAL '1' DAY;
END LOOP;
RETURN;
END generate_dates_pipelined;
/
SELECT
c.COLUMN_VALUE
FROM
TABLE(generate_dates_pipelined(DATE '2022-04-01',
DATE '2022-06-30')) c