69 lines
2.1 KiB
SQL
69 lines
2.1 KiB
SQL
--------------------------------------------------------------------------------
|
|
--- A T_TANITASIORA hiányos, de a releváns T_TANITASIORADIGTAMESZKOZ kitöltve
|
|
--------------------------------------------------------------------------------
|
|
UPDATE t
|
|
SET
|
|
t.C_DIGESZKOZTIPUSID = ISNULL(C_DIGESZKOZTIPUSID, 8587)
|
|
,C_DIGPLATFORMTIPUSID = ISNULL(C_DIGPLATFORMTIPUSID, 8573)
|
|
FROM T_TANITASIORA t
|
|
INNER JOIN T_TANITASIORADIGTAMESZKOZ todte ON todte.C_TANITASIORAID = t.ID AND todte.TOROLT = 'F'
|
|
INNER JOIN T_FELHASZNALO f ON f.ID = t.C_TANARID AND f.TOROLT = 'F'
|
|
WHERE t.TOROLT = 'F'
|
|
AND t.C_MEGTARTOTT = 'T'
|
|
AND (C_DIGESZKOZTIPUSID IS NULL
|
|
or C_DIGPLATFORMTIPUSID IS NULL)
|
|
|
|
--------------------------------------------------------------------------------
|
|
--- A T_TANITASIORA invalid adatot tartalmaz
|
|
--------------------------------------------------------------------------------
|
|
UPDATE todte
|
|
SET
|
|
todte.TOROLT = 'T'
|
|
FROM T_TANITASIORADIGTAMESZKOZ todte
|
|
WHERE todte.TOROLT = 'F'
|
|
AND todte.C_DIGTAMESZKOZTIPUSID = 0
|
|
|
|
--------------------------------------------------------------------------------
|
|
--- A T_TANITASIORA kitöltve, de a releváns T_TANITASIORADIGTAMESZKOZ hiányos
|
|
--------------------------------------------------------------------------------
|
|
;WITH cte AS (
|
|
SELECT
|
|
t.id AS tanitasiOraId
|
|
,t.C_INTEZMENYID AS intezmenyId
|
|
,t.C_TANEVID AS tanevId
|
|
FROM T_TANITASIORA t
|
|
LEFT JOIN T_TANITASIORADIGTAMESZKOZ todte ON todte.C_TANITASIORAID = t.ID
|
|
INNER JOIN T_FELHASZNALO f ON f.ID = t.C_TANARID AND F.TOROLT = 'F'
|
|
WHERE t.TOROLT = 'F'
|
|
AND t.C_MEGTARTOTT = 'T'
|
|
AND C_DIGESZKOZTIPUSID IS NOT NULL
|
|
AND todte.id IS NULL
|
|
AND todte.TOROLT = 'F'
|
|
)
|
|
INSERT INTO T_TANITASIORADIGTAMESZKOZ (
|
|
C_DIGTAMESZKOZTIPUSID
|
|
,C_TANITASIORAID
|
|
,C_INTEZMENYID
|
|
,C_TANEVID
|
|
,TOROLT
|
|
,SERIAL
|
|
,LASTCHANGED
|
|
,CREATED
|
|
,MODIFIER
|
|
,CREATOR
|
|
,ELOZOTANEVIREKORDID
|
|
)
|
|
SELECT
|
|
8600
|
|
,tanitasiOraId
|
|
,intezmenyId
|
|
,tanevId
|
|
,'F'
|
|
,0
|
|
,GETDATE()
|
|
,GETDATE()
|
|
,0
|
|
,0
|
|
,NULL
|
|
FROM cte
|