SELECT vpr_wk_type.Work_type,
		c.Auto_Card, c.Name As Last_Name_Ru, c.Name_i As First_Name_Ru,
		c.Name_o As Middle_Name_Ru, c.NetName As ShortName, c.EMail, c.First_Name,
		c.Last_Name, c.Second_Name As Middle_Name, c.Phone_1 As Phone_Work, c.Phone_2 As Phone_Mob,
		people.in_date As Start_Date, people.out_date As End_Date, people.Num_Tab,
		a.Name_appoint As Job_Title_Ru, a.job As Job_Title, 
		(CASE WHEN isnull(prl.prd,0)=0 THEN '0' ELSE convert(varchar,prl.prd,104) END) As Maternity,
		setup.Short_name As Company_Ru, setup.Addr_city As City_Ru,
		dbo.UDF_pr_tree_name(pr_current.Code_struct_name, ' / ') As Department_Ru, Name_eng As Company
from card c
left join (select  max(out_date)as od, auto_card from people group by auto_card )p on c.Auto_Card = p.Auto_Card 
left join (select  auto_card ,   min(sovm)as sv from people group by  auto_card order by sv)ps on ps.Auto_Card= p.Auto_Card 
left join people on p.Auto_Card = people.Auto_Card and p.od=people.out_date
left join pr_current on pr_current.pid = people.pid
left join Appointments a on a.Code_appoint = pr_current.Code_Appoint
left join (select pid, max(fromd)as prd from pr_leave where pr_leave.Code_Leave = 2 and getdate() between fromd and tod group by pid )prl ON 
prl.pid = people.pId

left join vpr_wk_type on pr_current.Work_Code= vpr_wk_type.work_code
left join setup on setup.id_firm = pr_current.id_Firm
WHERE pr_current.Flag_last = '*' 

select  auto_card ,   min(sovm)as sv from people group by  auto_card order by sv

select  min(auto_card) from people

left join (select top 1 max(out_date)as od, auto_card from people group by auto_card, out_date order by out_date )p on c.Auto_Card = p.Auto_Card

left join (select top 1 max(out_date)as od, auto_card from people group by auto_card, out_date order by out_date )p on c.Auto_Card = p.Auto_Card

outer apply(select top 1 max(p.out_date)as od, p.auto_card from people p where p.Auto_Card = c.Auto_Card group by p.auto_card, p.out_date order by p.out_date )p

left join (
	select max(out_date)as od, auto_card 
	from people group by auto_card
	)p on c.auto_card = p.auto_card

select max(out_date)as od, auto_card 
from people group by auto_card

select max(out_date)as od, auto_card 
from people group by auto_card

left join (select  max(out_date)as od, auto_card from people group by auto_card )p on c.Auto_Card = p.Auto_Card 
left join people on p.Auto_Card = people.Auto_Card and p.od=people.out_date

declare  @person table (id int, lastname varchar(100))
insert into @person 
select 1,'Иванов'
union all
select 2,'Петров'
union all
select 3,'Сидоров'

declare @pesrson_attr table (id int, date datetime)
insert into @pesrson_attr
select 1,'20120801'
union all
select 1,'20120101'
union all
select 2,'20120311'
union all
select 2,'20120311'


select p.*,(select top 1 date from @pesrson_attr pa where p.id=pa.id order by date desc)
from 
@person p