/* BENUTZER */
DECLARE @Benutzer int;
SET @Benutzer = 14;

/* Deklarationsblock */
DECLARE @sxtng_Dauer_H int;
DECLARE @sxtng_Dauer_M int;
DECLARE @sxtng_Dauer_L int;
DECLARE @bda_Dauer_H int;
DECLARE @bda_Dauer_M int;
DECLARE @bda_Dauer_L int;
DECLARE @liu_Dauer_H int;
DECLARE @liu_Dauer_M int;
DECLARE @liu_Dauer_L int;

/* Festlegen der Werte für die Variablen */
/* H = High; M = Medium; L = Low */

/* Erlaubte Dauer bei x Tagen nicht geändert nach Prio: */
SET @sxtng_Dauer_H = 1;
SET @sxtng_Dauer_M = 4;
SET @sxtng_Dauer_L = 10;
/* Beginn der Arbeit */
SET @bda_Dauer_H = 0;
SET @bda_Dauer_M = 2;
SET @bda_Dauer_L = 6;
/* Lösung ist überfällig */
SET @liu_Dauer_H = 3;
SET @liu_Dauer_M = 5;
SET @liu_Dauer_L = 20;

/* Abfrage Seit X Tagen nicht geändert */
SELECT T0.[callID] as TicketNr, T0.[subject] as Betreff, T0.[custmrName] as Kunde,T1.[U_NAME] as Bearbeiter, T0.[priority] as Priorität, 'Seit mehreren Tagen nicht geändert' as Grund, DATEDIFF(DAY, T0.[updateDate], GetDate()) - DATEDIFF(wk, T0.[updateDate], GetDate()) % 7 * 2 as 'Tage in Alarmzustand'
FROM OSCL T0
INNER JOIN OUSR T1 ON T0.[assignee] = T1.[USERID]
WHERE (T0.[priority] = 'L' AND DATEDIFF(DAY, T0.[updateDate], GetDate()) - DATEDIFF(wk, T0.[updateDate], GetDate()) % 7 * 2 > @sxtng_Dauer_L) AND T0.[assignee] = @Benutzer AND T0.[status] != '-1'
OR (T0.[priority] = 'M' AND DATEDIFF(DAY, T0.[updateDate], GetDate()) - DATEDIFF(wk, T0.[updateDate], GetDate()) % 7 * 2 > @sxtng_Dauer_M) AND T0.[assignee] = @Benutzer AND T0.[status] != '-1'
OR (T0.[priority] = 'H' AND DATEDIFF(DAY, T0.[updateDate], GetDate()) - DATEDIFF(wk, T0.[updateDate], GetDate()) % 7 * 2 > @sxtng_Dauer_H) AND T0.[assignee] = @Benutzer AND T0.[status] != '-1'

UNION ALL /* Zusammenfügen */

/* Beginn der Arbeit */
SELECT T0.[callID] as TicketNr, T0.[subject] as Betreff, T0.[custmrName] as Kunde, T1.[U_NAME] as Bearbeiter , T0.[priority] as Priorität, 'Arbeit noch nicht begonnen' as Grund, DATEDIFF(DAY, T0.[createDate], GetDate()) - DATEDIFF(wk, T0.[createDate], GetDate()) % 7 * 2 as 'Tage in Alarmzustand'
FROM OSCL T0
INNER JOIN OUSR T1 ON T0.[assignee] = T1.[USERID]
WHERE (T0.[priority] = 'L' AND T0.[status] = '-3' AND DATEDIFF(DAY, T0.[createDate], GetDate()) - DATEDIFF(wk, T0.[createDate], GetDate()) % 7 * 2 > @bda_Dauer_L) AND T0.[assignee] = @Benutzer
OR (T0.[priority] = 'M' AND T0.[status] = '-3' AND DATEDIFF(DAY, T0.[createDate], GetDate()) - DATEDIFF(wk, T0.[createDate], GetDate()) % 7 * 2 > @bda_Dauer_M) AND T0.[assignee] = @Benutzer
OR (T0.[priority] = 'H' AND T0.[status] = '-3' AND DATEDIFF(DAY, T0.[createDate], GetDate()) - DATEDIFF(wk, T0.[createDate], GetDate()) % 7 * 2 > @bda_Dauer_H) AND T0.[assignee] = @Benutzer

UNION ALL /* Zusammenfügen */

/* Nächster Kontakt Heute oder Überfällig */
SELECT T0.[callID] as TicketNr, T0.[subject] as Betreff, T0.[custmrName] as Kunde,T1.[U_NAME] as Bearbeiter, T0.[priority] as Priorität, 'Kontakt heute oder überfällig' as Grund, DATEDIFF(DAY, T0.U_nContact, GetDate()) as 'Tage in Alarmzustand'
FROM OSCL T0
INNER JOIN OUSR T1 ON T0.[assignee] = T1.[USERID]
WHERE (T0.U_nContact <= GetDate()) AND T0.[assignee] = @Benutzer AND T0.[status] != '-1'

UNION ALL /* Zusammenfügen */

/* Lösung ist überfällig */
SELECT T0.[callID] as TicketNr, T0.[subject] as Betreff, T0.[custmrName] as Kunde, T1.[U_NAME] as Bearbeiter, T0.[priority] as Priorität, 'Lösung überfällig' as Grund, DATEDIFF(DAY, T0.[createDate], GetDate()) - DATEDIFF(wk, T0.[createDate], GetDate()) % 7 * 2 as 'Tage in Alarmzustand'
FROM OSCL T0
INNER JOIN OUSR T1 ON T0.[assignee] = T1.[USERID]
WHERE (T0.[priority] = 'L' AND DATEDIFF(Day, T0.createDate, GetDate()) - DATEDIFF(wk, T0.[createDate], GetDate()) % 7 * 2 > @liu_Dauer_L) AND T0.[assignee] = @Benutzer AND T0.[status] != '-1'
OR (T0.[priority] = 'M' AND DATEDIFF(Day, T0.createDate, GetDate()) - DATEDIFF(wk, T0.[createDate], GetDate()) % 7 * 2 > @liu_Dauer_M) AND T0.[assignee] = @Benutzer AND T0.[status] != '-1'
OR (T0.[priority] = 'H' AND DATEDIFF(Day, T0.createDate, GetDate()) - DATEDIFF(wk, T0.[createDate], GetDate()) % 7 * 2 > @liu_Dauer_H) AND T0.[assignee] = @Benutzer AND T0.[status] != '-1'
ORDER BY T0.[callID]