Clever Geek Handbook

Construct correct SQL string for ADO to get data from MAX Date

I am extracting data from a closed binary Excel file using ADO.
I have 2 sheets in this file and I want to get all data from Sheet1 where ID exists in Sheet2.
And I was able to do this:

sqlstr = "SELECT e.NOTES, u.SPO, e.ID, e.REF, e.PID "
sqlstr = sqlstr & ", e.DESCRIPTION, e.CreateDate "
sqlstr = sqlstr & "FROM [Sheet2$] u "
sqlstr = sqlstr & "INNER JOIN [Sheet1$] e "
sqlstr = sqlstr & "ON e.ID = u.ID;"

The problem occurs when a specific ID has been displayed 2 or more x in Sheet 1, but with different dates.
I need to get the ID with the highest data and so I will try the following:

sqlstr = "SELECT e.NOTES, u.SPO, e.ID, e.REF, e.PID  "
sqlstr = sqlstr & ", e.DESCRIPTION, e.CreateDate "
sqlstr = sqlstr & "FROM [Sheet2$] u "
sqlstr = sqlstr & "INNER JOIN (SELECT ID, REF, PID, DESCRIPTION, NOTES, MAX(CreateDate) "
sqlstr = sqlstr & "FROM [Sheet1$] GROUP BY ID, REF, PID, DESCRIPTION, NOTES) e "
sqlstr = sqlstr & "ON e.ID = u.ID;"

Unfortunately it doesn't work and gives me this error:

enter image description here

I tried to isolate my Sub Query like this:

sqlstr = "SELECT ID, REF, PID, DESCRIPTION, NOTES, MAX(CreateDate) "
sqlstr = sqlstr & "FROM [Sheet1$] GROUP BY ID, REF, PID, DESCRIPTION, NOTES;"

It works great. It returns all IDs with a maximum date.
My questions:

  • Why doesn't it work when I try to use it as a Sub Query.
  • Does ADO support Sub Query when extracting data from Excel files or databases?
  • Is there a way to get the result that I would like to use for a query only?

Any help is appreciated.

Clarification: Sub Query works if I run it with SQL. I tried to get it to work using ADO. This is the first time I am doing a Sub Query in SQL String to use when opening a Recordset. So I thought that maybe I was doing something wrong or doing something out of scope.

+3


source to share


2 answers


You may have overdone it.

Try the following:



(untested code)

SELECT e.NOTES, u.SPO, e.ID, e.REF, e.PID, e.DESCRIPTION, MAX(e.CreateDate) as CreateDate 
FROM [Sheet1$] e  
INNER JOIN [Sheet2$] u ON e.ID = u.ID 
GROUP BY e.ID, e.REF, e.PID, e.DESCRIPTION, e.NOTES, u.SPO
+2


source


I'd first joined Sheet1

to Sheet2

, and then join subquery, which will be only by the maximum date. Something like that:

SELECT e.NOTES, u.SPO, e.ID, e.REF, e.PID, e.DESCRIPTION, e.CreateDate
FROM [Sheet2$] u

INNER JOIN [Sheet1$] e
ON e.ID = u.ID

INNER JOIN (
    SELECT ID, MAX(CreateDate) AS MaxCD
    FROM [Sheet1$]
    GROUP BY ID
) AS MCD
ON e.ID = MCD.ID
AND e.CreateDate = MCD.MaxCD


Essentially, these are:

  • Combining all results from Sheet1

  • Rejoins Sheet1

    , but only with the highest date created.
+1


source






More articles:


All Articles