Mix, Max for a group of records

[h20vrrq]

Hi, I am trying to create a view that has the start_date and end_date for each employee based on their location of work and I'm struggling to work it out.

The table is called location_history and the fields are
ID, (this is the unique number for each record in the table)
employee_visit_ID, (unique employee number)
office_ID, (ID of the office the employee is working)
start_date, (date employee started worting in the office)
end_date, (date employee stopped working in the office)
building_ID (ID of the building where the office is)

Employees often move from ofice to office within the same building, so there will be many records for each employee with different start and dates with the same building ID.

What I need to work out is the start_date & end_date for each employee at each building, so the view needs to show Building_ID, Start_Date, End_date and this needs to be grouped by Building_ID.

Any help would be greatly appreciated.

 

[IanWaterman]

select building_id, min(start_date) Start_date, max(end_date) end_date
from location_history
group by building_id

Ian

 

[SkipVought]

for each employee based on their location of work

Maybe...

select employee_visit_ID, building_id, min(start_date) Start_date, max(end_date) end_date
from location_history
group by employee_visit_ID, building_id

include the office id if more granularity is required.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[IanWaterman]

Well spotted Skip :-0

Never read exam papers properly either.

Ian