44 lines
2.8 KiB
SQL
44 lines
2.8 KiB
SQL
|
|
--1. lépés: a hibás tanévû záradékokkal együtt létrejött következõ tanévû záradékok törlése
|
|
update z1
|
|
set TOROLT = 'T', MODIFIER = 0, LASTCHANGED = GETDATE(), SERIAL += 1
|
|
--select *
|
|
from T_ZARADEK z1
|
|
inner join T_TANULOCSOPORT tcs1 on tcs1.ID = z1.C_TANULOCSOPORTID and tcs1.TOROLT = 'F'
|
|
inner join T_TANULO t1 on t1.ID = tcs1.C_TANULOID and t1.TOROLT = 'F'
|
|
--következõ tanévID-s, de idei tanévIS-s tanulócsoporthoz tartozó Záradékok kapcsolása
|
|
inner join (select z2.ID as ZarID, z2.C_TANEVID as ZarTanevID, z2.C_SZOVEG as ZarSzoveg, z2.CREATED as ZarCr, tcs2.ID TcsID, tcs2.C_TANEVID as TcsTanevID, t2.ID as TanuloID
|
|
from T_ZARADEK z2
|
|
inner join T_TANEV te2 on te2.ID = z2.C_TANEVID and te2.TOROLT = 'F' and te2.C_KOVETKEZO = 'T'
|
|
inner join T_TANULOCSOPORT tcs2 on tcs2.ID = z2.C_TANULOCSOPORTID and tcs2.TOROLT = 'F'
|
|
inner join T_TANULO t2 on t2.ID = tcs2.C_TANULOID and t2.TOROLT = 'F'
|
|
where z2.TOROLT = 'F' and tcs2.C_TANEVID <> z2.C_TANEVID
|
|
) x on t1.ELOZOTANEVIREKORDID = x.TanuloID
|
|
and z1.C_SZOVEG = x.ZarSzoveg
|
|
and z1.Created between DATEADD(s, -3, x.ZarCr) and DATEADD(s, +3, x.ZarCr)
|
|
|
|
--2. lépés: a hibás tanévû záradékok tanévének javítása, ha NINCS a létrehozás után új záradék a tanulónak
|
|
update z2
|
|
set C_TANEVID = tcs2.C_TANEVID, MODIFIER = 0, LASTCHANGED = GETDATE(), SERIAL += 1
|
|
--select z2.ID as ZarID, z2.C_TANEVID as ZarTanevID, z2.C_SZOVEG as ZarSzoveg, z2.CREATED as ZarCr, tcs2.ID TcsID, tcs2.C_TANEVID as TcsTanevID--, t2.ID as TanuloID
|
|
from T_ZARADEK z2
|
|
inner join T_TANEV te2 on te2.ID = z2.C_TANEVID and te2.TOROLT = 'F' and te2.C_KOVETKEZO = 'T'
|
|
inner join T_TANULOCSOPORT tcs2 on tcs2.ID = z2.C_TANULOCSOPORTID and tcs2.TOROLT = 'F'
|
|
--inner join T_TANULO t2 on t2.ID = tcs2.C_TANULOID and t2.TOROLT = 'F'
|
|
where z2.TOROLT = 'F' and tcs2.C_TANEVID <> z2.C_TANEVID
|
|
and not exists (select 1
|
|
from T_ZARADEK z3
|
|
where z3.C_TANULOCSOPORTID = z2.C_TANULOCSOPORTID and z3.CREATED > z2.CREATED
|
|
)
|
|
|
|
--3. lépés: a hibás tanévû záradékokból megmaradak törlése
|
|
update z2
|
|
set TOROLT = 'T', MODIFIER = 0, LASTCHANGED = GETDATE(), SERIAL += 1
|
|
--select z2.ID as ZarID, z2.C_TANEVID as ZarTanevID, z2.C_SZOVEG as ZarSzoveg, z2.CREATED as ZarCr, tcs2.ID TcsID, tcs2.C_TANEVID as TcsTanevID--, t2.ID as TanuloID
|
|
from T_ZARADEK z2
|
|
inner join T_TANEV te2 on te2.ID = z2.C_TANEVID and te2.TOROLT = 'F' and te2.C_KOVETKEZO = 'T'
|
|
inner join T_TANULOCSOPORT tcs2 on tcs2.ID = z2.C_TANULOCSOPORTID and tcs2.TOROLT = 'F'
|
|
--inner join T_TANULO t2 on t2.ID = tcs2.C_TANULOID and t2.TOROLT = 'F'
|
|
where z2.TOROLT = 'F' and tcs2.C_TANEVID <> z2.C_TANEVID
|
|
|