SQL – customers who have recently traded

[AndrewMozley]

Working at a very humble level with SQL, and would like to extract a cursor of customers who have traded in the last five years, showing the account name &c and the date and value of their last invoice. Would then export this to Excel

Giving names to these tables, Cust has fields including Account, Name, CustEmail, Invs has fields including Account, Invdate and InvValue.

Grateful for guidance on the suitable SELECT - SQL command to use within a VFP program.

Thanks.

 

[JRB-Bldr]

Keep in mind that when you do a SQL Query of an 'external' database, you need to use the SQL Syntax of THAT database, not VFP's.

So for M$ SQL Server you need to study up on T-SQL syntax.
Its not very different from VFP SQL syntax, but there are indeed some differences.
In fact in most cases you can build the query syntax as though it were in VFP, test it, and then modify just those parts of it that need changing.

If you question your query string, go into the M$ SQL environment and test it there first to ensure that it is correct.

After that you can use it in a SQLExecute() command within VFP after having established your SQLConnect().

Good Luck,
JRB-Bldr

 

[AndrewMozley]

Thanks JRB. However I don’t believe that I will need to use SQLConnect(). The database is a standard VFP database, located in a subfolder of the current folder.

I believe that I should be able to use standard VFP syntax - something like

SELECT Cust.Account, Cust.name, Cust.email, Invs.date . . FROM Cust, . . WHERE . . .

I am really looking for guidance on the clauses to include in the SELECT statement to extract one record for each customer who has traded in the last five years.

I will then export the contents of the resultant cursor to Excel.

Thanks again.

 

[Olaf Doschke]

It's implossible to help you without knowing the data, but you surely will nbeed to JOIN ORDERS filtered to orders of the last 5 yeras to extract the customers who made them, unless custoemrs table has a lastorderdate field, which would be redundant data against the rules, but simplyfy your task.

In northwind you'd start in Orders with the Orderdate column.

SELECT DISTINCT CUSTOMERS.* FROM CUSTOMERS INNER JOIN ORDERS ON CUSTOMERS.CUSTOMERID = ORDERS.CUSTOMERID AND ORDERDATE BETWEEN GOMONTH(DATE(),-5*12) AND DATE()

or even simpler as the upper limit really is useless;

SELECT DISTINCT CUSTOMERS.* FROM CUSTOMERS INNER JOIN ORDERS ON CUSTOMERS.CUSTOMERID = ORDERS.CUSTOMERID AND ORDERDATE >= GOMONTH(DATE(),-5*12)

That gives an empty result, as Northwind orders are all older, but you get the point.

Bye, Olaf.

 

[AndrewMozley]

Thank you Olaf, for your helpful reply. You have achieved the impossible! Have edited the statement to use the fields defined in the first email, and it worked first time!

SELECT DISTINCT Account, Name FROM Cust INNER JOIN Invs ON Cust.Account = Invs.Account AND InvDate BETWEEN . . .

May I ask for one more piece of help?

The above enquiry gives one record per relevant customer. Is there a way of including another column in the output which is the latest Invdate from the Invs table. I have experimented with saying SELECT DISTINCT Account, Name, MAX(Invdate) . . . but that runs into the sand; maybe I need to use a GROUP BY clause.

 

[Olaf Doschke]

To get some MAX value, you have to go for GROUP BY instead of DISTINCT, actually DISTINCT is just a shortcut, if you only want the single row without having to write out the group by columns, which can be tedious with many columns and impossible without nknowing the field names.

So you need

SELECT Account, Name, Max(Invdate) as LastInvdate FROM Cust INNER JOIN Invs ON Cust.Account = Invs.Account AND InvDate BETWEEN ... GROUP BY Account, Name

Bye, Olaf.

 

[Olaf Doschke]

Notice you only get the lastest Invdate within the queried range of records (BETWEEN), if your upper DATE would be in the past, you might miss a later order, so the Invdate>=... would fit better to not limit the Invs and get the real last Invdate, even though there should be no Invdate>Date() anyway.

Bye, Olaf.

 

