DHL Intraship

USE [...300] /*** Name eigener 300-DB eintragen ***/
GO

/****** Object:  View [dbo].[UserQueryDHLIntraShip]  ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[UserQueryDHLIntraShip]
AS
SELECT     dbo.Adressen.KorrNr AS [Fortlaufende Nummer], dbo.Adressen.Name1 AS Firma, LEFT(REPLACE(dbo.Adressen.Strasse, '.', ' '), PATINDEX('% [0-9]%', 
                      REPLACE(dbo.Adressen.Strasse, '.', ' ')) - 1) AS Straße, REPLACE(SUBSTRING(REPLACE(dbo.Adressen.Strasse, '.', ' '), PATINDEX('% [0-9]%', 
                      REPLACE(dbo.Adressen.Strasse, '.', ' ')), LEN(REPLACE(dbo.Adressen.Strasse, '.', ' '))), ' ', '') AS Hausnummer, dbo.Adressen.PLZ_W AS PLZ, 
                      dbo.Adressen.Ortsname AS Ort, dbo.Land.ISOCode AS Land, dbo.Adressen.Name2 AS Firma2, '' AS Firma3, dbo.Adressen.KorrNr AS Matchcode, 
                      dbo.Adressen.Telefonnummer AS Telefon, '' AS Fax, '' AS Email, dbo.Adressen.UmsatzsteuerIdentnummer AS [Umsatzsteuer-Identifikationsnummer], 
                      dbo.Adressen.Suchname AS Kontakt, dbo.Adressen.Zusatz AS Adresszusatz
FROM         dbo.Adressen LEFT OUTER JOIN
                      dbo.AdressFilialDaten ON dbo.Adressen.KorrNr = dbo.AdressFilialDaten.KorrNr FULL OUTER JOIN
                      dbo.Land ON dbo.Adressen.Ländercode = dbo.Land.Ländercode
WHERE     
(dbo.AdressFilialDaten.Filialnummer = 0 
OR dbo.AdressFilialDaten.Filialnummer IS NULL) 
AND (dbo.Adressen.LetzterVorfall > '1') 
AND (NOT (dbo.Adressen.Name1 IS NULL)) AND (NOT (dbo.Adressen.PLZ_W IS NULL)) AND (NOT (dbo.Adressen.Ortsname IS NULL)) 

/*** Einschränkung auf Adressen in Deutschland, Schweiz, Italien ***/

AND (dbo.Land.ISOCode = 'DE' OR
        dbo.Land.ISOCode = 'CH' OR
        dbo.Land.ISOCode = 'IT')

/*** Nur Adressen mit Vorfall innerhalb der letzten 370  Tage  ***/

AND (DATEDIFF(dd, dbo.Adressen.LetzterVorfall, CONVERT(VARCHAR(8), GETDATE(), 112)) < 370) 

GO