You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
212 lines
7.4 KiB
212 lines
7.4 KiB
USE [Vertragsverwaltung]
|
|
GO
|
|
|
|
/****** Object: View [dbo].[TXP_IN_Geschlossen] Script Date: 23.04.2013 08:08:10 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
CREATE VIEW [dbo].[TXP_IN_Geschlossen]
|
|
AS
|
|
SELECT TOP (100) PERCENT TicketID, MAX(Status_Changedate) AS Status_Changedate, TGNummer
|
|
FROM dbo.TXP_Ticket_Data
|
|
WHERE (Status_Neu = 'Geschlossen' OR
|
|
Status_Neu = 'Geschlossen "Autoclose"' OR
|
|
Status_Neu = 'Warten auf Schliessung' OR
|
|
Status_Neu = 'Solved') AND (TicketID LIKE 'in%')
|
|
GROUP BY TicketID, TGNummer
|
|
HAVING (NOT (TGNummer IS NULL))
|
|
|
|
GO
|
|
|
|
/****** Object: View [dbo].[TXP_IN_Offene_Langlaeufer] Script Date: 23.04.2013 08:08:10 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
CREATE VIEW [dbo].[TXP_IN_Offene_Langlaeufer]
|
|
AS
|
|
SELECT TOP (100) PERCENT TicketID, MAX(Status_Changedate) AS Status_Changedate, TGNummer
|
|
FROM dbo.TXP_Ticket_Data
|
|
WHERE (Status_Neu <> 'Geschlossen') AND (Status_Neu <> 'Geschlossen "Autoclose"') AND (TicketID LIKE 'in%') AND (Status_Neu <> 'Warten auf Schliessung') AND
|
|
(Status_Neu <> 'Solved') AND (DATEDIFF(d, Erstellungsdatum, GETDATE()) > 120) AND (TicketID NOT IN
|
|
(SELECT TicketID
|
|
FROM dbo.TXP_IN_Geschlossen))
|
|
GROUP BY TicketID, TGNummer
|
|
HAVING (NOT (TGNummer IS NULL))
|
|
|
|
GO
|
|
|
|
/****** Object: View [dbo].[TXP_IN_Geschlossen_Aufwand] Script Date: 23.04.2013 08:08:10 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
CREATE VIEW [dbo].[TXP_IN_Geschlossen_Aufwand]
|
|
AS
|
|
SELECT TOP (100) PERCENT dbo.TXP_IN_Geschlossen.TGNummer, CONVERT(float, SUM(dbo.TXP_LVer_IN_AU.CalculatedValue)) AS Aufwand,
|
|
dbo.TXP_IN_Geschlossen.Status_Changedate
|
|
FROM dbo.TXP_IN_Geschlossen INNER JOIN
|
|
dbo.TXP_LVer_IN_AU ON dbo.TXP_IN_Geschlossen.TicketID = dbo.TXP_LVer_IN_AU.Ticket_ID AND
|
|
dbo.TXP_IN_Geschlossen.TGNummer = dbo.TXP_LVer_IN_AU.ForUserID
|
|
GROUP BY dbo.TXP_IN_Geschlossen.TGNummer, dbo.TXP_IN_Geschlossen.Status_Changedate
|
|
ORDER BY dbo.TXP_IN_Geschlossen.TGNummer
|
|
|
|
GO
|
|
|
|
/****** Object: View [dbo].[TXP_IN_Geschlossen_IN_Aufwand] Script Date: 23.04.2013 08:08:10 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
CREATE VIEW [dbo].[TXP_IN_Geschlossen_IN_Aufwand]
|
|
AS
|
|
SELECT TOP (100) PERCENT dbo.TXP_IN_Geschlossen.TGNummer, CONVERT(float, dbo.TXP_LVer_IN_AU.CalculatedValue) AS Aufwand,
|
|
dbo.TXP_IN_Geschlossen.Status_Changedate, dbo.TXP_LVer_IN_AU.Ticket_ID
|
|
FROM dbo.TXP_IN_Geschlossen INNER JOIN
|
|
dbo.TXP_LVer_IN_AU ON dbo.TXP_IN_Geschlossen.TicketID = dbo.TXP_LVer_IN_AU.Ticket_ID AND
|
|
dbo.TXP_IN_Geschlossen.TGNummer = dbo.TXP_LVer_IN_AU.ForUserID
|
|
|
|
GO
|
|
|
|
/****** Object: View [dbo].[TXP_AU_Offen] Script Date: 23.04.2013 08:08:10 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
CREATE VIEW [dbo].[TXP_AU_Offen]
|
|
AS
|
|
SELECT TOP (100) PERCENT TicketID, MAX(Status_Changedate) AS Status_Changedate, TGNummer
|
|
FROM dbo.TXP_Ticket_Data
|
|
WHERE (Status_Neu <> 'Geschlossen') AND (TicketID LIKE 'au_%')
|
|
GROUP BY TicketID, TGNummer
|
|
HAVING (NOT (TGNummer IS NULL))
|
|
ORDER BY TicketID
|
|
|
|
GO
|
|
|
|
/****** Object: View [dbo].[TXP_AU_Geschlossen] Script Date: 23.04.2013 08:08:10 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
CREATE VIEW [dbo].[TXP_AU_Geschlossen]
|
|
AS
|
|
SELECT TOP (100) PERCENT TicketID, MAX(Status_Changedate) AS Status_Changedate, TGNummer
|
|
FROM dbo.TXP_Ticket_Data
|
|
WHERE (Status_Neu = 'Geschlossen') AND (TicketID LIKE 'au_%')
|
|
GROUP BY TicketID, TGNummer
|
|
HAVING (NOT (TGNummer IS NULL))
|
|
ORDER BY TicketID
|
|
|
|
GO
|
|
|
|
/****** Object: View [dbo].[TXP_IN_Offen] Script Date: 23.04.2013 08:08:10 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
CREATE VIEW [dbo].[TXP_IN_Offen]
|
|
AS
|
|
SELECT TOP (100) PERCENT TicketID, MAX(Status_Changedate) AS Status_Changedate, TGNummer
|
|
FROM dbo.TXP_Ticket_Data
|
|
WHERE (Status_Neu <> 'Geschlossen') AND (Status_Neu <> 'Geschlossen "Autoclose"') AND (TicketID LIKE 'in%') AND (Status_Neu <> 'Warten auf Schliessung') AND
|
|
(Status_Neu <> 'Solved')
|
|
GROUP BY TicketID, TGNummer
|
|
HAVING (NOT (TGNummer IS NULL))
|
|
|
|
GO
|
|
|
|
/****** Object: View [dbo].[TXP_AU_IN] Script Date: 23.04.2013 08:08:10 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
CREATE VIEW [dbo].[TXP_AU_IN]
|
|
AS
|
|
SELECT DISTINCT TicketID
|
|
FROM dbo.TXP_Ticket_Data
|
|
WHERE (TicketID LIKE 'au_%') OR
|
|
(TicketID LIKE 'IN_%')
|
|
|
|
GO
|
|
|
|
/****** Object: View [dbo].[Honoris_ne_300] Script Date: 23.04.2013 08:08:10 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
CREATE VIEW [dbo].[Honoris_ne_300]
|
|
AS
|
|
SELECT dbo.Honoris_JOURNAL.DATUM, dbo.Honoris_JOURNAL.ANZAHLINT, dbo.Honoris_mitarbeiter.NAME, dbo.Honoris_mitarbeiter.VORNAME,
|
|
dbo.Honoris_PROJEKT.KURZNAME, dbo.Honoris_PROJEKT.BEZEICHNUNG, dbo.Honoris_mitarbeiter.MITARBEITER_ID, dbo.Honoris_JOURNAL.ERFDATUM,
|
|
dbo.Honoris_PROJEKT.PROJEKT_NR
|
|
FROM dbo.Honoris_JOURNAL INNER JOIN
|
|
dbo.Honoris_PROJEKT ON dbo.Honoris_JOURNAL.PROJEKT_NR = dbo.Honoris_PROJEKT.PROJEKT_NR INNER JOIN
|
|
dbo.Honoris_mitarbeiter ON dbo.Honoris_JOURNAL.MITARBEITER_NR = dbo.Honoris_mitarbeiter.MITARBEITER_NR
|
|
WHERE (dbo.Honoris_PROJEKT.PROJEKT_NR < 200) OR
|
|
(dbo.Honoris_PROJEKT.PROJEKT_NR > 399)
|
|
|
|
|
|
GO
|
|
|
|
/****** Object: View [dbo].[Honoris_300] Script Date: 23.04.2013 08:08:10 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
CREATE VIEW [dbo].[Honoris_300]
|
|
AS
|
|
SELECT dbo.Honoris_JOURNAL.DATUM, dbo.Honoris_JOURNAL.ANZAHLINT, dbo.Honoris_mitarbeiter.NAME, dbo.Honoris_mitarbeiter.VORNAME,
|
|
dbo.Honoris_PROJEKT.KURZNAME, dbo.Honoris_PROJEKT.BEZEICHNUNG, dbo.Honoris_mitarbeiter.MITARBEITER_ID, dbo.Honoris_JOURNAL.ERFDATUM,
|
|
dbo.Honoris_PROJEKT.PROJEKT_NR
|
|
FROM dbo.Honoris_JOURNAL INNER JOIN
|
|
dbo.Honoris_PROJEKT ON dbo.Honoris_JOURNAL.PROJEKT_NR = dbo.Honoris_PROJEKT.PROJEKT_NR INNER JOIN
|
|
dbo.Honoris_mitarbeiter ON dbo.Honoris_JOURNAL.MITARBEITER_NR = dbo.Honoris_mitarbeiter.MITARBEITER_NR
|
|
WHERE (dbo.Honoris_PROJEKT.PROJEKT_NR >= 300) AND (dbo.Honoris_PROJEKT.PROJEKT_NR <= 399)
|
|
|
|
|
|
GO
|
|
|
|
/****** Object: View [dbo].[Honoris_200] Script Date: 23.04.2013 08:08:10 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
CREATE VIEW [dbo].[Honoris_200]
|
|
AS
|
|
SELECT dbo.Honoris_JOURNAL.DATUM, dbo.Honoris_JOURNAL.ANZAHLINT, dbo.Honoris_mitarbeiter.NAME, dbo.Honoris_mitarbeiter.VORNAME,
|
|
dbo.Honoris_PROJEKT.KURZNAME, dbo.Honoris_PROJEKT.BEZEICHNUNG, dbo.Honoris_mitarbeiter.MITARBEITER_ID, dbo.Honoris_JOURNAL.ERFDATUM,
|
|
dbo.Honoris_PROJEKT.PROJEKT_NR
|
|
FROM dbo.Honoris_JOURNAL INNER JOIN
|
|
dbo.Honoris_PROJEKT ON dbo.Honoris_JOURNAL.PROJEKT_NR = dbo.Honoris_PROJEKT.PROJEKT_NR INNER JOIN
|
|
dbo.Honoris_mitarbeiter ON dbo.Honoris_JOURNAL.MITARBEITER_NR = dbo.Honoris_mitarbeiter.MITARBEITER_NR
|
|
WHERE (dbo.Honoris_PROJEKT.PROJEKT_NR >= 200) AND (dbo.Honoris_PROJEKT.PROJEKT_NR < 300)
|
|
|
|
GO
|
|
|