98 lines
2.0 KiB
Transact-SQL
98 lines
2.0 KiB
Transact-SQL
-- Típusos tábla nélkül felvett DictionaryItemek töröltre állítása a 2019/2020 tanévben
|
|
|
|
DROP TABLE IF EXISTS #notok
|
|
DROP TABLE IF EXISTS #alTablak
|
|
DROP TABLE IF EXISTS #result
|
|
|
|
CREATE TABLE #notok (
|
|
C_TYPE nvarchar(255)
|
|
,dibDB int
|
|
,typeDB int
|
|
)
|
|
|
|
CREATE TABLE #alTablak (
|
|
C_TYPE nvarchar(255)
|
|
,typeDB int
|
|
)
|
|
|
|
CREATE TABLE #result (
|
|
dibId int
|
|
,C_TANEVID int
|
|
,C_TYPE nvarchar(255) COLLATE DATABASE_DEFAULT
|
|
)
|
|
|
|
DECLARE @sql nvarchar(max)=N''
|
|
|
|
SET @sql = N'
|
|
SELECT
|
|
C_TYPE
|
|
,COUNT(*) AS dibDB
|
|
,NULL
|
|
FROM T_DICTIONARYITEMBASE dib
|
|
INNER JOIN T_TANEV tn ON tn.ID = dib.C_TANEVID
|
|
AND tn.TOROLT = ''F''
|
|
AND tn.C_AKTIV = ''T''
|
|
AND tn.C_NEV = ''2019/2020''
|
|
WHERE dib.TOROLT =''F''
|
|
GROUP BY dib.C_TYPE'
|
|
|
|
INSERT INTO #notok
|
|
EXEC sp_executesql @sql
|
|
|
|
DELETE
|
|
FROM #notok
|
|
WHERE C_TYPE IN ('ImpositionStatus', 'PartfillStatus', 'PayingType', 'TransactionStatus')
|
|
|
|
SET @sql =N''
|
|
|
|
SELECT @sql += N'
|
|
SELECT
|
|
''' + C_TYPE + ''' C_TYPE
|
|
,COUNT(*)
|
|
FROM T_'+C_TYPE+' t
|
|
INNER JOIN T_TANEV tn ON tn.ID = t.C_ALTANEVID
|
|
AND tn.TOROLT = ''F''
|
|
AND tn.C_AKTIV = ''T''
|
|
AND tn.C_NEV = ''2019/2020''
|
|
WHERE t.TOROLT=''F'''
|
|
FROM #notok
|
|
|
|
INSERT INTO #alTablak
|
|
EXEC sp_executesql @sql
|
|
|
|
UPDATE t1
|
|
SET typeDB = t2.typeDB
|
|
FROM #notok t1
|
|
INNER JOIN #alTablak t2 ON t1.C_TYPE = t2.C_TYPE
|
|
|
|
SET @sql = N''
|
|
|
|
SELECT @sql +=N'
|
|
SELECT
|
|
dib.ID
|
|
,dib.C_TANEVID
|
|
,dib.C_TYPE
|
|
FROM T_DICTIONARYITEMBASE dib
|
|
INNER JOIN T_TANEV tn ON tn.ID = dib.C_TANEVID
|
|
AND tn.TOROLT = ''F''
|
|
AND tn.C_AKTIV = ''T''
|
|
AND tn.C_NEV = ''2019/2020''
|
|
LEFT JOIN T_' + C_TYPE + ' t ON t.ID = dib.ID
|
|
AND t.C_ALTANEVID = tn.ID
|
|
AND t.TOROLT = ''F''
|
|
WHERE t.ID IS NULL
|
|
AND dib.C_TYPE = ''' + C_TYPE + '''
|
|
AND dib.TOROLT=''F'''
|
|
FROM #notok
|
|
WHERE dibDB <> typeDB
|
|
|
|
INSERT INTO #result
|
|
EXEC sp_executesql @sql
|
|
|
|
UPDATE dib
|
|
SET TOROLT='T'
|
|
FROM #result r
|
|
INNER JOIN T_DICTIONARYITEMBASE dib ON dib.ID = r.dibId
|
|
AND dib.C_TANEVID = r.C_TANEVID
|
|
AND dib.C_TYPE = r.C_TYPE
|