[/src]
select .. where date_out = 
(select  min(date_out) from prikaz_tmp)
[src]

[/src]
ALTER proc sp_create_prikaz_tmp AS

Delete from prikaz_tmp
declare @pers char ( 10 ),
	@date_in datetime,
	@date_out datetime,
	@d_in_prev datetime,
	@d_out_prev datetime,
	@d_out_prik datetime,
	@otd_prev char( 10 ),
	@otd char( 10 ),
	@first tinyint,
	@u_size dec( 14 , 2 ),
	@fl_uch char( 1 ),
	@fl_tmp char( 1 ),
	@cat char( 10 ),
	@tmp smallint,
	@date_2050 datetime
set @cat = '11'  --Категория ученики для ГОТЭКА
 
set @tmp =  61   -- Колличество дней врем работы
 
set @date_2050 = '2050-01-01'
declare cur cursor for 
select  pers_id from const
open cur
fetch cur into @pers
while @@fetch_status =  0  
begin
  set @first =  1 
  declare cur_jur cursor for 
  select distinct when_, when_ended, n_otd from y_jur_dot where pers_id = @pers and when_ended is not null and n_otd is not null
    order by when_
  open cur_jur
  fetch cur_jur into @date_in, @date_out, @otd
  while @@fetch_status =  0  
  begin
    if @first =  1   -- первая запись в журнале
 
    begin
      insert into prikaz_tmp (tip, pers_id, date_in, date_out, n_otd, fl_priem, unit_size, dt)
        values ('001', @pers, @date_in, @date_2050, @otd, '1',  1 , @date_in)
    end else  -- НЕ первая запись в журнале
 
    begin
      select @d_out_prik = max(date_out) from prikaz where tip = '002' and (date_out between @d_in_prev and @d_out_prev) and pers_id = @pers
      if exists (select * from prikaz where tip = '001' and date_in = @date_in and pers_id = @pers )
        OR @d_out_prik is not null
        OR (@otd <> @otd_prev and (@otd in ('0001','d2','r83','r84') OR @otd_prev in ('0001','d2','r83','r84')))
      begin
        insert into prikaz_tmp (tip, pers_id, date_in, date_out, n_otd, fl_priem, unit_size, dt)
          values ('001', @pers, @date_in, @date_2050, @otd, '1',  1 , @date_in)
        insert into prikaz_tmp (tip, pers_id, date_in, date_out, n_otd, fl_priem, unit_size, dt)
          values ('002', @pers, @date_2050, isnull(@d_out_prik, isnull(@d_out_prev,@date_2050)), @otd_prev, '1', - 1 , isnull(@d_out_prik, @d_out_prev))
      end
      else
      begin
        if @otd <> @otd_prev
        begin
        insert into prikaz_tmp (tip, pers_id, date_in, date_out, n_otd, fl_priem, unit_size, dt)
          values ('001', @pers, @date_in, @date_2050, @otd, '0',  1 , @date_in)
        insert into prikaz_tmp (tip, pers_id, date_in, date_out, n_otd, fl_priem, unit_size, dt)
          values ('002', @pers, @date_2050,isnull(@d_out_prev,@date_2050), @otd_prev, '0', - 1 , @d_out_prev)
        end
      end
    end
    set @first =  0 
    select @d_in_prev = @date_in, @d_out_prev = @date_out, @otd_prev = @otd
    fetch cur_jur into @date_in, @date_out, @otd
  end
  close cur_jur
  deallocate cur_jur
  insert into prikaz_tmp (tip, pers_id, date_in, date_out, n_otd, fl_priem, unit_size, dt)
    values ('002', @pers, @date_2050, isnull(@d_out_prev,@date_2050), @otd_prev, '0', - 1 , @d_out_prev)
  fetch cur into @pers
end
close cur
deallocate cur

delete from prikaz_tmp where year(date_in) =  2050  and year(date_out) =  2050 

 --update prikaz_tmp set unit_size = 1
 

 -- Не целая ставка без  внутренних совмещений ПРИЁМ
 
update prikaz_tmp set unit_size = D.unit_size
FROM dol_unit D, prikaz_tmp P, y_jur_dunit Y
WHERE Y.dunit_kod = D.kod AND P.pers_id = Y.pers_id AND D.kod_podr = P.n_otd 
AND D.unit_size<> 1  AND (Y.date_begin between P.date_in AND P.date_out)
AND isnull(D.flag_multi,'0') <>  1  AND P.tip = '001'

 -- Не целая ставка без  внутренних совмещений УВОЛЬНЕНИЕ
 
declare cur cursor for
  select pers_id, date_in, n_otd, unit_size from prikaz_tmp 
    where fl_priem <>  2  AND unit_size <>  1  AND tip = '001'
 --order by pers_id, date_
 
open cur
fetch cur into @pers, @date_in, @otd, @u_size
while @@fetch_status =  0 
begin
  update prikaz_tmp set unit_size = @u_size
  where pers_id = @pers AND n_otd = @otd and date_out =
  (select  min(date_out)
    from prikaz_tmp
    where fl_priem <>  2  AND tip = '002' and date_out >= @date_in AND pers_id =@pers 
    AND n_otd = @otd
    group by pers_id, n_otd, unit_size)
  fetch cur into @pers, @date_in, @otd, @u_size
end
close cur
deallocate cur

 -- принять по внутреннему совмешению
 
insert into prikaz_tmp 
  SELECT '001', M.pers_id, M.date_begin, @date_2050, D.kod_podr, '2', D.unit_size ,  M.date_begin, '0', '0'
  FROM dol_unit D, dunit_multiwork M 
  WHERE D.kod = M.dolunit  --AND M.date_end is null
 
 -- Уволить по внутреннему совмешению
 
insert into prikaz_tmp
  SELECT '002', M.pers_id, @date_2050, M.date_end, D.kod_podr, '2',  0 . 0  - D.unit_size ,  M.date_end, '0', '0'
  FROM dol_unit D, dunit_multiwork M 
  WHERE D.kod = M.dolunit AND M.date_end is not null

 --update prikaz_tmp set unit_size = 0 - ABS(unit_size) where tip = '002'
 
update prikaz_tmp set dt = date_in where year(date_out) =  2050 
update prikaz_tmp set dt = date_out where year(date_in) =  2050 

GO
[src]