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?

I'm not sure I understand what you're doing. Why is your filter in the select count clause instead of the where clause?
First do a select * from table where closed date is within 30 days. Then do another select customer id, count (*) from the previous result set, group by customer id

Well in that same query I'm pulling in about 15 other metrics, which have vastly different criteria, so I need it in the select statement.

I guess my follow up question to that is , is it possible to add in some criteria in the select statement as a workaround?

setting the date trunc to equal one another unfortunately doesn't capture the cases where the closed date falls into the month after.

This post was edited by Dtrain3083 on Jan 12 2016 07:46am

http://www.w3schools.com/sql/func_date_sub.asp

use date functions to get rid of this problem
first you define interval by using function
then you can trunc to fit your display requests

so if i understand correctly, you want to see the two rows (from your example) and just have the top one with a 0 count? perhaps you can show your sample output.

suppose your data looks like this:


what is your desired output?

1) 0 for each row not within 30 days, 1 otherwise. this can be accomplished with the case statement; no count required



2) you said "most recent email_date":


or something else? keep in mind if you wanna see less than 4 rows, you'll need something in the where or a group by, as i suggested earlier. if you wanna see all 4 rows, you'll have to clarify what the count represents.

This post was edited by carteblanche on Jan 12 2016 10:51pm