﻿--Migration
UPDATE es SET
   es.TOROLT = 'T'
  ,es.MODIFIER = 0
  ,es.LASTCHANGED = GETDATE()
FROM T_TANULOESEMENY es
  INNER JOIN (
    SELECT te.ID, ROW_NUMBER() OVER(PARTITION BY tte.C_TANULOID, te.C_TANITASIORAID, te.C_TIPUS ORDER BY te.CREATED DESC) RN
    FROM T_TANULO_TANULOESEMENY tte
      INNER JOIN T_TANULOESEMENY te ON tte.C_TANULOESEMENYID = te.ID
    --  INNER JOIN T_TANEV tv ON tv.C_AKTIV = 'T' AND tv.ID = te.C_TANEVID
    WHERE te.C_TANITASIORAID IS NOT NULL 
      AND te.TOROLT = 'F'
  ) x ON x.ID = es.ID
WHERE x.RN > 1
GO

UPDATE es SET
   es.C_BEIRASTANULOID = tte.C_TANULOID
  ,es.MODIFIER = 0
  ,es.LASTCHANGED = GETDATE()
FROM T_TANULOESEMENY es
  INNER JOIN T_TANULO_TANULOESEMENY tte ON tte.C_TANULOESEMENYID = es.ID 
WHERE es.C_TANITASIORAID IS NOT NULL 
  AND es.TOROLT = 'F'
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'NCU_TanuloEsemeny_TanitasiOraId_Tipus_BeirasTanuloId') BEGIN
CREATE UNIQUE NONCLUSTERED INDEX NCU_TanuloEsemeny_TanitasiOraId_Tipus_BeirasTanuloId 
  ON dbo.T_TANULOESEMENY (C_TANITASIORAID, C_TIPUS, C_BEIRASTANULOID) 
WHERE C_TANITASIORAID IS NOT NULL 
  AND C_BEIRASTANULOID IS NOT NULL 
  AND TOROLT = 'F'
  WITH (FILLFACTOR = 80)
END
GO
