﻿--Dupla tanítási órás HF törlés
UPDATE hf SET
   TOROLT='T'
  ,MODIFIER = 0 
  ,LASTCHANGED = GETDATE()
FROM(  
  SELECT 
     ID
    ,ROW_NUMBER() OVER(PARTITION BY hf.C_TANITASIORAID ORDER BY CREATED) rn
  FROM (
    SELECT 
       C_TANITASIORAID
    FROM T_HAZIFELADATOK 
    WHERE TOROLT='F' AND C_TANITASIORAID IS NOT NULL
    GROUP BY C_TANITASIORAID
    HAVING COUNT(*)>1) dupla
    INNER JOIN T_HAZIFELADATOK hf ON hf.C_TANITASIORAID = dupla.C_TANITASIORAID
      AND hf.TOROLT='F' AND hf.C_TANITASIORAID IS NOT NULL) duplaId
  INNER JOIN T_HAZIFELADATOK hf ON hf.ID=duplaId.ID AND duplaId.rn>1
--Dupla órarendi órás HF törlés
UPDATE hf SET
   TOROLT='T'
  ,MODIFIER = 0 
  ,LASTCHANGED = GETDATE()
FROM(  
  SELECT 
     ID
    ,ROW_NUMBER() OVER(PARTITION BY hf.C_ORARENDIORAID,hf.C_FELADASDATUMA ORDER BY hf.CREATED) rn
  FROM (
    SELECT 
       C_ORARENDIORAID
      ,C_FELADASDATUMA
    FROM T_HAZIFELADATOK 
    WHERE TOROLT='F' AND C_ORARENDIORAID IS NOT NULL
    GROUP BY C_ORARENDIORAID,C_FELADASDATUMA
    HAVING COUNT(*)>1) dupla
    INNER JOIN T_HAZIFELADATOK hf ON hf.C_ORARENDIORAID = dupla.C_ORARENDIORAID AND hf.C_FELADASDATUMA = dupla.C_FELADASDATUMA
      AND hf.TOROLT='F' AND hf.C_ORARENDIORAID IS NOT NULL) duplaId
  INNER JOIN T_HAZIFELADATOK hf ON hf.ID=duplaId.ID AND duplaId.rn>1
--Dupla tanítási órás Uindex
DROP INDEX IF EXISTS T_HAZIFELADATOK.NCU_Hazifeladatok_TanevId_IntezmenyId_TanitasiOraId
GO
CREATE UNIQUE INDEX NCU_Hazifeladatok_TanevId_IntezmenyId_TanitasiOraId
ON T_HAZIFELADATOK (
   C_TANEVID
  ,C_INTEZMENYID
  ,C_TANITASIORAID
)
WHERE TOROLT='F' AND C_TANITASIORAID IS NOT NULL
WITH FILLFACTOR = 80
GO
--Dupla órarendi órás Uindex
DROP INDEX IF EXISTS T_HAZIFELADATOK.NCU_Hazifeladatok_TanevId_IntezmenyId_OrarendiOraId_FeladasDatuma
GO
CREATE UNIQUE INDEX NCU_Hazifeladatok_TanevId_IntezmenyId_OrarendiOraId_FeladasDatuma
ON T_HAZIFELADATOK (
   C_TANEVID
  ,C_INTEZMENYID
  ,C_ORARENDIORAID
  ,C_FELADASDATUMA
)
WHERE TOROLT='F' AND C_ORARENDIORAID IS NOT NULL
WITH FILLFACTOR = 80
GO