1

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

2
  • 1
    Are you trying to get all of the dates or the count of dates between the issue/expiry date? – armitage Feb 10 at 11:31
  • @armitage I need to get all of the dates. If I have a calendar table that has all dates in it, do you think I can do this with a simple inner join between that calendar table and the above table? – Pantea Feb 10 at 11:46
2

This should get you started:

declare
  s date := date'2020-01-01';
  e date := date'2020-01-04';
  ndays number;
begin
  ndays := e-s+1;
  for i in 1..ndays loop
    dbms_output.put_line (s);
    s := s+1;
  end loop;
end;
/

01-JAN-2020
02-JAN-2020
03-JAN-2020
04-JAN-2020

PL/SQL procedure successfully completed.

Just use simple date arithmetic: the difference between two dates is the number of days between the two:

date'2020-01-04' - date'2020-01-01' + 1

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:

s := s + 1;

The next stage is to turn that into a usable function. First we need a type:

create or replace type date_array as table of date;
/

This defines an array of dates as is used as the output of the pipelined function:

create or replace function dates_between (s date, e date)
return date_array
pipelined
is
  d date := s;
begin
  for i in 1..e-s+1 loop
    pipe row(d);
    d := d + 1;
  end loop;
end;
/
show errors

This function returns the list of dates between two dates (including the end date). Like this:

SQL> select * from table (dates_between (date'2020-02-23',date'2020-03-05'));

COLUMN_VALUE
-----------------
23-FEB-2020
24-FEB-2020
25-FEB-2020
26-FEB-2020
27-FEB-2020
28-FEB-2020
29-FEB-2020
01-MAR-2020
02-MAR-2020
03-MAR-2020
04-MAR-2020
05-MAR-2020

12 rows selected.

Let's try that with your example. First let's create and populate the test table:

drop table test_table purge; 
create table test_table (
  customer_num      NUMBER,
  card_number       char(2),
  card_issue_date   DATE,
  card_expire_date  DATE
);
insert into test_table values (1,'x',to_date('1/1/2020 ','MM-DD-YYYY')  ,to_date('1/4/2020','MM-DD-YYYY'));
insert into test_table values (1,'y',to_date('1/1/2020 ','MM-DD-YYYY')  ,to_date('1/4/2020','MM-DD-YYYY'));
insert into test_table values (1,'z',to_date('1/11/2020','MM-DD-YYYY')  ,to_date('1/15/2020','MM-DD-YYYY'));
insert into test_table values (1,'b',to_date('1/11/2020','MM-DD-YYYY')  ,to_date('1/20/2020','MM-DD-YYYY'));
insert into test_table values (1,'w',to_date('1/12/2020','MM-DD-YYYY')  ,to_date('1/17/2020','MM-DD-YYYY'));
insert into test_table values (1,'a',to_date('1/18/2020','MM-DD-YYYY')  ,to_date('2/12/2020','MM-DD-YYYY'));
commit;

(Noticed I corrected the first date to match your description).

Here is the query that gives the result you describe:

select customer_num, card_number, column_value as card_date
from test_table, table (dates_between (card_issue_date,card_expire_date));

which returns:

CUSTOMER_NUM CARD_N CARD_DATE
------------ ------ -----------------
           1 x      01-JAN-2020
           1 x      02-JAN-2020
           1 x      03-JAN-2020
           1 x      04-JAN-2020
           1 y      01-JAN-2020
           1 y      02-JAN-2020
           1 y      03-JAN-2020
           1 y      04-JAN-2020
           1 z      11-JAN-2020
           1 z      12-JAN-2020
           1 z      13-JAN-2020
           1 z      14-JAN-2020
           1 z      15-JAN-2020
           1 b      11-JAN-2020
           1 b      12-JAN-2020
           1 b      13-JAN-2020
           1 b      14-JAN-2020
           1 b      15-JAN-2020
           1 b      16-JAN-2020
           1 b      17-JAN-2020
           1 b      18-JAN-2020
           1 b      19-JAN-2020
           1 b      20-JAN-2020
           1 w      12-JAN-2020
           1 w      13-JAN-2020
           1 w      14-JAN-2020
           1 w      15-JAN-2020
           1 w      16-JAN-2020
           1 w      17-JAN-2020
           1 a      18-JAN-2020
           1 a      19-JAN-2020
           1 a      20-JAN-2020
           1 a      21-JAN-2020
           1 a      22-JAN-2020
           1 a      23-JAN-2020
           1 a      24-JAN-2020
           1 a      25-JAN-2020
           1 a      26-JAN-2020
           1 a      27-JAN-2020
           1 a      28-JAN-2020
           1 a      29-JAN-2020
           1 a      30-JAN-2020
           1 a      31-JAN-2020
           1 a      01-FEB-2020
           1 a      02-FEB-2020
           1 a      03-FEB-2020
           1 a      04-FEB-2020
           1 a      05-FEB-2020
           1 a      06-FEB-2020
           1 a      07-FEB-2020
           1 a      08-FEB-2020
           1 a      09-FEB-2020
           1 a      10-FEB-2020
           1 a      11-FEB-2020
           1 a      12-FEB-2020

55 rows selected.
-2

Using just SQL and using Tom's as_many_rows trick(adjust as required):

with as_many_rows_as_you_need
as
( select level l
  from dual
  connect by level <= 100
) 
select customer_num cust, card_number card, (a.card_issue_date + l) dat
from   as_many_rows_as_you_need
     , test_table a
where  a.card_issue_date + l <= a.card_expire_date
and    a.customer_num = 1
--and    a.card_number  = 'x'
order by card_number, (a.card_issue_date + l)
;

Using above table definition and data, results in(cut-off...):

CUST    CARD    DAT
1   a   19-jan-20
1   a   20-jan-20
1   a   21-jan-20
1   b   12-jan-20
1   b   13-jan-20
1   b   14-jan-20
1   b   15-jan-20
1   b   16-jan-20
1   b   17-jan-20
1   b   18-jan-20
1   b   19-jan-20
1   b   20-jan-20
1   w   13-jan-20
1   w   14-jan-20
1   w   15-jan-20
1   w   16-jan-20
1   w   17-jan-20
...
3
  • ok sorry I see now that I had missed the clue. Retry below... – KoJaman Feb 10 at 13:22
  • h'm I wonder what Pantea and @Albert Godfrind think of this solution? – KoJaman Feb 11 at 16:38
  • Can anybody explain what's missing in my answer? Just to learn. I'd say my answer tics all boxes and personally I would always go for an SQL solution if I have the choice. 9 out of 10 times it's plain more efficient. Technically speaking SQL is also PL/SQL, just not the other way around. – KoJaman Mar 31 at 13:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.