[Koen Piller]

Andrew,

As Olaf pointed out it is rather difficult to give a good advise without knowing how your data is structured.
Find below an example in which I have introduced an ID in both tables which will give a result as per your request: only those who have traded in the last five years. The last five years, does this mean you want to see all entries in years 2012 and later, or does this mean you want to see entries made in month's younger than 60 months ago? I presume the first in which case:

CREATE CURSOR curCust (ID i AUTOINC, Account c (10), Name c(10), custEmail c(50))
INSERT into curCust(Account,name,custEmail) VALUES ("Aaaa", "Mr. One", "One@email.com")
INSERT INTO curCust(Account,name,custEmail) VALUES ("Aaab", "Mr. Two", "Two@email.com")
INSERT into curCust(Account,name,custEmail) VALUES ("Aaac", "Mr. Six", "Six@email.com")
INSERT INTO curCust(Account,name,custEmail) VALUES ("Aaad", "Mr. Three", "Three@email.com")
INSERT into curCust(Account,name,custEmail) VALUES ("Baaa", "Mr. Four", "Four@email.com")
INSERT INTO curCust(Account,name,custEmail) VALUES ("Baab", "Mr. Five", "Five@email.com")

CREATE CURSOR curInv (ID i autoinc, iCust i, Account c(10), invdate d, invvalue n(10,4))
INSERT INTO curInv( iCust, account, invdate,invvalue) VALUES (4,"Aaad",{^2017-08-12},2500.00)
INSERT INTO curInv( iCust, account, invdate,invvalue) VALUES (1,"Aaaa",{^2013-10-01},500.00)
INSERT INTO curInv( iCust, account, invdate,invvalue) VALUES (2,"Aaab",{^2015-07-16},2800.00)
INSERT INTO curInv( iCust, account, invdate,invvalue) VALUES (6,"Baab",{^2014-08-31},800.00)
INSERT INTO curInv( iCust, account, invdate,invvalue) VALUES (3,"Aaac",{^2011-02-15},9500.00)
INSERT INTO curInv( iCust, account, invdate,invvalue) VALUES (5,"Baaa",{^2017-03-09},1788.00)
*BROWSE normal

SELECT curCust.name, curCust.custEmail, curInv.invdate, curInv.invvalue ;
FROM curCust ;
INNER JOIN curInv ON curCust.ID = curInv.iCust ;
WHERE ((YEAR(DATE()) - YEAR(curInv.invdate))<5) ;
INTO CURSOR curResult Nofilter

For export to Excel I would advise you to use one of Vilhelm's classes enabling you to export in one go to Excel.xlsx (2013 and later) file type.

Regards,

Koen

 

[JRB-Bldr]

Is there a way of including another column in the output which is the latest Invdate from the Invs table.

If Olaf's original query gave you most of what you wanted, but you still needed the additional field of LastInvDt added, you could always do it with 2 sequential queries.

The first query would be Olaf's latest query suggestion to get the Max(Invdate) as LastInvdate and write its result into a Cursor.

Then do Olaf's first query suggestion (where he achieved the impossible!), but add an additional JOIN of the MAX(InvDate) cursor and include its LastInvdate field in the query syntax.

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

I'm pretty sure this will do the job:

SELECT Account, Name, Max(Invdate) as LastInvdate FROM Cust INNER JOIN Invs ON Cust.Account = Invs.Account AND InvDate>=GOMONTH(DATE(),-5*12) GROUP BY Account, Name

It just wasn't possible to know the field names before Andrew posted his adjusted query. That's all.

Going back to northwind:

SELECT DISTINCT CUSTOMERS.CUSTOMERID, CUSTOMERS.COMPANYNAME, MAX(ORDERDATE) as LastOdrder FROM CUSTOMERS INNER JOIN ORDERS ON CUSTOMERS.CUSTOMERID = ORDERS.CUSTOMERID AND ORDERDATE >= GOMONTH(DATE(),-20*12) GROUP BY CUSTOMERS.CUSTOMERID, COMPANYNAME

Bye, Olaf.