Joining an aggregate of a table to itself

I am trying to figure out how to do a simple aggregate of a table and then join it to itself to get the remaining fields. I have a few tables that list information on daily entries that includes job_id, location_id, desc, date, beginDate, endDate, manager. I want to list every entry in the table, but for each job_id and location_id combination, I'd like to get the very first entry for beginDate and the very latest entry for endDate for the group and then list all the remaining information within each record.

My first attempt to do this is:

Select M.job_id, M.location_id, M.desc, M.date, Y.beginDate, Y.endDate, M.manager
from myTable M
inner join

(select job_id, location_id, min(beginDate), max(endDate)

from myTable

group by job_id, location_id) as Y

on M.job_id = Y.job_id, M.location_id = Y.location_id

This seems inefficient and I'm not sure it's the best way to get what I want. Any suggestions?

Thanks!!!

I'm not completely sure what to aim at; it will help if you will give some sample data and the desired output. You might be looking for something like one of these:

declare @.myTable table
( rid integer,
job_id integer,
location_id integer,
[desc] varchar(10),
date datetime,
manager varchar(10)
)

insert into @.myTable
select 1, 1, 1, 'First Job', '4/20/7', 'Flintstone' union all
select 2, 1, 1, 'First Job', '4/21/7', 'Slate' union all
select 3, 1, 1, 'First Job', '4/22/7', 'Flintstone' union all
select 4, 2, 2, 'Future Job', '4/20/7', 'Jetson' union all
select 5, 2, 2, 'Future Job', '4/21/7', 'Spacely' union all
select 6, 2, 2, 'Future Job', '4/22/7', 'Spacely'

Select M.job_id,
M.location_id,
M.[desc],
M.date,
Y.beginDate,
Y.endDate,
M.manager
from @.myTable M
inner join
( select job_id,
location_id,
min(Date) as beginDate,
max(Date) as endDate
from @.myTable
group by job_id, location_id
) Y
on M.job_id = Y.job_id
and M.location_id = Y.location_id

/*
job_id location_id desc date beginDate endDate manager
- -- - -
1 1 First Job 2007-04-20 00:00:00.000 2007-04-20 00:00:00.000 2007-04-22 00:00:00.000 Flintstone
1 1 First Job 2007-04-21 00:00:00.000 2007-04-20 00:00:00.000 2007-04-22 00:00:00.000 Slate
1 1 First Job 2007-04-22 00:00:00.000 2007-04-20 00:00:00.000 2007-04-22 00:00:00.000 Flintstone
2 2 Future Job 2007-04-20 00:00:00.000 2007-04-20 00:00:00.000 2007-04-22 00:00:00.000 Jetson
2 2 Future Job 2007-04-21 00:00:00.000 2007-04-20 00:00:00.000 2007-04-22 00:00:00.000 Spacely
2 2 Future Job 2007-04-22 00:00:00.000 2007-04-20 00:00:00.000 2007-04-22 00:00:00.000 Spacely
*/

declare @.myTable2 table
( rid integer,
job_id integer,
location_id integer,
[desc] varchar(10),
date datetime,
manager varchar(10)
)

insert into @.myTable2
select 1, 1, 1, 'First Job', '4/20/7', 'Slate' union all
select 2, 1, 1, 'First Job', '4/21/7', 'Slate' union all
select 3, 1, 1, 'First Job', '4/22/7', 'Slate' union all
select 4, 2, 2, 'Future Job', '4/20/7', 'Spacely' union all
select 5, 2, 2, 'Future Job', '4/21/7', 'Spacely' union all
select 6, 2, 2, 'Future Job', '4/22/7', 'Spacely'
select job_id,
location_id,
[desc],
min(date) as beginDate,
max(date) as endDate,
Manager
from @.myTable2
group by job_id,
location_id,
[desc],
Manager
order by job_id,
location_id

/*
job_id location_id desc beginDate endDate manager
- -- - -
1 1 First Job 2007-04-20 00:00:00.000 2007-04-22 00:00:00.000 Slate
2 2 Future Job 2007-04-20 00:00:00.000 2007-04-22 00:00:00.000 Spacely
*/