How to report the date a customer exceeds a purchase threshold

[tim.burleson]

Hello,

I am trying to build a query that will tell me the date a customer
crosses a predetermined threshold. I have an invoice table that
carries the invoice date and the amount invoiced among other things.
We would like to reward customers that do x amount of business with
us. How can I query a the invoice table to tell me what date each
customer has accumulated x amount of business.

For instance. I want to know when a customer goes over $10,000.00 in
business.

I am sorry if I am not using the right terms but hopefully I have come
close to expressing my question.

Your help is appreciated.

Thanks, Tim

 

[John Spencer]

Perhaps by using a correlated subquery in the where clause. This will be
slow.
Assumption: CustomerID is a number field

SELECT CustomerID, InvoiceDate
FROM InvoiceTable as Outer
WHERE InvoiceDate IN (
SELECT TOP 1 InvoiceDate
FROM InvoiceTable as Inner
WHERE DSUM("InvoiceAmount","InvoiceTable","CustomerID=" &
Outer.CustomerID) >= 10000
ORDER BY InvoiceDate ASC)

If that works but is too slow, post back with your table and field names and
I will try to suggest a faster alternative.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[tim.burleson]

Perhaps by using a correlated subquery in the where clause. This will be
slow.
Assumption: CustomerID is a number field

SELECT CustomerID, InvoiceDate
FROM InvoiceTable as Outer
WHERE InvoiceDate IN (
SELECT TOP 1 InvoiceDate
FROM InvoiceTable as Inner
WHERE DSUM("InvoiceAmount","InvoiceTable","CustomerID=" &
Outer.CustomerID) >= 10000
ORDER BY InvoiceDate ASC)

If that works but is too slow, post back with your table and field names and
I will try to suggest a faster alternative.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Ok, I feel like an idiot...I am using SQL Server not Access...wrong
area...my bad.

The table name is tbInvoice.

InvoiceDate, Amount, GUIDCustomer are the fields

GUIDCustomer is used to pull customer data from tbCustomer

The outcome of the query should be
CustID (from Customer), InvoiceDate that pushed over the 10k mark

Any help with SQL Server or should I post over there?

Thanks, Tim

 

[John Spencer]

The same technique should work with SQL server, except you cannot use DSUM
and would need to use a subquery.

It would probably be best to post over there. I think you might end up with
something that looks like the following.

SELECT GuidCustomer, InvoiceDate
FROM tblInvoice as T1
WHERE InvoiceDate IN
(SELECT TOP 1 InvoiceDate
FROM tblInvoice as T2
WHERE (SELECT SUM(Amount)
FROM tblInvoice as T3
WHERE T3.GuidCustomer = T1.GuidCustomer ) > 10000
ORDER BY InvoiceDate)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..