472 lines
No EOL
11 KiB
SQL
472 lines
No EOL
11 KiB
SQL
-- =================================
|
|
-- Gondviselők, akiknek van egynél több azonos email cím vagy telefonszám adatuk
|
|
-- =================================
|
|
|
|
UPDATE E
|
|
SET
|
|
Torolt = 'T'
|
|
,Modifier = 0
|
|
,Lastchanged = GETDATE()
|
|
,Serial += 1
|
|
FROM
|
|
T_Email E
|
|
INNER JOIN (
|
|
SELECT
|
|
Id
|
|
,ROW_NUMBER() OVER (PARTITION BY C_Emailcim, C_Emailtipusa, C_Felhasznaloid, C_Gondviseloid, C_Intezmenyid, C_Tanevid ORDER BY C_Alapertelmezett DESC, Created DESC) AS Rn
|
|
FROM
|
|
T_Email E
|
|
WHERE
|
|
E.Torolt = 'F'
|
|
) X ON X.Id = E.Id
|
|
WHERE
|
|
X.Rn > 1
|
|
AND E.C_Gondviseloid IS NOT NULL
|
|
AND NOT EXISTS(SELECT 1 FROM T_Alkalmazott A WHERE A.Id = E.C_Felhasznaloid)
|
|
|
|
UPDATE T
|
|
SET
|
|
Torolt = 'T'
|
|
,Modifier = 0
|
|
,Lastchanged = GETDATE()
|
|
,Serial += 1
|
|
FROM
|
|
T_Telefon T
|
|
INNER JOIN (
|
|
SELECT
|
|
Id
|
|
,ROW_NUMBER() OVER (PARTITION BY C_Telefonszam, C_Telefontipusa, C_Felhasznaloid, C_Gondviseloid, C_Intezmenyid, C_Tanevid ORDER BY C_Alapertelmezett DESC, Created DESC) AS Rn
|
|
FROM
|
|
T_Telefon T
|
|
WHERE
|
|
T.Torolt = 'F'
|
|
) X ON X.Id = T.Id
|
|
WHERE
|
|
X.Rn > 1
|
|
AND T.C_Gondviseloid IS NOT NULL
|
|
AND NOT EXISTS(SELECT 1 FROM T_Alkalmazott A WHERE A.Id = T.C_Felhasznaloid)
|
|
|
|
|
|
-- =================================
|
|
-- Gondviselők, akiknek van egynél több (nem feltétlenül) azonos email cím vagy telefonszám adatuk
|
|
-- =================================
|
|
|
|
UPDATE E
|
|
SET
|
|
Torolt = 'T'
|
|
,Modifier = 0
|
|
,Lastchanged = GETDATE()
|
|
,Serial += 1
|
|
FROM
|
|
T_Email E
|
|
INNER JOIN (
|
|
SELECT
|
|
Id
|
|
,ROW_NUMBER() OVER (PARTITION BY C_Felhasznaloid, C_Gondviseloid, C_Intezmenyid, C_Tanevid ORDER BY C_Alapertelmezett DESC, Created DESC) AS Rn
|
|
FROM
|
|
T_Email E
|
|
WHERE
|
|
E.Torolt = 'F'
|
|
) X ON X.Id = E.Id
|
|
WHERE
|
|
X.Rn > 1
|
|
AND E.C_Gondviseloid IS NOT NULL
|
|
|
|
UPDATE T
|
|
SET
|
|
Torolt = 'T'
|
|
,Modifier = 0
|
|
,Lastchanged = GETDATE()
|
|
,Serial += 1
|
|
FROM
|
|
T_Telefon T
|
|
INNER JOIN (
|
|
SELECT
|
|
Id
|
|
,ROW_NUMBER() OVER (PARTITION BY C_Felhasznaloid, C_Gondviseloid, C_Intezmenyid, C_Tanevid ORDER BY C_Alapertelmezett DESC, Created DESC) AS Rn
|
|
FROM
|
|
T_Telefon T
|
|
WHERE
|
|
T.Torolt = 'F'
|
|
) X ON X.Id = T.Id
|
|
WHERE
|
|
X.Rn > 1
|
|
AND T.C_Gondviseloid IS NOT NULL
|
|
|
|
|
|
-- =================================
|
|
-- Gondviselők, akiknek nincs alapértelmezett email cím vagy telefonszám adatuk
|
|
-- =================================
|
|
|
|
UPDATE E
|
|
SET
|
|
C_Alapertelmezett = 'T'
|
|
,Modifier = 0
|
|
,Lastchanged = GETDATE()
|
|
,Serial += 1
|
|
FROM
|
|
T_Email E
|
|
WHERE
|
|
E.C_Gondviseloid IS NOT NULL
|
|
AND E.C_Alapertelmezett = 'F'
|
|
AND E.Torolt = 'F'
|
|
|
|
UPDATE T
|
|
SET
|
|
C_Alapertelmezett = 'T'
|
|
,Modifier = 0
|
|
,Lastchanged = GETDATE()
|
|
,Serial += 1
|
|
FROM
|
|
T_Telefon T
|
|
WHERE
|
|
T.C_Gondviseloid IS NOT NULL
|
|
AND T.C_Alapertelmezett = 'F'
|
|
AND T.Torolt = 'F'
|
|
|
|
-- =================================
|
|
-- Tanulók, akiknek van egynél több azonos email cím vagy telefonszám adatuk
|
|
-- =================================
|
|
|
|
UPDATE E
|
|
SET
|
|
Torolt = 'T'
|
|
,Modifier = 0
|
|
,Lastchanged = GETDATE()
|
|
,Serial += 1
|
|
FROM
|
|
T_Email E
|
|
INNER JOIN (
|
|
SELECT
|
|
Id
|
|
,ROW_NUMBER() OVER (PARTITION BY C_Emailcim, C_Felhasznaloid, C_Gondviseloid, C_Intezmenyid, C_Tanevid ORDER BY C_Alapertelmezett DESC, Created DESC) AS Rn
|
|
FROM
|
|
T_Email E
|
|
WHERE
|
|
E.Torolt = 'F'
|
|
) X ON X.Id = E.Id
|
|
|
|
WHERE
|
|
X.Rn > 1
|
|
AND E.C_Gondviseloid IS NULL
|
|
AND EXISTS(SELECT 1 FROM T_Tanulo Ta WHERE Ta.Id = E.C_Felhasznaloid)
|
|
|
|
UPDATE T
|
|
SET
|
|
Torolt = 'T'
|
|
,Modifier = 0
|
|
,Lastchanged = GETDATE()
|
|
,Serial += 1
|
|
FROM
|
|
T_Telefon T
|
|
INNER JOIN (
|
|
SELECT
|
|
Id
|
|
,ROW_NUMBER() OVER (PARTITION BY C_Telefonszam, C_Felhasznaloid, C_Gondviseloid, C_Intezmenyid, C_Tanevid ORDER BY C_Alapertelmezett DESC, Created DESC) AS Rn
|
|
FROM
|
|
T_Telefon T
|
|
WHERE
|
|
T.Torolt = 'F'
|
|
) X ON X.Id = T.Id
|
|
WHERE
|
|
X.Rn > 1
|
|
AND T.C_Gondviseloid IS NULL
|
|
AND EXISTS(SELECT 1 FROM T_Tanulo Ta WHERE Ta.Id = T.C_Felhasznaloid)
|
|
|
|
|
|
-- =================================
|
|
-- Tanulók, akiknek nincs alapértelmezett email cím vagy telefonszám adatuk
|
|
-- =================================
|
|
|
|
UPDATE E
|
|
SET
|
|
C_Alapertelmezett = 'T'
|
|
,Modifier = 0
|
|
,Lastchanged = GETDATE()
|
|
,Serial += 1
|
|
FROM
|
|
T_Email E
|
|
INNER JOIN (
|
|
SELECT
|
|
E.Id
|
|
,ROW_NUMBER () OVER (PARTITION BY e.C_FELHASZNALOID, e.C_INTEZMENYID, e.C_TANEVID ORDER BY e.CREATED DESC) RN
|
|
FROM
|
|
T_Tanulo Ta
|
|
INNER JOIN T_Email E ON E.C_Felhasznaloid = Ta.Id
|
|
WHERE
|
|
E.C_Gondviseloid IS NULL
|
|
AND NOT EXISTS(
|
|
SELECT TOP 1 1
|
|
FROM T_Email E2
|
|
WHERE E2.C_Felhasznaloid = Ta.Id AND E2.C_Gondviseloid IS NULL AND E2.Torolt = 'F' AND E2.C_Alapertelmezett = 'T')
|
|
) X ON X.Id = E.Id
|
|
WHERE E.Torolt = 'F' AND X.RN = 1
|
|
|
|
UPDATE T
|
|
SET
|
|
C_Alapertelmezett = 'T'
|
|
,Modifier = 0
|
|
,Lastchanged = GETDATE()
|
|
,Serial += 1
|
|
FROM
|
|
T_Telefon T
|
|
INNER JOIN (
|
|
SELECT
|
|
T.Id
|
|
,ROW_NUMBER () over (partition by t.C_FELHASZNALOID, t.C_INTEZMENYID, t.C_TANEVID ORDER BY t.CREATED DESC) RN
|
|
FROM
|
|
T_Tanulo Ta
|
|
INNER JOIN T_Telefon T ON T.C_Felhasznaloid = Ta.Id
|
|
WHERE
|
|
T.C_Gondviseloid IS NULL
|
|
AND NOT EXISTS(
|
|
SELECT TOP 1 1
|
|
FROM T_Telefon T2
|
|
WHERE T2.C_Felhasznaloid = Ta.Id AND T2.C_Alapertelmezett = 'T' AND T2.C_Gondviseloid IS NULL AND T2.Torolt = 'F')
|
|
) X ON X.Id = T.Id
|
|
WHERE T.Torolt = 'F' AND X.RN = 1
|
|
|
|
|
|
-- =================================
|
|
-- Tanulók, akiknek több alapértelmezett email cím vagy telefonszám adatuk van
|
|
-- =================================
|
|
|
|
UPDATE E
|
|
SET
|
|
C_Alapertelmezett = 'F'
|
|
,Modifier = 0
|
|
,Lastchanged = GETDATE()
|
|
,Serial += 1
|
|
FROM
|
|
T_Email E
|
|
INNER JOIN (
|
|
SELECT
|
|
Id
|
|
,ROW_NUMBER() OVER (PARTITION BY C_Felhasznaloid, C_Gondviseloid, C_Intezmenyid, C_Tanevid ORDER BY Created DESC) AS Rn
|
|
FROM
|
|
T_Email E
|
|
WHERE
|
|
E.Torolt = 'F'
|
|
AND E.C_Alapertelmezett = 'T'
|
|
) X ON X.Id = E.Id
|
|
|
|
WHERE
|
|
X.Rn > 1
|
|
AND E.C_Gondviseloid IS NULL
|
|
AND EXISTS(SELECT 1 FROM T_Tanulo Ta WHERE Ta.Id = E.C_Felhasznaloid)
|
|
|
|
UPDATE T
|
|
SET
|
|
C_Alapertelmezett = 'F'
|
|
,Modifier = 0
|
|
,Lastchanged = GETDATE()
|
|
,Serial += 1
|
|
FROM
|
|
T_Telefon T
|
|
INNER JOIN (
|
|
SELECT
|
|
Id
|
|
,ROW_NUMBER() OVER (PARTITION BY C_Felhasznaloid, C_Gondviseloid, C_Intezmenyid, C_Tanevid ORDER BY Created DESC) AS Rn
|
|
FROM
|
|
T_Telefon T
|
|
WHERE
|
|
T.Torolt = 'F'
|
|
AND T.C_Alapertelmezett = 'T'
|
|
) X ON X.Id = T.Id
|
|
WHERE
|
|
X.Rn > 1
|
|
AND T.C_Gondviseloid IS NULL
|
|
AND EXISTS(SELECT 1 FROM T_Tanulo Ta WHERE Ta.Id = T.C_Felhasznaloid)
|
|
|
|
|
|
-- =================================
|
|
-- Alkalmazott, akiknek van egynél több azonos email cím vagy telefonszám adatuk (de az NEM publikus!)
|
|
-- =================================
|
|
|
|
UPDATE E
|
|
SET
|
|
Torolt = 'T'
|
|
,Modifier = 0
|
|
,Lastchanged = GETDATE()
|
|
,Serial += 1
|
|
FROM
|
|
T_Email E
|
|
INNER JOIN (
|
|
SELECT
|
|
Id
|
|
,ROW_NUMBER() OVER (PARTITION BY C_Emailcim, C_Felhasznaloid, C_Gondviseloid, C_Intezmenyid, C_Tanevid ORDER BY C_Alapertelmezett DESC, Created DESC) AS Rn
|
|
FROM
|
|
T_Email E
|
|
WHERE
|
|
E.Torolt = 'F'
|
|
AND E.C_Ispublic = 'F'
|
|
) X ON X.Id = E.Id
|
|
|
|
WHERE
|
|
X.Rn > 1
|
|
AND E.C_Gondviseloid IS NULL
|
|
AND EXISTS(SELECT 1 FROM T_Alkalmazott A WHERE A.Id = E.C_Felhasznaloid)
|
|
|
|
UPDATE T
|
|
SET
|
|
Torolt = 'T'
|
|
,Modifier = 0
|
|
,Lastchanged = GETDATE()
|
|
,Serial += 1
|
|
FROM
|
|
T_Telefon T
|
|
INNER JOIN (
|
|
SELECT
|
|
Id
|
|
,ROW_NUMBER() OVER (PARTITION BY C_Telefonszam, C_Felhasznaloid, C_Gondviseloid, C_Intezmenyid, C_Tanevid ORDER BY C_Alapertelmezett DESC, Created DESC) AS Rn
|
|
FROM
|
|
T_Telefon T
|
|
WHERE
|
|
T.Torolt = 'F'
|
|
AND T.C_Ispublic = 'F'
|
|
) X ON X.Id = T.Id
|
|
WHERE
|
|
X.Rn > 1
|
|
AND T.C_Gondviseloid IS NULL
|
|
AND EXISTS(SELECT 1 FROM T_Alkalmazott A WHERE A.Id = T.C_Felhasznaloid)
|
|
|
|
|
|
-- =================================
|
|
-- Alkalmazott, akiknek az alapértelmezett email cím vagy telefonszám adatuk egyben publikus is
|
|
-- =================================
|
|
|
|
UPDATE E
|
|
SET
|
|
C_Alapertelmezett = 'F'
|
|
,Modifier = 0
|
|
,Lastchanged = GETDATE()
|
|
,Serial += 1
|
|
FROM
|
|
T_Email E
|
|
WHERE
|
|
E.C_Alapertelmezett = 'T'
|
|
AND E.C_Ispublic = 'T'
|
|
AND E.Torolt = 'F'
|
|
|
|
UPDATE T
|
|
SET
|
|
C_Alapertelmezett = 'F'
|
|
,Modifier = 0
|
|
,Lastchanged = GETDATE()
|
|
,Serial += 1
|
|
FROM
|
|
T_Telefon T
|
|
WHERE
|
|
T.C_Alapertelmezett = 'T'
|
|
AND T.C_Ispublic = 'T'
|
|
AND T.Torolt = 'F'
|
|
|
|
|
|
-- =================================
|
|
-- Alkalmazott, akiknek több alapértelmezett email cím vagy telefonszám adatuk van
|
|
-- =================================
|
|
|
|
UPDATE E
|
|
SET
|
|
C_Alapertelmezett = 'F'
|
|
,Modifier = 0
|
|
,Lastchanged = GETDATE()
|
|
,Serial += 1
|
|
FROM
|
|
T_Email E
|
|
INNER JOIN (
|
|
SELECT
|
|
Id
|
|
,Torolt
|
|
,ROW_NUMBER() OVER (PARTITION BY C_Felhasznaloid, C_Gondviseloid, C_Intezmenyid, C_Tanevid ORDER BY Created DESC) AS Rn
|
|
FROM
|
|
T_Email E
|
|
WHERE
|
|
E.Torolt = 'F'
|
|
AND E.C_Alapertelmezett = 'T'
|
|
) X ON X.Id = E.Id
|
|
|
|
WHERE
|
|
X.Rn > 1
|
|
AND E.C_Gondviseloid IS NULL
|
|
AND EXISTS(SELECT 1 FROM T_Alkalmazott A WHERE A.Id = E.C_Felhasznaloid)
|
|
|
|
UPDATE T
|
|
SET
|
|
C_Alapertelmezett = 'T'
|
|
,Modifier = 0
|
|
,Lastchanged = GETDATE()
|
|
,Serial += 1
|
|
FROM
|
|
T_Telefon T
|
|
INNER JOIN (
|
|
SELECT
|
|
Id
|
|
,ROW_NUMBER() OVER (PARTITION BY C_Felhasznaloid, C_Gondviseloid, C_Intezmenyid, C_Tanevid ORDER BY Created DESC) AS Rn
|
|
FROM
|
|
T_Telefon T
|
|
WHERE
|
|
T.Torolt = 'F'
|
|
AND T.C_Alapertelmezett = 'T'
|
|
) X ON X.Id = T.Id
|
|
WHERE
|
|
X.Rn > 1
|
|
AND T.C_Gondviseloid IS NULL
|
|
AND EXISTS(SELECT 1 FROM T_Alkalmazott A WHERE A.Id = T.C_Felhasznaloid)
|
|
|
|
|
|
-- =================================
|
|
-- Alkalmazott, akiknek nincs alapértelmezett email cím vagy telefonszám adatuk
|
|
-- =================================
|
|
|
|
UPDATE E
|
|
SET
|
|
C_Alapertelmezett = 'T'
|
|
,Modifier = 0
|
|
,Lastchanged = GETDATE()
|
|
,Serial += 1
|
|
FROM
|
|
T_Email E
|
|
INNER JOIN (
|
|
SELECT
|
|
E.Id
|
|
,ROW_NUMBER () OVER (PARTITION BY e.C_FELHASZNALOID, e.C_INTEZMENYID, e.C_TANEVID ORDER BY e.CREATED DESC) RN
|
|
FROM
|
|
T_Alkalmazott A
|
|
INNER JOIN T_Email E ON E.C_Felhasznaloid = A.Id
|
|
WHERE
|
|
A.Torolt = 'F'
|
|
AND E.C_Gondviseloid IS NULL
|
|
AND E.C_Ispublic = 'F'
|
|
AND NOT EXISTS(
|
|
SELECT TOP 1 1
|
|
FROM T_Email E2
|
|
WHERE E2.C_Felhasznaloid = A.Id AND E2.C_Alapertelmezett = 'T' AND E2.C_Gondviseloid IS NULL AND E2.Torolt = 'F')
|
|
) X ON X.Id = E.Id
|
|
WHERE E.Torolt = 'F' AND X.RN = 1
|
|
|
|
UPDATE T
|
|
SET
|
|
C_Alapertelmezett = 'T'
|
|
,Modifier = 0
|
|
,Lastchanged = GETDATE()
|
|
,Serial += 1
|
|
FROM
|
|
T_Telefon T
|
|
INNER JOIN (
|
|
SELECT
|
|
T.Id
|
|
,ROW_NUMBER () OVER (PARTITION BY t.C_FELHASZNALOID, t.C_INTEZMENYID, t.C_TANEVID ORDER BY t.CREATED DESC) RN
|
|
FROM
|
|
T_Alkalmazott A
|
|
INNER JOIN T_Telefon T ON T.C_Felhasznaloid = A.Id
|
|
WHERE
|
|
A.Torolt = 'F'
|
|
AND T.C_Ispublic = 'F'
|
|
AND T.C_Gondviseloid IS NULL
|
|
AND NOT EXISTS(
|
|
SELECT TOP 1 1
|
|
FROM T_Telefon T2
|
|
WHERE T2.C_Felhasznaloid = A.Id AND T2.C_Alapertelmezett = 'T' AND T2.Torolt = 'F' AND T2.C_Gondviseloid IS NULL)
|
|
) X ON X.Id = T.Id
|
|
WHERE T.Torolt = 'F' AND X.RN = 1 |