Need help with selecting distinct event date within last 180 days

Database
Enthusiast

Need help with selecting distinct event date within last 180 days

Select A.SubscriberKey,COUNT(DISTINCT EventDate) AS Count,B.CreatedDate

From _Open A

JOIN _ListSubscribers B

ON A.SubscriberKey = B.SubscriberKey

Where B.ListID = '10630' AND B.CreatedDate Between 'Jan 10 2009' AND 'Nov 06 2012' AND EventDate > (Select DATEADD(day,-180,getdate()))

Group By A.SubscriberKey,B.CreatedDate

I want to select only Distinct Event Date's that have occured in the past 180 days. Query is pulling info, but I am getting a much higher count than is possible for the time frame. For example, the user can only have a max of 60 distinct event dates in the past 180 days and I am getting users that have 1,000+. Is this because the event date is going further back? Thanks for any help!!

2 REPLIES
WAQ
Enthusiast

Re: Need help with selecting distinct event date within last 180 days

First of all correct this statement in the query:

B.CreatedDate Between 'Jan 10 2009' AND 'Nov 06 2012'

You are not comparing createdDate with the actual DATES. Cast Jan 10 2009 and Nov 06 2012 to date and then see if it works.

Enthusiast

Re: Need help with selecting distinct event date within last 180 days

Try this query...

SELECT A.SubscriberKey, B.CreatedDate, EventDate,Count(EventDate) as "count"

FROM _Open A

JOIN _ListSubscribers B

ON A.SubscriberKey = B.SubscriberKey

WHERE (B.ListID = '10630') AND (B.CreatedDate Between 1090110 AND 1121106)

(AND EventDate > (DATE-180))

Group By A.SubscriberKey,B.CreatedDate,EventDate