SQL count distinct # of calls 6 months prior to create date

Am trying to figure out the SQL to:

1. count # of distinct calls
2. made on an account 6 months prior to the account being created

I also need to CAST the date field. I’m thinking something like:

case when (call_date as date format 'MM/DD/YYYY') 
between (create_date as date format 'MM/DD/YYYY') and 
(ADD_MONTHS, (create_date as date format 'MM/DD/YYYY), -6) 
then COUNT (DISTINCT call_nbr) as calls

Here’s a snippet of the data i am working with. The answer I require 3 Calls.

Note: both dates are flagged in the db table as DATE format.

• Call_Nbr…..Call Date……Create Date
• 12345……..03/14/2020….07/23/2020…..include in result set
• 12345……..03/14/2020….07/23/2020…..exclude in result set
• 45678……..02/14/2020….07/23/2020…..include in result set
• 91011……..01/20/2020….07/23/2020…..include in result set
• 91211……..01/24/2020….07/23/2020…..exclude in result set
• 12345……..11/14/2019….07/23/2020…..exclude in result set

Answer

I think you want:

select count(distinct call_nbr) no_calls
from mytable
where call_date >= add_months(create_date, -6)

If you have a column that represnets the account_id, then you can use a group by clause to get the count of calls per account:

select account_id, count(distinct call_nbr) no_calls
from mytable
where call_date >= add_months(create_date, -6)
group by account_id

---

Edit: it seems like you want conditional aggregation instead:

select 
    account_id, 
    count(distinct case when call_date >= add_months(create_date, -6) then call_nbr end) no_calls
from mytable
group by account_id