INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

SQL – customers who have recently traded

SQL – customers who have recently traded

(OP)
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.

RE: SQL – customers who have recently traded

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

RE: SQL – customers who have recently traded

(OP)
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

CODE -->

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.

RE: SQL – customers who have recently traded

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.

CODE

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;

CODE

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.

RE: SQL – customers who have recently traded

(OP)
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.



RE: SQL – customers who have recently traded

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

CODE

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.

RE: SQL – customers who have recently traded

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.

RE: SQL – customers who have recently traded

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:

CODE --> vfp

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

RE: SQL – customers who have recently traded

Quote (AndrewMozley)

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

RE: SQL – customers who have recently traded

I'm pretty sure this will do the job:

CODE

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:

CODE

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close