moving selection

[W]

Hi,

I want to be able to see which customers bought in the course of ten
consecutive days, where the BeginDate can be whatever date and which can vary
for each customer, how many products b, e, f one bought out of a range of
products a, b, c, d, e, f, g.

I’ve already tried many queries, amongst others with the Max and Min
function for the date of purchase, but that returns effectively the first and
last possible days. So, if someone buys 5 times on Jan 1st, Jan 3rd, Jan
9th, February 11th, March 3rd, I’ll get for the MinDate Jan 1st and for the
MaxDate March 3rd, regardless of the fact that I have a period of 3
consecutive days which correspond to the criteria, namely : Jan 1st, Jan 3rd,
Jan 9th.

I know this is a difficult one, so any help will be greatly appreciated.

W

 

[Michel Walsh]

A first query:

--------------
SELECT DISTINCT customer, purchaseDate
FROM myTable
-------------

Save it under the name qd. It removes records such as if a customer make 2
purchase the same day. It removes the extra purchase, in that case.


Next,


----------------
SELECT a.customer, a.purchaseDate, COUNT(*) AS rank
FROM qd AS a INNER JOIN qd AS b
ON a.customer=b.customer
AND a.purchaseDate >= b.purchaseDate
GROUP BY a.customer, a.purchaseDate
----------------



saves as qr. Ranking, per customer, will allows to find sequences of
consecutive days.


The last query give what you want, for each sequence ( a given customer may
appear twice, if the customer has two sequences of at least 10 consecutive
days):


------------------
SELECT customer, MIN(purchaseDate), MAX(purchaseDate)
FROM qr
GROUP BY customer, purchaseDate - rank
HAVING MAX(purchaseDate)-MIN(purchaseDate) >= 9
------------------




Hoping it may help,
Vanderghast, Access MVP

 

[W]

Thanks so much for your quick answer.
It helps me because it gives me the customerId, but the dates mentioned in
Min and Max are always the same. Anyway, I can start trying to clear this
out and I'll post back my results to you.

W