39 lines
1.2 KiB
Transact-SQL
39 lines
1.2 KiB
Transact-SQL
DROP PROCEDURE IF EXISTS uspTanevValtasAlkalmazottTorlesKereses
|
|
GO
|
|
CREATE PROCEDURE uspTanevValtasAlkalmazottTorlesKereses
|
|
@pKovTanevId INT
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT a.ID
|
|
,a.ID AS AlkalmazottId
|
|
,fel.C_NYOMTATASINEV AS AlkalmazottNev
|
|
,fel.C_OKTATASIAZONOSITO AS OktatasiAzonosito
|
|
,m.C_ALKALMAZASKEZDETE AS AlkalmazasKezdete
|
|
,m.C_ALKALMAZASMEGSZUNESE AS AlkalmazasVege
|
|
FROM T_ALKALMAZOTT_OSSZES a
|
|
INNER JOIN T_FELHASZNALO_OSSZES fel ON fel.ID = a.ID
|
|
AND fel.TOROLT = 'F'
|
|
AND fel.C_TANEVID = a.C_ALTANEVID
|
|
INNER JOIN T_TANEV_OSSZES t ON t.ID = @pKovTanevId
|
|
AND t.C_KOVETKEZO = 'T'
|
|
AND t.TOROLT = 'F'
|
|
INNER JOIN T_MUNKAUGYIADATOK_OSSZES m ON m.C_ALKALMAZOTTID = a.ID
|
|
AND m.C_TANEVID = a.C_ALTANEVID
|
|
AND m.TOROLT = 'F'
|
|
LEFT JOIN T_ORARENDIORA_OSSZES oo ON oo.C_TANARID = a.ID
|
|
AND oo.TOROLT = 'F'
|
|
AND oo.C_TANEVID = a.C_ALTANEVID
|
|
LEFT JOIN T_FOGLALKOZAS_OSSZES f ON f.C_TANARID = a.ID
|
|
AND f.TOROLT = 'F'
|
|
AND f.C_TANEVID = a.C_ALTANEVID
|
|
WHERE a.C_ALTANEVID = @pKovTanevId
|
|
AND C_ALKALMAZASMEGSZUNESE <= DATEADD(DAY, - 1, t.C_KEZDONAP)
|
|
AND oo.ID IS NULL
|
|
AND f.ID IS NULL
|
|
AND a.TOROLT = 'F'
|
|
END
|
|
GO
|
|
|
|
|