48 lines
1.4 KiB
Transact-SQL
48 lines
1.4 KiB
Transact-SQL
DROP PROCEDURE IF EXISTS uspDeleteLastsZaradekokAndZaradekNyelvekByTanulocsoportok
|
||
GO
|
||
|
||
CREATE PROCEDURE uspDeleteLastsZaradekokAndZaradekNyelvekByTanulocsoportok
|
||
@pModifierId int
|
||
,@pTanuloCsoportIdList nvarchar(max)
|
||
,@pIsOsztalyList bit
|
||
|
||
AS
|
||
BEGIN
|
||
SET NOCOUNT ON;
|
||
|
||
CREATE TABLE #Zaradek (Id int, Rn int, Cnt int)
|
||
|
||
INSERT INTO #Zaradek
|
||
SELECT
|
||
z.ID AS Id
|
||
,ROW_NUMBER() OVER (PARTITION BY C_TANULOCSOPORTID ORDER BY C_ROGZITESDATUMA DESC) AS Rn
|
||
,IIF(@pIsOsztalyList = 0 OR tcs.C_KILEPESDATUM IS NULL OR z.C_ISKIVAGYATSOROLASIZARADEK = 'F', 2, COUNT(1) OVER (PARTITION BY C_TANULOCSOPORTID ORDER BY C_ISKIVAGYATSOROLASIZARADEK DESC)) AS Cnt
|
||
FROM T_ZARADEK_OSSZES z
|
||
INNER JOIN T_TANULOCSOPORT_OSSZES tcs ON tcs.ID = z.C_TANULOCSOPORTID AND tcs.TOROLT = 'F'
|
||
WHERE tcs.ID IN (SELECT VALUE FROM string_split(@pTanuloCsoportIdList, ','))
|
||
AND z.TOROLT = 'F'
|
||
|
||
UPDATE z SET
|
||
z.TOROLT = 'T'
|
||
,z.SERIAL += 1
|
||
,z.LASTCHANGED = GETDATE()
|
||
,z.MODIFIER = @pModifierId
|
||
FROM T_ZARADEK_OSSZES z
|
||
INNER JOIN #Zaradek zaradek ON zaradek.ID = z.ID
|
||
AND zaradek.RN = 1
|
||
AND zaradek.CNT > 1
|
||
|
||
UPDATE zny SET
|
||
zny.TOROLT = 'T'
|
||
,zny.SERIAL = zny.SERIAL + 1
|
||
,zny.LASTCHANGED = GETDATE()
|
||
,zny.MODIFIER = @pModifierId
|
||
FROM T_ZARADEKNYELV_OSSZES zny
|
||
INNER JOIN #Zaradek zaradek ON zaradek.ID = zny.C_ZARADEKID
|
||
AND zaradek.RN = 1
|
||
AND zaradek.CNT > 1
|
||
|
||
DROP TABLE #Zaradek
|
||
|
||
END
|
||
GO
|