Temporal query (all rows starting at the closest date to Jan 1st 08) 3

[mickeyziggyk]

Hello All

I work with a HR db that has no end dates built into its design. I only have effective dates that represent the start of a change to an employee's profile.

I want to see all records/changes about every employee from the date Jan 1st 2008.

But some employees have had no changes to their profile since 2007, which means that if I do a simple select * between 01/01/2008 and now(), this would miss that particular population.

Therefore I want to know everything between the first update closest to Jan 1st 2008 to the current date.

My SQL has got as far as :

SELECT *
FROM HR
WHERE
[eff date] between

(select max([eff date]) from HR
where[eff date] <#01/01/2008#)
and now();

But, I know that this is not the whole picture and that I think I should query the relation on itself. I simply do not know how to grab the closest record to Jan 1st 2008 and then all subsequent records after that.

Each employee is uniquely identified by an employee number(empl_id).

I am baffled, if someone can help, I will gladly listen and learn.

Thanks

 

[lespaul]

Find all the employees and their max effective date:

SELECT Empl_ID, Max([eff date]) As LastDate FROM HR GROUP BY Empl_ID

use that query as a "temp table" and join it back into the HR table so you can get all the information you need:

SELECT * FROM HR H 
INNER JOIN [b](SELECT Empl_ID, Max([eff date]) As LastDate FROM HR GROUP BY Empl_ID)[/b] A ON H.Empl_ID = A.Empl_ID AND H.[Eff Date] = A.LastDate

HTH, post back with any other issues!

Leslie

Have you met Hardy Heron?

 

[JBinQLD]

I dont think Leslies solution will work as it will only return the latest entry per person whereas OP wanted ALL transaction from Jan 08, not just the most recent. I broke it into these steps:

QryAllEntriesThisYear

SELECT HR.empl_ID, HR.[eff date]
FROM HR
WHERE (((HR.[eff date])>=#1/1/2008#));

QryMostRecentEntry

SELECT HR.empl_ID, Max(HR.[eff date]) AS [MaxOfeff date]
FROM HR
GROUP BY HR.empl_ID;
[code]

Now we want just those entries that only have a Pre-Jan 08 entry:

QryEntriesFromLastYear
[code]
SELECT QryMostRecentEntry.empl_ID, QryMostRecentEntry.[MaxOfeff date]
FROM QryMostRecentEntry LEFT JOIN QryAllEntriesThisYear ON QryMostRecentEntry.empl_ID = QryAllEntriesThisYear.empl_ID
WHERE (((QryAllEntriesThisYear.empl_ID) Is Null));

Now we union those results with all the comments we have in QryAllEntriesThisYear giving final SQL:

SELECT QryAllEntriesThisYear.*
FROM QryAllEntriesThisYear

UNION SELECT QryEntriesFromLastYear.*
FROM QryEntriesFromLastYear;

Hope this helps,

JB

 

[PHV]

What about something like this ?
SELECT A.*
FROM HR A
WHERE [eff date] BETWEEN
(Select Max([eff date]) From HR Where [eff date]<=#2008-01-01# And [emplyee ID]=A.[emplyee ID])
And Date();

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mickeyziggyk]

Thank you all for your time and suggestions.

I came up with a slightly different solution as there are more conditions than I mentioned (thanks PH as otherwise your solution is very concise)

What I appreciated most was the manner in which you approached the problem and broke it down into chunks.

In the end I grabbed all max records before Jan 2008 and did a union with all records post Jan 2008.

Mickeyziggyk