Query to find the first date a customer purcahsed an item

  • Thread starter Thread starter yuri greene
  • Start date Start date
Y

yuri greene

I am trying to write a query to find the first time a
customer purchased an item. I have tried to use a subquery
but am unable to get it to work correctly.
Here is the SQL of the fields if someone could help me
with this.

SELECT SH_ORDDT.CO_NUMBER, SH_ORDDT.CUST_ID,
SH_ORDDT.ITEM, SH_ORDDT.LN_CREATE_DATE
FROM SH_ORDDT
GROUP BY SH_ORDDT.CO_NUMBER, SH_ORDDT.CUST_ID,
SH_ORDDT.ITEM, SH_ORDDT.LN_CREATE_DATE
HAVING (((SH_ORDDT.ITEM) Like "*-*-F*"));

What I need to have the first or min LN_CREATE_DATE for
the item for each customer. I do however need the
co_number field to link to another query and this is where
it is giving me trouble. I can get it to work without
this field.
 
Try:
SELECT SH_ORDDT.CO_NUMBER, SH_ORDDT.CUST_ID,
SH_ORDDT.ITEM, min(SH_ORDDT.LN_CREATE_DATE)
FROM SH_ORDDT
GROUP BY SH_ORDDT.CO_NUMBER, SH_ORDDT.CUST_ID,
SH_ORDDT.ITEM, SH_ORDDT.LN_CREATE_DATE
HAVING (((SH_ORDDT.ITEM) Like "*-*-F*"));
Click to expand...
 
This comes back to the same amount of records and brings
up multiple dates for the same item and customer. Now this
will work if we take the CO_Number out. This is a number
that we issue, and is a date, like 031504-001 and they go
up for the number of orders for the day like 031504-002
and so on.
 
UNTESTED and possibly slow.

SELECT A.CO_NUMBER,
A.CUST_ID,
A.ITEM,
A.LN_CREATE_DATE
FROM SH_ORDDT AS A
WHERE A.LN_CREATE_DATE =
(SELECT Min(B.LN_CREATE_DATE)
FROM SH_ORDDT AS B
WHERE A.Co_Number = B.Co_Number
AND A.Cust_Id = B.Cust_Id
AND A.Item = B.Item
AND A.Item Like "*-*-F*")
AND A.Item Like "*-*-F*"
 
Thanks, I was able to get this to work by taking Co_Number
out of the where statement. For some reason it still gave
the max amount of records but taking it out gave all the
first order dates. Thanks for all your help.
 
Back
Top