﻿UPDATE T_TANITASIORA SET TOROLT='T',LASTCHANGED=GETDATE(),MODIFIER=0 WHERE ID IN(
SELECT ID FROM(
SELECT 
   tao.ID 
  ,ROW_NUMBER() OVER (PARTITION BY 
     tao.C_DATUM
    ,tao.C_OSZTALYCSOPORTID
    ,tao.C_TANTARGYID
    ,tao.C_TANARID
    ,tao.C_TANEVID
    ,tao.C_INTEZMENYID
    ,tao.C_ORAKEZDETE
    ,tao.C_ORAVEGE
    ORDER BY tao.CREATED) rn
FROM(
SELECT 
   C_DATUM
  ,C_OSZTALYCSOPORTID
  ,C_TANTARGYID
  ,C_TANARID
  ,C_TANEVID
  ,C_INTEZMENYID
  ,C_ORAKEZDETE
  ,C_ORAVEGE
FROM T_TANITASIORA
WHERE TOROLT='F' AND C_ORASZAM IS NULL
GROUP BY 
   C_DATUM
  ,C_OSZTALYCSOPORTID
  ,C_TANTARGYID
  ,C_TANARID
  ,C_TANEVID
  ,C_INTEZMENYID
  ,C_ORAKEZDETE
  ,C_ORAVEGE
HAVING COUNT(*)>1) x
  INNER JOIN T_TANITASIORA tao ON tao.C_DATUM = x.C_DATUM
    AND tao.C_OSZTALYCSOPORTID = x.C_OSZTALYCSOPORTID
    AND tao.C_TANTARGYID = x.C_TANTARGYID
    AND tao.C_TANARID = x.C_TANARID
    AND tao.C_TANEVID = x. C_TANEVID
    AND tao.C_INTEZMENYID = x.C_INTEZMENYID
    AND tao.C_ORAKEZDETE = x.C_ORAKEZDETE
    AND tao.C_ORAVEGE = x.C_ORAVEGE
    AND tao.TOROLT='F' AND C_ORASZAM IS NULL) y WHERE rn>1)

--===============C_ORASZAM NOT NULL==============
SELECT 
   C_DATUM
  ,C_OSZTALYCSOPORTID
  ,C_TANTARGYID
  ,C_TANARID
  ,C_TANEVID
  ,C_INTEZMENYID
  ,C_ORASZAM
INTO #tmpRecords
FROM T_TANITASIORA
WHERE TOROLT='F' AND C_ORASZAM IS NOT NULL
GROUP BY 
   C_DATUM
  ,C_OSZTALYCSOPORTID
  ,C_TANTARGYID
  ,C_TANARID
  ,C_TANEVID
  ,C_INTEZMENYID
  ,C_ORASZAM
HAVING COUNT(*)>1

SELECT 
  tao.ID, 
  ROW_NUMBER() OVER (PARTITION BY 
     tao.C_DATUM
    ,tao.C_OSZTALYCSOPORTID
    ,tao.C_TANTARGYID
    ,tao.C_TANARID
    ,tao.C_TANEVID
    ,tao.C_INTEZMENYID
    ,tao.C_ORASZAM
    ORDER BY tao.CREATED) rn
  INTO #tmpIds
FROM #tmpRecords t
  INNER JOIN T_TANITASIORA tao ON tao.C_DATUM = t.C_DATUM
    AND tao.C_OSZTALYCSOPORTID = t.C_OSZTALYCSOPORTID
    AND tao.C_TANTARGYID = t.C_TANTARGYID
    AND tao.C_TANARID = t.C_TANARID
    AND tao.C_TANEVID = t.C_TANEVID
    AND tao.C_INTEZMENYID = t.C_INTEZMENYID
    AND tao.C_ORASZAM = t.C_ORASZAM
    AND tao.TOROLT='F' AND tao.C_ORASZAM IS NOT NULL

DELETE FROM #tmpIds WHERE rn<=1
ALTER TABLE T_TANITASIORA DISABLE TRIGGER ALL
UPDATE T_TANITASIORA SET TOROLT='T',MODIFIER=0, LASTCHANGED=GETDATE() WHERE ID IN (SELECT ID FROM #tmpIds)
ALTER TABLE T_TANITASIORA ENABLE TRIGGER ALL
  
--===============INDEXEK==============
DROP INDEX IF EXISTS T_TANITASIORA.NCU_TanitasiOra_TanevId_IntezmenyId_Datum_OsztalyCsoportId_TantargyId_TanarId_Orakezdete_Oravege
CREATE UNIQUE INDEX NCU_TanitasiOra_TanevId_IntezmenyId_Datum_OsztalyCsoportId_TantargyId_TanarId_Orakezdete_Oravege ON T_TANITASIORA 
(
     C_TANEVID
    ,C_INTEZMENYID
    ,C_DATUM
    ,C_OSZTALYCSOPORTID
    ,C_TANTARGYID
    ,C_TANARID
    ,C_ORAKEZDETE
    ,C_ORAVEGE
)
WHERE TOROLT='F' AND C_ORASZAM IS NULL
WITH (FILLFACTOR = 80)

DROP INDEX IF EXISTS T_TANITASIORA.NCU_TanitasiOra_Datum_OsztalyCsoportId_TantargyId_TanarId_TanevId_IntezmenyId_Oraszam
CREATE UNIQUE INDEX NCU_TanitasiOra_Datum_OsztalyCsoportId_TantargyId_TanarId_TanevId_IntezmenyId_Oraszam ON T_TANITASIORA 
(
     C_TANEVID
    ,C_INTEZMENYID
    ,C_DATUM
    ,C_OSZTALYCSOPORTID
    ,C_TANTARGYID
    ,C_TANARID
    ,C_ORASZAM
)
WHERE TOROLT='F' AND C_ORASZAM IS NOT NULL
WITH (FILLFACTOR = 80)

