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

I have a calendar table with below structure :

create table dimdate
  datekey   DATE,
  <other columns related to date>

The above table has all dates .

For each custoemr_num we need to find the total number of days he has an active card between '1/1/2020 ' and '1/31/2020'. We need to take some important points into consideration here :

1)We need to pay attention that for some cards such as card number x and a the issue_date might be smaller than '1/1/2020 ' or bigger than '1/31/2020'.

2)We have to pay attention to overlaps. There might be overlap between issue_date and expire_date of different cards (b,w,z,a).

First we thought that we could calculate the number of active days for each card and then subtract the overlaps from the final result and
what we wrote so far is this :

select t.customer_num,
       lead(t.card_issue_date) over(partition by t.customer_num order by t.card_issue_date) next_card_issue_date,
         when t.card_issue_date < to_date('1/1/2020', 'mm/dd/yyyy') and
              t.card_expire_date > to_date('1/31/2020', 'mm/dd/yyyy') then
          to_date('1/31/2020', 'mm/dd/yyyy') -
          to_date('1/1/2020', 'mm/dd/yyyy')
         when t.card_issue_date < to_date('1/1/2020', 'mm/dd/yyyy') then
          t.card_expire_date - to_date('1/1/2020', 'mm/dd/yyyy')
         when t.card_expire_date > to_date('1/31/2020', 'mm/dd/yyyy') then
          to_date('1/31/2020', 'mm/dd/yyyy') - t.card_issue_date
          t.card_expire_date - t.card_issue_date
       end as card_active_days

  from test_table t
 where t.card_issue_date <= to_date('1/31/2020', 'mm/dd/yyyy')
   and t.card_expire_date >= to_date('1/1/2020', 'mm/dd/yyyy')

but we could not handle the overlaps. I was wondering if you could help me here .

Thanks in advance

  • 1
    GUYS,what a bout 31 - (sum of days that no card was active)????
    – Pantea
    Commented Feb 9, 2021 at 14:28
  • 1
    You'll probably need to use some sort of subquery to produce a result set that handles overlaps, but I don't have the time to figure it out at the moment. Commented Feb 9, 2021 at 14:42
  • 1
    Oracle also has an INTERVAL type, you may find it useful: oracletutorial.com/oracle-basics/oracle-interval Commented Feb 9, 2021 at 14:45
  • 1
    Today's a busy day for me, sorry. Commented Feb 9, 2021 at 14:51
  • 1
    Could you post your desired results given the sample data? I know you explained it but the actual results would be nice to have. Commented Feb 10, 2021 at 21:18

1 Answer 1


I think I have found a good solution for this. Could you tell me if there are better ways other than this one?

select a.customer_num, count(distinct b.datekey) num_of_days
  from (select t.customer_num,
          from test_table t
         where t.card_issue_date <= to_date('1/31/2020', 'mm/dd/yyyy')
           and t.card_expire_date >= to_date('1/1/2020', 'mm/dd/yyyy')) a
 inner join dimdate b
    on b.datekey between a.card_issue_date and a.card_expire_date
   and b.datekey between to_date('1/1/2020', 'mm/dd/yyyy') and
       to_date('1/31/2020', 'mm/dd/yyyy')
 group by a.customer_num
  • 1
    you need a.card_issue_date and a.card_expire_date instead of a.issue_date and a.expire_date and you don't need t.card_number,. Result I get is 1, 25. Commented Feb 11, 2021 at 19:32
  • @BobbyDurrett Thanks for your comment . I just edited my query and yes that's the correct result . I think I found the solution :)
    – Pantea
    Commented Feb 11, 2021 at 19:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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