Records active ON or AFTER a specific date

[wrightyrx7]

Hi all,

I have a query from a table (to reduce the number of fields).

The list is all the positions people have performed, as person could have one record another could have 10 the number is unlimited.

Fields are:-
-employee ID
-start date
-position

I need to find any records that were active ON or AFTER 01/09/2014. This will be 1 record for most but some could have multiple.

I think it makes it more difficult that there is no END DATE.

Any help would be great.

Thanks in advance
Chris

 

[CJ_London]

the only problem with not having an end date is that the last date prior to 1/9/2014 will be the 'active' record - even if that person has left.

the code you want is as follows:

SELECT *
FROM myTable
WHERE StartDate=(SELECT Max(StartDate) FROM myTable as T WHERE EmployeeID=myTable.EmployeeID AND StartDate<#09/01/2014#)

Note the date structure dates described between #'s need to be i the american format of mm/dd/yyyy

 

[wrightyrx7]

Hi CJ,

Many thanks for you prompt reply.

I have got the code working, but is this only pulling out records that where effective before 01/09/2014?

I have gone through the data from the results and there is no records that are effective on or after 01/09/2014.

Sorry if i am misunderstanding.

Chris

 

[CJ_London]

Sorry, misread your question

in that case your query would be

SELECT *
FROM myTable
WHERE StartDate=(SELECT Max(StartDate) FROM myTable as T WHERE EmployeeID=myTable.EmployeeID AND StartDate<#09/01/2014#) OR StartDate>=#09/01/2014#

just a bit added to the end

 

[ConnorGiles]

Just adding here but without the need SQL - you could add this to your query :

 >= 01/09/2014

That would be placed in the criteria for StartDate inside the query

(Just thought I'd take a stab at it for you)

 

[wrightyrx7]

Thanks guys but i dont seem to be getting the data i need.

-Some people may have a position that started before 01/09/2014 and is still ongoing
-Some could have a position that started before 01/09/2014 AND a new position that started AFTER the 01/09/2014 meaning they will have two lines of data.

I hope this makes sense.

Chris

 

[CJ_London]

so my solution i post #4 should work

Suggest you post the SQL you are actually using