I have a table with below structure
create table test_table
(
customer_num NUMBER,
card_number char(2),
card_issue_date DATE,
card_expire_date DATE
)
Example data is:
customer_num | card_number | card_issue_date | card_expire_date
--------------------------------------------------------------------------
1 | x | 1/1/2019 | 1/4/2020
1 | y | 1/1/2020 | 1/4/2020
1 | z | 1/11/2020 | 1/15/2020
1 | b | 1/11/2020 | 1/20/2020
1 | w | 1/12/2020 | 1/17/2020
1 | a | 1/18/2020 | 2/12/2020
For each customer's card
, I need to have all dates between card_issue_date and card_expire_date
.
So the output for the first record would be like this :
customer_num | card_number | card_issue_date
----------------------------------------------------
1 | x | 1/1/2019
1 | x | 1/2/2020
1 | x | 1/3/2020
1 | x | 1/4/2020
IS there any function or something else that can help me out with this?
Thanks in advance
Best Answer
This should get you started:
Just use simple date arithmetic: the difference between two dates is the number of days between the two:
The above returns 4 (the +1 is so as to also include the last day, since this is what you describe in your example).
Then the rest is just a loop over the number of days, incrementing the start date by one day on each loop:
The next stage is to turn that into a usable function. First we need a type:
This defines an array of dates as is used as the output of the pipelined function:
This function returns the list of dates between two dates (including the end date). Like this:
Let's try that with your example. First let's create and populate the test table:
(Noticed I corrected the first date to match your description).
Here is the query that gives the result you describe:
which returns: