USE [DEV] GO /****** Object: StoredProcedure [dbo].[plr_StazPracyRok1] Script Date: 17.04.2019 11:15:50 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[plr_StazPracyRok1] @JK smallint, @Osoby tinyint, -- 0 wszyscy,1 pracownicy,2 pozostali @Rok smallint, @Listy varchar(50)=Null, @Jedn varchar(15)=Null, @Komorka varchar(10)=Null, @Sort tinyint=0, -- 0-nazwiska,1-jedn,2-komora,3-lista,4-data rocznicy @Typ tinyint=0, -- 0 - w zakladzie, 1-ogółem @BezWykszt tinyint=0, -- 1 nie uwzględniać wykształcenia @Lata varchar(100)='5,20,25,30,35,40,45', @Uz varchar(20)='' AS ---2019-04-17------------------------------------------------------------------------------------------------------------------------------------------------------------------ DECLARE @SQL varchar(4000), @SQL1 varchar(1000), @DataOd datetime, @DataDo datetime, @Dzien datetime, @UID uniqueidentifier, @i int, @xx varchar(20), @St tinyint, -- typ wylicznia stazu do urlopu @Tak tinyint ----------------------------------------------------------------------------------------- SET DATEFORMAT dmy IF ((SELECT OBJECT_ID('tempdb..#Osoby')) IS NOT NULL) DROP TABLE #Osoby IF ((SELECT OBJECT_ID('tempdb..#Staz2')) IS NOT NULL) DROP TABLE #Staz2 create table #Osoby ( UID uniqueidentifier, Tak int, DataZatr datetime, DataZw datetime, DataOd datetime, DataDo datetime, DataR datetime, LataR int, DataS datetime, StazL int, StazM int, StazD int, Staz int, Symbol varchar(15), lp int, Wsk tinyint, Jest tinyint, Il int, Lp1 int, StazLs int, StazMs int, StazDs int ) CREATE TABLE #Staz2 (ID int IDENTITY(1,1) NOT NULL, UID uniqueidentifier, DataOd datetime, DataDo datetime, Staz varchar(10), r int, m int, d int, Typ tinyint, ZP tinyint, -- 0-w zakł,1-poza ID1 int ) CREATE TABLE #Staz2a (ID int IDENTITY(1,1) NOT NULL, UID uniqueidentifier, DataOd datetime, DataDo datetime, Staz varchar(10), r int, m int, d int, Typ tinyint, ZP tinyint, -- 0-w zakł,1-poza ID1 int ) CREATE TABLE #Lata ( Rok Int ) -- lata stazu mijające w danym roku SET @i=PATINDEX('%,%',@Lata) while @i>0 begin SET @xx=Substring(@Lata,1,@i-1) SET @Lata=Substring(@Lata,@i+1,Len(@Lata)-@i) SET @i=CONVERT(int,@xx) INSERT INTO #Lata (Rok) Values (@i) SET @i=PATINDEX('%,%',@Lata) end if len(@Lata)>0 begin SET @xx=@Lata SET @i=CONVERT(int,@xx) INSERT INTO #Lata (Rok) Values (@i) end SET @SQL='INSERT INTO #Osoby (UID,Jest,Lp,Symbol,Tak,DataZatr,DataZw,Wsk) SELECT UID,0,' if @Sort=1 begin SET @SQL=@SQL+'1,Jedn' end else begin if @Sort=2 begin SET @SQL=@SQL+'1,Komorka' end else begin if @Sort=3 begin SET @SQL=@SQL+'IsNull(NrListy,-1),Symbol=CASE WHEN NrListy Is Null THEN '' '' ELSE convert(varchar(10),NrListy) END' end else begin SET @SQL=@SQL+'1,Symbol=''''' end end end SET @Tak=0 if len(IsNull(@Uz,''))=0 begin SET @SQL=@SQL+',1,DataZatrudnienia,DataZwolnienia,0 FROM tblPracownicy WHERE (JK='+convert(varchar(10),@JK)+')' end else begin SET @SQL=@SQL+',dbo.pl_PracTak(JK,'''+@Uz+''',Jedn,Komorka,NrListy),DataZatrudnienia,DataZwolnienia,0 FROM tblPracownicy o WHERE (JK='+convert(varchar(10),@JK)+')' end SET @SQL=@SQL+' AND (Year(DataZatrudnienia)<='+convert(varchar(4),@Rok)+') AND ((DataZwolnienia Is Null) OR (Year(DataZwolnienia)>='+convert(varchar(4),@Rok)+'))' if @Osoby=1 begin SET @SQL=@SQL+' AND (IsNull(Pracownik,0)=1)' end else begin if @Osoby=2 SET @SQL=@SQL+' AND (IsNull(Pracownik,0)=0)' end if len(IsNull(@Jedn,''))>0 SET @SQL=@SQL+' AND (Jedn LIKE '''+Rtrim(@Jedn)+''')' if len(IsNull(@Komorka,''))>0 SET @SQL=@SQL+' AND (Komorka LIKE '''+Rtrim(@Komorka)+''')' SET @SQL1='' SET @Listy=Rtrim(IsNull(@listy,'')) if len(@listy)>0 begin SET @i=PATINDEX('%,%',@Listy) while @i>0 begin SET @xx=Substring(@Listy,1,@i-1) SET @Listy=Substring(@Listy,@i+1,Len(@Listy)-@i) SET @i=PATINDEX('%-%',@xx) if @i>0 SET @SQL1= @SQL1+' OR ((NrListy>='+Substring(@xx,1,@i-1)+') AND (NrListy<='+Substring(@xx,@i+1,Len(@xx)-@i)+'))' else SET @SQL1= @SQL1+' OR (NrListy='+@xx+')' SET @i=PATINDEX('%,%',@Listy) end if len(@Listy)>0 begin SET @xx=@Listy SET @i=PATINDEX('%-%',@xx) if @i>0 SET @SQL1= @SQL1+' OR ((NrListy>='+Substring(@xx,1,@i-1)+') AND (NrListy<='+Substring(@xx,@i+1,Len(@xx)-@i)+'))' else SET @SQL1= @SQL1+' OR (NrListy='+@xx+')' end if len(@SQL1)>4 SET @SQL=@SQL+' AND ('+Substring(@SQL1,5,len(@SQL1)-4)+')' end EXEC(@SQL) -- usuwanie osób z ograniczonym dostepem if (SELECT count(*) FROM #Osoby WHERE IsNull(Tak,0)=0)>0 begin -- są osoby do których nie ma uprawnien SET @Tak=1 DELETE FROM #Osoby WHERE IsNull(Tak,0)=0 end SET @xx=convert(Varchar(4),@Rok)+'0101' SET @Dzien=CONVERT(datetime,@xx) UPDATE #Osoby SET DataOd=CASE WHEN @Dzien>DataZatr THEN @Dzien ELSE DataZatr END SET @xx=convert(Varchar(4),@Rok)+'1231' SET @Dzien=CONVERT(datetime,@xx) UPDATE #Osoby SET DataS=DataOd,DataDo=ISNULL(DataZw,convert(datetime,@Dzien)) -- staż pracy SELECT @St=Staz FROM kdsParametry WHERE JK=@JK -------------------------------------------------------------------------------------------------- if IsNull(@St,0)=0 begin if @Typ=1 begin -- staz przed zakładem - pole w kartotece INSERT INTO #Staz2a (r,m,d,Typ,UID,ZP) SELECT p.StazL,p.StazM,p.StazD,9,p.UID,1 FROM tblPracownicy p INNER JOIN #Osoby o ON p.UID=o.UID WHERE (ISNULL(p.StazL,0)>0) OR (ISNULL(p.StazM,0)>0) OR (ISNULL(p.StazD,0)>0) INSERT INTO #Staz2 (r,m,d,Typ,UID,ZP) SELECT p.StazL,p.StazM,p.StazD,9,p.UID,1 FROM tblPracownicy p INNER JOIN #Osoby o ON p.UID=o.UID WHERE (ISNULL(p.StazL,0)>0) OR (ISNULL(p.StazM,0)>0) OR (ISNULL(p.StazD,0)>0) end --w zakł czas od daty zatrudnienia INSERT INTO #Staz2 (DataOd,DataDo,Typ,UID,ZP) SELECT p.DataZatrudnienia,CASE WHEN ISNULL(DataZwolnienia,o.DataOd)>=o.DataOd THEN o.DataOd ELSE DataZwolnienia END ,1,p.UID,0 FROM tblPracownicy p INNER JOIN #Osoby o ON p.UID=o.UID -- minus urlop bezpłatny po dacie zatrudnienia DECLARE C6 CURSOR LOCAL FOR SELECT w.UID_tblPracownicy,CASE WHEN w.DataOd<=o.DataZatr THEN o.DataZatr ELSE w.DataOd END, CASE WHEN w.DataDo>=o.DataOd THEN o.DataOd ELSE w.DataDo END FROM kdtAbsencje w INNER JOIN #Osoby o ON w.UID_tblPracownicy=o.UID INNER JOIN kdsTypyAbsencji t ON w.TypAbsencji=t.Symbol WHERE (t.KodZus='111') AND (w.DataOd<=o.DataOd) AND ((w.DataOd>=o.DataZatr) OR (w.DataDo>=o.DataZatr)) ORDER BY w.DataOd OPEN C6 FETCH C6 INTO @UID,@DataOd,@DataDo WHILE @@FETCH_STATUS >= 0 BEGIN SET @i=0 SELECT TOP 1 @i=ID FROM #Staz2 WHERE (UID=@UID) AND (DataDo=DATEADD(d,-1,@DataOd)) AND (Typ=2) if @i>0 begin UPDATE #Staz2 SET DataDo=@DataDo where ID=@i end else begin INSERT INTO #Staz2 (DataOd,DataDo,Typ,UID,ZP) Values (@DataOd,@DataDo,2,@UID,0) end FETCH C6 INTO @UID,@DataOd,@DataDo END CLOSE C6 DEALLOCATE C6 end else begin if @Typ=1 begin -- historia DECLARE C3 CURSOR LOCAL FOR SELECT p.UID_tblPracownicy,p.DataOd,CASE WHEN p.DataDo>o.DataOd THEN o.DataOd ELSE p.DataDo END FROM kdtPrzebiegPracy p INNER JOIN #Osoby o ON p.UID_tblPracownicy=o.UID WHERE (IsNull(p.Staz,0)=1) AND (p.DataOd Is Not Null) AND (p.DataDo Is Not Null) AND (p.DataOd<=o.DataOd) ORDER BY p.DataOd OPEN C3 FETCH C3 INTO @UID,@DataOd,@DataDo WHILE @@FETCH_STATUS >= 0 BEGIN SET @i=0 SELECT TOP 1 @i=ID FROM #Staz2 WHERE (UID=@UID) AND (DataDo=DATEADD(d,-1,@DataOd)) if @i>0 begin UPDATE #Staz2 SET DataDo=@DataDo where ID=@i end else begin INSERT INTO #Staz2 (DataOd,DataDo,Typ,UID,ZP) Values (@DataOd,@DataDo,1,@UID,1) end SET @i=0 SELECT TOP 1 @i=ID FROM #Staz2a WHERE (UID=@UID) AND (DataDo=DATEADD(d,-1,@DataOd)) if @i>0 begin UPDATE #Staz2a SET DataDo=@DataDo where ID=@i end else begin INSERT INTO #Staz2a (DataOd,DataDo,Typ,UID,ZP) Values (@DataOd,@DataDo,1,@UID,1) end FETCH C3 INTO @UID,@DataOd,@DataDo END CLOSE C3 DEALLOCATE C3 end -- stanowiska DECLARE C4 CURSOR LOCAL FOR SELECT p.UID_tblPracownicy,p.DataOd,CASE WHEN IsNull(p.DataDo,o.DataOd)>=o.DataOd THEN o.DataOd ELSE p.DataDo END FROM kdtStanowiska p INNER JOIN #Osoby o ON p.UID_tblPracownicy=o.UID WHERE (IsNull(p.Staz,0)=1) AND (p.DataOd Is Not Null) AND (p.DataOd<=o.DataOd) ORDER BY p.DataOd OPEN C4 FETCH C4 INTO @UID,@DataOd,@DataDo WHILE @@FETCH_STATUS >= 0 BEGIN SET @i=0 SELECT TOP 1 @i=ID FROM #Staz2 WHERE (UID=@UID) AND (DataDo=DATEADD(d,-1,@DataOd)) AND (ZP=0) if @i>0 begin UPDATE #Staz2 SET DataDo=@DataDo where ID=@i end else begin INSERT INTO #Staz2 (DataOd,DataDo,Typ,UID,ZP) Values (@DataOd,@DataDo,1,@UID,0) end FETCH C4 INTO @UID,@DataOd,@DataDo END CLOSE C4 DEALLOCATE C4 if @Typ=1 begin if IsNull(@BezWykszt,0)=0 begin if IsNull(@St,0)=1 begin -- wykształcenie INSERT INTO #Staz2 (r,m,Typ,UID,ZP) SELECT w.Lata,w.Miesiace,9,s1.UID,1 FROM tblPracownicy s1 INNER JOIN #Osoby o ON s1.UID=o.UID INNER JOIN kdsWyksztalcenie w ON s1.Wyksztalcenie=w.Symbol INSERT INTO #Staz2a (r,m,Typ,UID,ZP) SELECT w.Lata,w.Miesiace,9,s1.UID,1 FROM tblPracownicy s1 INNER JOIN #Osoby o ON s1.UID=o.UID INNER JOIN kdsWyksztalcenie w ON s1.Wyksztalcenie=w.Symbol end else begin -- z wykazu szkół INSERT INTO #Staz2 (r,m,d,Typ,UID,ZP) SELECT StazL=SUM(StazRR),StazM=SUM(StazMM),StazD=SUM(StazDD),9,p.UID_tblPracownicy,1 FROM kdtWyksztalcenie p INNER JOIN #Osoby o ON p.UID_tblPracownicy=o.UID WHERE (ISNULL(p.DataZak,o.DataOd)<=o.DataOd) AND (ISNULL(p.DataStazDo,o.DataOd)>=o.DataOd) GROUP BY p.UID_tblPracownicy INSERT INTO #Staz2a (r,m,d,Typ,UID,ZP) SELECT StazL=SUM(StazRR),StazM=SUM(StazMM),StazD=SUM(StazDD),9,p.UID_tblPracownicy,1 FROM kdtWyksztalcenie p INNER JOIN #Osoby o ON p.UID_tblPracownicy=o.UID WHERE (ISNULL(p.DataZak,o.DataOd)<=o.DataOd) AND (ISNULL(p.DataStazDo,o.DataOd)>=o.DataOd) GROUP BY p.UID_tblPracownicy end end end -- minus urlop bezpłatny DECLARE C5 CURSOR LOCAL FOR SELECT w.UID_tblPracownicy,w.DataOd,CASE WHEN IsNull(w.DataDo,o.DataOd)>=o.DataOd THEN o.DataOd ELSE w.DataDo END FROM kdtAbsencje w INNER JOIN #Osoby o ON w.UID_tblPracownicy=o.UID INNER JOIN kdsTypyAbsencji t ON w.TypAbsencji=t.Symbol WHERE (t.KodZus='111') AND (w.DataOd<=o.DataOd) ORDER BY w.DataOd OPEN C5 FETCH C5 INTO @UID,@DataOd,@DataDo WHILE @@FETCH_STATUS >= 0 BEGIN SET @i=0 SELECT TOP 1 @i=ID FROM #Staz2 WHERE (UID=@UID) AND (DataDo=DATEADD(d,-1,@DataOd)) AND (Typ=2) if @i>0 begin UPDATE #Staz2 SET DataDo=@DataDo where ID=@i end else begin INSERT INTO #Staz2 (DataOd,DataDo,Typ,UID,ZP) Values (@DataOd,@DataDo,2,@UID,0) end FETCH C5 INTO @UID,@DataOd,@DataDo END CLOSE C5 DEALLOCATE C5 end --staż na początek miesiąca lub datę zatrudnienia(jeżeli póxniejsza) UPDATE #Staz2 SET Staz=IsNull(dbo.pl_LMD(DataOd,DataDo),'') WHERE Typ<9 UPDATE #Staz2 SET r=convert(int,Substring(Staz,1,PATINDEX('%/%',Staz)-1)), Staz=Substring(Staz,PATINDEX('%/%',Staz)+1,5) WHERE (Typ<9) AND (LEN(Staz)>0) UPDATE #Staz2 SET m=convert(int,Substring(Staz,1,PATINDEX('%/%',Staz)-1)), Staz=Substring(Staz,PATINDEX('%/%',Staz)+1,2) WHERE (Typ<9) AND (LEN(Staz)>0) UPDATE #Staz2 SET d=convert(int,Staz) WHERE Typ<9 UPDATE #Osoby SET StazD=b.d,StazM=b.m,StazL=b.r FROM #Osoby a INNER JOIN (SELECT o.UID,r=SUM(CASE WHEN TYP=2 THEN -1 ELSE 1 END *IsNull(s.r,0)), m=SUM(CASE WHEN TYP=2 THEN -1 ELSE 1 END *IsNull(s.m,0)), d=SUM(CASE WHEN TYP=2 THEN -1 ELSE 1 END *IsNull(s.d,0)) FROM #Osoby o INNER JOIN #Staz2 s ON o.UID=s.UID GROUP BY o.UID) b ON a.UID=b.UID While (Select COUNT(*) FROM #Osoby WHERE StazD<0)>0 begin UPDATE #Osoby SET StazM=Isnull(StazM,0)-1, StazD=StazD+30 WHERE StazD<0 end UPDATE #Osoby SET Staz=Isnull(StazD,0) % 30 UPDATE #Osoby SET StazM=Isnull(StazM,0)+(IsNull(StazD,0)-Staz)/30 UPDATE #Osoby SET StazD=Staz While (Select COUNT(*) FROM #Osoby WHERE StazM<0)>0 begin UPDATE #Osoby SET StazL=Isnull(StazL,0)-1, StazM=StazM+12 WHERE StazM<0 end UPDATE #Osoby SET Staz=Isnull(StazM,0) % 12 UPDATE #Osoby SET StazL=Isnull(StazL,0)+(IsNull(StazM,0)-Staz)/12 UPDATE #Osoby SET StazM=Staz UPDATE #Osoby SET Jest=1 FROM #Osoby o INNER JOIN #Lata l ON o.StazL=l.Rok WHERE (IsNull(StazM,0)=0) AND (IsNull(StazD,0)=0) UPDATE #Osoby SET Jest=2 FROM #Osoby o INNER JOIN #Lata l ON o.StazL=(l.Rok-1) WHERE Jest=0 UPDATE #Osoby SET DataOd=CASE WHEN DataZatr>DataOd then DataZatr ELSE DataOd END WHERE Jest=2 UPDATE #Osoby SET DataS=DataOd, StazLs=StazL,StazMs=StazM,StazDs=StazD UPDATE #Osoby SET DataOd=DATEADD(m,12-IsNull(StazM,0)-1, DataOd) WHERE Jest=2 UPDATE #Osoby SET Jest=0 WHERE (Jest=2) AND (Year(DataOd)<>@Rok) DELETE FROM #Staz2a WHERE UID IN (SELECT UID FROM #Osoby WHERE Jest=0) DELETE FROM #Osoby WHERE Jest=0 ------------------------------------------------------------------------------------------------- WHILE (SELECT count(*) FROM #Osoby WHERE Jest=2)>0 BEGIN -------------------------------------------------------------------------------------------------- DELETE FROM #Staz2 INSERT INTO #Staz2 (r,m,d,Typ,UID,ZP) SELECT r,m,d,Typ,UID,ZP FROM #Staz2a UPDATE #Osoby SET StazL=0,StazM=0,StazD=0,Staz=0 WHERE Jest=2 if IsNull(@St,0)=0 begin --w zakł czas od daty zatrudnienia INSERT INTO #Staz2 (DataOd,DataDo,Typ,UID,ZP) SELECT p.DataZatrudnienia,CASE WHEN ISNULL(DataZwolnienia,o.DataOd)>=o.DataOd THEN o.DataOd ELSE DataZwolnienia END ,1,p.UID,0 FROM tblPracownicy p INNER JOIN #Osoby o ON p.UID=o.UID -- minus urlop bezpłatny po dacie zatrudnienia DECLARE C6 CURSOR LOCAL FOR SELECT w.UID_tblPracownicy,CASE WHEN w.DataOd<=o.DataZatr THEN o.DataZatr ELSE w.DataOd END, CASE WHEN w.DataDo>=o.DataOd THEN o.DataOd ELSE w.DataDo END FROM kdtAbsencje w INNER JOIN #Osoby o ON w.UID_tblPracownicy=o.UID INNER JOIN kdsTypyAbsencji t ON w.TypAbsencji=t.Symbol WHERE (t.KodZus='111') AND (w.DataOd<=o.DataOd) AND ((w.DataOd>=o.DataZatr) OR (w.DataDo>=o.DataZatr)) ORDER BY w.DataOd OPEN C6 FETCH C6 INTO @UID,@DataOd,@DataDo WHILE @@FETCH_STATUS >= 0 BEGIN SET @i=0 SELECT TOP 1 @i=ID FROM #Staz2 WHERE (UID=@UID) AND (DataDo=DATEADD(d,-1,@DataOd)) AND (Typ=2) if @i>0 begin UPDATE #Staz2 SET DataDo=@DataDo where ID=@i end else begin INSERT INTO #Staz2 (DataOd,DataDo,Typ,UID,ZP) Values (@DataOd,@DataDo,2,@UID,0) end FETCH C6 INTO @UID,@DataOd,@DataDo END CLOSE C6 DEALLOCATE C6 end else begin -- stanowiska DECLARE C4 CURSOR LOCAL FOR SELECT p.UID_tblPracownicy,p.DataOd,CASE WHEN IsNull(p.DataDo,o.DataOd)>=o.DataOd THEN o.DataOd ELSE p.DataDo END FROM kdtStanowiska p INNER JOIN #Osoby o ON p.UID_tblPracownicy=o.UID WHERE (IsNull(p.Staz,0)=1) AND (p.DataOd Is Not Null) AND (p.DataOd<=o.DataOd) ORDER BY DataOd OPEN C4 FETCH C4 INTO @UID,@DataOd,@DataDo WHILE @@FETCH_STATUS >= 0 BEGIN SET @i=0 SELECT TOP 1 @i=ID FROM #Staz2 WHERE (UID=@UID) AND (DataDo=DATEADD(d,-1,@DataOd)) AND (ZP=0) if @i>0 begin UPDATE #Staz2 SET DataDo=@DataDo where ID=@i end else begin INSERT INTO #Staz2 (DataOd,DataDo,Typ,UID,ZP) Values (@DataOd,@DataDo,1,@UID,0) end FETCH C4 INTO @UID,@DataOd,@DataDo END CLOSE C4 DEALLOCATE C4 -- minus urlop bezpłatny DECLARE C5 CURSOR LOCAL FOR SELECT w.UID_tblPracownicy,w.DataOd,CASE WHEN IsNull(w.DataDo,o.DataOd)>=o.DataOd THEN o.DataOd ELSE w.DataDo END FROM kdtAbsencje w INNER JOIN #Osoby o ON w.UID_tblPracownicy=o.UID INNER JOIN kdsTypyAbsencji t ON w.TypAbsencji=t.Symbol WHERE (t.KodZus='111') AND (w.DataOd<=@Dzien) ORDER BY w.DataOd OPEN C5 FETCH C5 INTO @UID,@DataOd,@DataDo WHILE @@FETCH_STATUS >= 0 BEGIN SET @i=0 SELECT TOP 1 @i=ID FROM #Staz2 WHERE (UID=@UID) AND (DataDo=DATEADD(d,-1,@DataOd)) AND (Typ=2) if @i>0 begin UPDATE #Staz2 SET DataDo=@DataDo where ID=@i end else begin INSERT INTO #Staz2 (DataOd,DataDo,Typ,UID,ZP) Values (@DataOd,@DataDo,2,@UID,0) end FETCH C5 INTO @UID,@DataOd,@DataDo END CLOSE C5 DEALLOCATE C5 end UPDATE #Staz2 SET Staz=IsNull(dbo.pl_LMD(DataOd,DataDo),'') WHERE Typ<9 UPDATE #Staz2 SET r=convert(int,Substring(Staz,1,PATINDEX('%/%',Staz)-1)), Staz=Substring(Staz,PATINDEX('%/%',Staz)+1,5) WHERE (Typ<9) AND (LEN(Staz)>0) UPDATE #Staz2 SET m=convert(int,Substring(Staz,1,PATINDEX('%/%',Staz)-1)), Staz=Substring(Staz,PATINDEX('%/%',Staz)+1,2) WHERE (Typ<9) AND (LEN(Staz)>0) UPDATE #Staz2 SET d=convert(int,Staz) WHERE Typ<9 UPDATE #Osoby SET StazD=b.d,StazM=b.m,StazL=b.r FROM #Osoby a INNER JOIN (SELECT o.UID,r=SUM(CASE WHEN TYP=2 THEN -1 ELSE 1 END *IsNull(s.r,0)), m=SUM(CASE WHEN TYP=2 THEN -1 ELSE 1 END *IsNull(s.m,0)), d=SUM(CASE WHEN TYP=2 THEN -1 ELSE 1 END *IsNull(s.d,0)) FROM #Osoby o INNER JOIN #Staz2 s ON o.UID=s.UID GROUP BY o.UID) b ON a.UID=b.UID WHERE a.Jest=2 While (Select COUNT(*) FROM #Osoby WHERE StazD<0)>0 begin UPDATE #Osoby SET StazM=Isnull(StazM,0)-1, StazD=StazD+30 WHERE (Jest=2) AND (StazD<0) end UPDATE #Osoby SET Staz=Isnull(StazD,0) % 30 WHERE (Jest=2) UPDATE #Osoby SET StazM=Isnull(StazM,0)+(IsNull(StazD,0)-Staz)/30 WHERE (Jest=2) UPDATE #Osoby SET StazD=Staz WHERE (Jest=2) While (Select COUNT(*) FROM #Osoby WHERE (Jest=2) AND (StazM<0))>0 begin UPDATE #Osoby SET StazL=Isnull(StazL,0)-1, StazM=StazM+12 WHERE (Jest=2) AND (StazM<0) end UPDATE #Osoby SET Staz=Isnull(StazM,0) % 12 WHERE (Jest=2) UPDATE #Osoby SET StazL=Isnull(StazL,0)+(IsNull(StazM,0)-Staz)/12 WHERE (Jest=2) UPDATE #Osoby SET StazM=Staz WHERE (Jest=2) UPDATE #Osoby SET Jest=1 FROM #Osoby o INNER JOIN #Lata l ON o.StazL=l.Rok WHERE (Jest=2) AND (IsNull(StazM,0)=0) AND (IsNull(StazD,0)=0) UPDATE #Osoby SET DataOd=DATEADD(d,1,DataOd) WHERE Jest=2 UPDATE #Osoby SET Jest=0 WHERE (Jest=2) AND (Year(DataOd)<>@Rok) DELETE FROM #Staz2 WHERE UID IN (SELECT UID FROM #Osoby WHERE Jest=0) DELETE FROM #Osoby WHERE Jest=0 END SET @SQL='SELECT Tak='+convert(varchar(1),@Tak)+',l.UID,o.DataZatrudnienia,o.DataZwolnienia, l.DataS,StazL=l.StazLs,StazM=l.StazMs,StazD=l.StazDs, DataRocznicy=l.DataOd,LataRocznicy=l.StazL, Osoba=Rtrim(IsNull(o.Nazwisko,''''))+'' ''+Rtrim(IsNull(o.Imie,''''))+'' ''+Rtrim(IsNull(o.ImieDrugie,''''))+'' (''+Convert(varchar(10),o.NrEto)+'')'' , Stanowisko=CASE WHEN l.UID Is Null THEN '' '' ELSE (SELECT TOP 1 St=''(''+convert(varchar(2),s.Nr)+'') ''+IsNull(s1.Nazwa,'''') FROM kdtStanowiska s INNER JOIN kdsStanowiska s1 ON s.Stanowisko=s1.Symbol WHERE (s.UID_tblPracownicy=o.UID) AND (s.DataOd<=l.DataOd) AND (IsNull(s.DataDo,l.DataOd)>=l.DataOd) ORDER BY s.Nr,s.DataOd DESC) END, l.Symbol,' if (@Sort>0) AND (@Sort<4) begin SET @SQL=@SQL+'SymbolN=l.Symbol+CASE WHEN l.Symbol=''Razem'' THEN '''' ELSE '' - ''+IsNull(j.Nazwa,'''') END' end else begin SET @SQL=@SQL+'SymbolN=l.Symbol' end SET @SQL=@SQL+' FROM #Osoby l LEFT OUTER JOIN tblPracownicy o ON l.UID=o.UID' if @Sort=1 begin SET @SQL=@SQL+' LEFT OUTER JOIN (SELECT * FROM kdsJednostki WHERE JK='+convert(varchar(10),@JK)+') j ON l.Symbol=j.Symbol' end else if @Sort=2 begin SET @SQL=@SQL+' LEFT OUTER JOIN (SELECT * FROM kdsKomorkiKosztow WHERE JK='+convert(varchar(10),@JK)+') j ON l.Symbol=j.Symbol' end else if @Sort=3 begin SET @SQL=@SQL+' LEFT OUTER JOIN (SELECT * FROM kdsNumeryListPlac WHERE JK='+convert(varchar(10),@JK)+') j ON l.Lp=j.Numer' end if @Sort=4 SET @SQL=@SQL+' ORDER BY l.DataR,o.Nazwisko,o.Imie,IsNull(o.ImieDrugie,''''),o.NrEto' else SET @SQL=@SQL+' ORDER BY l.Lp,l.Symbol,o.Nazwisko,o.Imie,IsNull(o.ImieDrugie,''''),o.NrEto' EXEC(@SQL) GO