# Query to find the "Number of days" a customer has an active card between a specified interval

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,
t.card_number,
t.card_issue_date,
t.card_expire_date,
lead(t.card_issue_date) over(partition by t.customer_num order by t.card_issue_date) next_card_issue_date,
case
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

else
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 .

• GUYS,what a bout 31 - (sum of days that no card was active)???? Commented Feb 9, 2021 at 14:28
• 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
• Oracle also has an INTERVAL type, you may find it useful: oracletutorial.com/oracle-basics/oracle-interval Commented Feb 9, 2021 at 14:45
• Today's a busy day for me, sorry. Commented Feb 9, 2021 at 14:51
• 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

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,
t.card_number,
t.card_issue_date,
t.card_expire_date
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
``````
• 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 :) Commented Feb 11, 2021 at 19:38