Count distinct pulling in counts for multiple rows of same id..how to fix?

1

I have a marketing table with a customer ID, email date, and a close date (when the sale is closed). I have a filter that basically says the closed date must occur within 30 days of the email date. The problem is that in certain cases where the close date occurs within 30 days of the email_date, I am double counting. I want the count to only show a 1 for the most recent email date, and never to double count.

Sample query to give you an idea:

DATE_TRUNC('month', email_date)
,count(distinct case when closed_date - email-date <= interval '30 days' then customer_id else null end)

Output Example Showing the double counting Problem:

Customer ID Email Date Count(distinct) Closed_Date

123 2015-05-01 1 2015-06-04
123 2015-06-01 1 2015-06-04

I've tried adding a filter like this:

date_trunc('month', email_date) = date_trunc('month', 'close_date)

DOESN'T WORK BECAUSE
the problem is that the close date can theoretically occur in '2015-07-01' because it's possible an email was sent towards the end of a month.

THOUGHTS?

2

This is a forum for SQL Server, so if you are using another RDBMS, this would not be the most appropriate forum to post questions.

Regardless: when you have an aggregate function such as COUNT, in T-SQL, you will also need a GROUP BY clause (which you have not shown if you do have one). The query will return one row for each distinct combination of the columns listed in the GROUP BY clause.

In SQL Server, all columns in the select list that are outside of an aggregate function must be listed in the GROUP BY clause.

3

Hey James.

That's part of the problem.

It will return distinct combinations, but the issue is that the close date can fall into more than one of these distinct combinations for a single id, so I'm basically looking for a way to say let it only fall into ONE of the buckets, regardless if it meets criteria for more than one, whether it's the max date, or min, date.

I know that sort of defies logic, but I need it to only appear once otherwise I'm double counting.