D
Daniel
I'm a newbie to databases and SQL, but here's my problem as best I can
describe. I'm using Access 2000 on WinXP.
I have a single table (CustomerVisits) that has CustomerID,
VisitTimeDate, and ItemPurchased as fields.
I want a query that returns the latest item that each Customer
purchased in the last 30 days. i.e., if I had 20,000 unique customers
in the last 30 days, I want what each of of them purchased on their
latest visit.
I came up with a query:
SELECT tbl1.ItemPurchased FROM CustomerVisits As tbl1
WHERE VisitTimeDate = (
SELECT Max(tbl2.VisitTimeDate) FROM CustomerVisits As tbl2
WHERE tbl2.CustomerID = tbl1.CustomerID AND
tbl2.VisitTimeDate >= curr_date - 30 AND
tbl2.VisitTimeDate <= curr_date
);
This works fine when there are only a few thousand records in the
table, but the more records I have, the longer it takes. With 1.5
million records, the time it takes to retrieve the results is
unacceptable.
I've indexed CustomerID and VisitTimeDate, but still too long.
I found the following query that is plenty fast even with 1.5M records:
SELECT CustomerID, Max(VisitTimeDate) FROM CustomerVisits
WHERE VisitTimeDate >= (curr_date - 30) AND
VisitTimeDate <= (curr_date)
GROUP BY CustomerID;
This gives me the CustomerID and VisitTimeDate of all the records that
I want, but not the ItemPurchased field from those records. I cannot,
for the life of me, figure out how to modify the above query to return
only (or at least including) the ItemPurchased field. Any suggestions?
describe. I'm using Access 2000 on WinXP.
I have a single table (CustomerVisits) that has CustomerID,
VisitTimeDate, and ItemPurchased as fields.
I want a query that returns the latest item that each Customer
purchased in the last 30 days. i.e., if I had 20,000 unique customers
in the last 30 days, I want what each of of them purchased on their
latest visit.
I came up with a query:
SELECT tbl1.ItemPurchased FROM CustomerVisits As tbl1
WHERE VisitTimeDate = (
SELECT Max(tbl2.VisitTimeDate) FROM CustomerVisits As tbl2
WHERE tbl2.CustomerID = tbl1.CustomerID AND
tbl2.VisitTimeDate >= curr_date - 30 AND
tbl2.VisitTimeDate <= curr_date
);
This works fine when there are only a few thousand records in the
table, but the more records I have, the longer it takes. With 1.5
million records, the time it takes to retrieve the results is
unacceptable.
I've indexed CustomerID and VisitTimeDate, but still too long.
I found the following query that is plenty fast even with 1.5M records:
SELECT CustomerID, Max(VisitTimeDate) FROM CustomerVisits
WHERE VisitTimeDate >= (curr_date - 30) AND
VisitTimeDate <= (curr_date)
GROUP BY CustomerID;
This gives me the CustomerID and VisitTimeDate of all the records that
I want, but not the ItemPurchased field from those records. I cannot,
for the life of me, figure out how to modify the above query to return
only (or at least including) the ItemPurchased field. Any suggestions?