Most Recent Date and Second-Most Recent Date SQL Query

[cleokep]

I am trying to query the Most Recent Date and the Second Most Recent Date from one linked table (ODBC connection) named PDB_DAILY_RDG.
This linked table is massive, with a row of data for each WELL_NUM and every DAILY_RDG_DATE, and I want the resulting table to have only 1 row for each WELL_NUM, and 3 columns total: WELL_NUM, MOST_RECENT_DATE, and SECOND_MOST_RECENT_DATE.

Linked table looks like this:
WELL_NUM DAILY_RDG_DATE
00008 3/15/2009
00008 3/16/2009
00008 3/17/2009
00009 3/15/2009
00009 3/16/2009

And resulting table should look like this:
WELL_NUM MOST_RECENT_DATE SECOND_MOST_RECENT_DATE
00008 3/17/2009 3/16/2009
00009 3/16/2009 3/15/2009

I found SQL code which I've modified that's supposed to do this, but am getting a Syntax error when I try to run it:


SELECT SQ_MostRecent.WELL_NUM,
SQ_MostRecent.Temp1 AS MostRecentDate,
SQ_TwoMostRecent.Temp2 AS SecondMostRecentDate

FROM
(
SELECT T1.WELL_NUM, Max(T1.DAILY_RDG_DATE) As Temp1
FROM PDB_DAILY_RDG AS T1
GROUP BY T1.WELL_NUM
) AS SQ_MostRecent

LEFT JOIN

(
SELECT T2.WELL_NUM, T2.DAILY_RDG_DATE AS Temp2
FROM PDB_DAILY_RDG AS T2
WHERE T2.DAILY_RDG_DATE IN
(
SELECT TOP 2 T3.DAILY_RDG_DATE
FROM PDB_DAILY_RDG AS T3
WHERE T3.WELL_NUM = T2.WELL_NUM
ORDER BY T3.DAILY_RDG_DATE DESC
)
) AS SQ_TwoMostRecent

ON (SQ_MostRecent.WELL_NUM = SQ_TwoMostRecent.WELL_NUM)
AND (SQ_MostRecent.Temp1 > SQ_TwoMostRecent.Temp2)


It doesn't like the "ON (SQ_MostRecent.WELL_NUM = SQ_TwoMostRecent.WELL_NUM)" part...any ideas?

 

[Micron]

Taking a stab here, but I don't think you need more than one table. I think your first, most recent date would be calculated field using the MAX operator with criteria being < or <= some date, and the second most recent date would be another calculated field using the same table, also using the MAX function. However, the criteria would be a nested sql statement kind of like the one you have. This would pull in the MAX date that is less than the first date. I think you are close, but I gotta go, so see if that helps. If not, I'll check back tomorrow.

 

[cleokep]

I think I see what you're saying with the one table idea. I've been playing around with the code but can't get it to run. It might be beyond my abilities, so might have to go with Query 1 to sort descending, and Query 2 to Find Top 2 Dates. But that was giving an error of "Cannot open database ". It may not be a database that your application recognizes, or the file may be corrupt". Could be due to size of the linked ODBC table?
Will try more tomorrow! Thanks.

 

[Micron]

Could be due to size of the linked ODBC table?

I've used linked ODBC tables many times and have not seen this, so I'd say no. A query or direct opening of the table only loads what the query asks for, or if opened directly, only retrieves part of the records until you scroll down beyond the last loaded record. It's size should not be an issue unless perhaps it is not linked, but is a full copy. See if you can open the table directly or run a simple select query against it to test for corruption or encryption of the table or its data.

It doesn't like the "ON (SQ_MostRecent.WELL_NUM = SQ_TwoMostRecent.WELL_NUM)" part

If you concluded this because Access highlighted that portion after a failed execution, be advised that Access often highlights a portion of sql nearest the offending part and there is nothing wrong with what's highlighted. After closer examination, what you have just looks odd in a few places (I've never seen '>' or subqueries as part of a join statement).

I've done what you're trying to do where I needed the next earliest date from another table. Stock code was common to both so an equal join was between both tables on that field. In your case, a self join might be required in the end, but I created this 'air' sql based on my project and am attempting to get the data from one instance of your table. Maybe I don't have your table/field names right but I'll take a stab at it shortly.


This excellent site might help you better grasp the idea of subqueries and self-joins.
Microsoft Access help, tutorials, examples

 

[Micron]

Harder than it looks at first, especially when I can't test it without building a test table. If this doesn't work, let me know.
SELECT PDB_DAILY_RDG.WELL_NUM, PDB_DAILY_RDG.DAILY_RDG_DATE AS MOST_RECENT_DATE FROM PDB_DAILY_RDG, (SELECT MAX( PDB_DAILY_RDG.DAILY_RDG_DATE) AS SECOND_MOST_RECENT_DATE FROM PDB_DAILY_RDG AS T WHERE T.WELL_NUM=PDB_DAILY_RDG.WELL_NUM AND T.SECOND_MOST_RECENT_DATE < PDB_DAILY_RDG.DAILY_RDG_DATE) AS SECOND_MOST_RECENT_DATE;

 

[Micron]

I spent over 2 hours on this, often close then worse then close again but no success UNTIL...

SELECT PDB_DAILY_RDG.WELL_NUM, PDB_DAILY_RDG.DAILY_RDG_DATE AS MOST_RECENT_DATE, tbl2.DAILY_RDG_DATE AS NEXT_MOST_RECENT_DATE
FROM PDB_DAILY_RDG INNER JOIN PDB_DAILY_RDG AS tbl2 ON PDB_DAILY_RDG.WELL_NUM = tbl2.WELL_NUM
WHERE (((PDB_DAILY_RDG.DAILY_RDG_DATE)=(SELECT MAX(DAILY_RDG_DATE) FROM PDB_DAILY_RDG AS tbl2 WHERE tbl2.WELL_NUM=PDB_DAILY_RDG.WELL_NUM)) AND ((tbl2.DAILY_RDG_DATE)=(SELECT MAX(DAILY_RDG_DATE) FROM PDB_DAILY_RDG AS tbl2 WHERE tbl2.WELL_NUM=PDB_DAILY_RDG.WELL_NUM AND tbl2.DAILY_RDG_DATE<pdb_daily_rdg.daily_rdg_date)));

WELL_NUM	MOST_RECENT_DATE	NEXT_MOST_RECENT_DATE
00008	3/17/2009	3/16/2009
00009	3/16/2009	3/15/2009

<tbody>
</tbody>

Hope the table of the results comes through. I ended up with not one, but TWO subqueries and a self-join on the table. Also hope you like it. I'm going to bed as it's now past 1:00 AM. Can't say I'm not determined...</pdb_daily_rdg.daily_rdg_date)));

 

[cleokep]

O...M...G....I don't know how to thank you, that's amazing! It works perfectly, can't believe you put that much effort into my problem. Thank you thank you thank you! Now get some rest

 

[Micron]

Your're very welcome. The way I see it, I also benefit in that I learn something that I can file away and maybe I'll need it for myself someday. Alas, I fear those days are numbers since I've retired and don't expect to be called back to do this kind of work many more times than the 2 it has already been. In that case, there is always the reason that I have had help from one forum or another so it's nice to be able to 'pay back'. Good luck with your project.