ETL_DWH_ARI/schema/GC_ETL_ARI/views/transform_config.Umsatzart.sql

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE   VIEW [transform_config].[Umsatzart] AS
WITH
    "Umsatzart_import"
        AS (SELECT [Client_DB]
     , [Code] AS [Gen_ Bus_ Posting Group]
     , [Description]
     , CASE
           WHEN [Code] LIKE '%gwl%' THEN 'GWL'
           WHEN [Code] LIKE '%intern%' THEN 'Intern'
           ELSE 'Extern'
    END AS [Zuordnung]
FROM [GC_TRANSIT_ARI].[import].[Gen_Bus_Posting_Group])
  , "Umsatzart_Master"
        AS (SELECT [Gen_ Bus_ Posting Group]
                 , [Description] AS [Bezeichnung]
                 , [Gen_ Bus_ Posting Group] + ' - ' + [Description] AS [Umsatzart]
            FROM (SELECT [Client_DB]
                       , [Gen_ Bus_ Posting Group]
                       , [Description]
                       , row_number() OVER (PARTITION BY [Gen_ Bus_ Posting Group] ORDER BY [Client_DB]) AS [Row_No]
                  FROM [Umsatzart_import]) AS [T1]
            WHERE [T1].[Row_No] = 1)

SELECT [Client_DB]
     , [Umsatzart_import].[Gen_ Bus_ Posting Group]
     , [Description]
     , [Umsatzart]
     , [Zuordnung]
FROM [Umsatzart_import]
         INNER JOIN [Umsatzart_Master] ON [Umsatzart_import].[Gen_ Bus_ Posting Group] = [Umsatzart_Master].[Gen_ Bus_ Posting Group]
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

GO