SQL - Comparing a recent record with another, but a least 100 days ago

Inside of a reporting program, I am querying a database that could contain a current and 0 to 6 historical records each with a date/timestamp.   No problem in getting the current record, now I want the most recent historical record as long as it is at least 100 days older than the current record.    A record would contain, user id,  sequential record number, timestamp, name, weight.  Can I do that with a query or am I going to need to collect all the historical records and test them inside my code?  If I can do that with a query, what would that look like?
greglwhiteAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shaun KlineLead Software EngineerCommented:
You could use a query similar to this:

select top 1 *
from table
where date < dateadd(day, -100, dateofconcern)
order by date desc

Open in new window


Where table and date are your respective table and date field, and dateofconcern is the date from your current record.
Julian HansenCommented:
When you say you want the most recent historical record - do you mean for a given user or do you want a list of the most historical records for all users?

For a single user this might work
SELECT * FROM `yourtable` a 
LEFT JOIN (
    SELECT MAX(`logdate`) AS `current`, `user_id` FROM `yourtable` GROUP BY `user_id`
) b ON a.user_id = b.user_id
WHERE a.`logdate` < b.`current` AND DATEDIFF(`current`, a.`logdate`) < 100 
ORDER BY DATEDIFF(`current`, a.`logdate`) desc
LIMIT 1;

Open in new window


If you want a result set containing all user records in one go that will require some thought.
This will potentially return more than two records per user_id

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
greglwhiteAuthor Commented:
for a given user, if John Doe has a record for today, I want the most recent record of his that is at least 100 days old.  

Will your response work on most SQL or is it specific to one flavor?   I have to support MS SQL, Postrgress and mySQL.
Julian HansenCommented:
No - the limit function is not supported on MS SQL - you would need to use TOP for that
OR
Use your DB layer find_first (if there is one) to return the first record in the data set.
greglwhiteAuthor Commented:
this got it done!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.