SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE VIEW [transform].[Kontenrahmen]
AS
SELECT [Konto_Nr] + ' - ' + [Konto_Bezeichnung] AS [Konto]
     , [Konto_Nr]
     , [Konto_Bezeichnung]
     , [Konto_Art]
     , [K].[KST-Nr]
     , [STK]
     , [Konto_1]
     , [Konto_2]
     , [Konto_3]
     , [Konto_4]
     , [Konto_5]
     , [Ebene1]
     , [Ebene2]
     , [Ebene3]
     , [Ebene4]
     , [Ebene5]
     , [Ebene6]
     , [Ebene7]
     , [Ebene8]
     , [Ebene9]
     , [Ebene10]
     , [Ebene11]
     , [Ebene12]
     , [Ebene13]
     , [Ebene14]
     , [Ebene15]
     , [Ebene16]
     , [Ebene17]
     , [Ebene18]
     , [Ebene19]
     , [Ebene20]
     , [Ebene21]
     , [Ebene22]
     , [Ebene23]
     , [Ebene24]
     , [Ebene25]
     , [Ebene26]
     , [Ebene27]
     , [Ebene28]
     , [Ebene29]
     , [Ebene30]
     , [Ebene31]
     , [Ebene32]
     , [Ebene33]
     , [Ebene34]
     , [Ebene35]
     , [Ebene36]
     , [Ebene37]
     , [Ebene38]
     , [Ebene39]
     , [Ebene40]
     , [Ebene41]
     , [Ebene42]
     , [Ebene43]
     , [Ebene44]
     , [Ebene45]
     , [Ebene46]
     , [Ebene47]
     , [Ebene48]
     , [Ebene49]
     , [Ebene50]
     , [Ebene51]
     , [Ebene52]
     , [Ebene53]
     , [Ebene54]
     , [Ebene55]
     , [Ebene56]
     , [Ebene57]
     , [Ebene58]
     , [Ebene59]
     , [Ebene60]

     , CASE
           WHEN [Ebene1] = 'XX' THEN 'XX'
           WHEN [Ebene1] = N'Umsatzerlöse'
               THEN '1_' + CASE
                               WHEN isnumeric(left([Ebene2], 4)) = 1 THEN left([Ebene2], 4)
                               WHEN isnumeric(left([Ebene3], 4)) = 1 THEN left([Ebene3], 4)
                               WHEN isnumeric(left([Ebene4], 4)) = 1 THEN left([Ebene4], 4)
               END
           WHEN [Ebene1] = 'Materialaufwand'
               THEN '2_' + CASE
                               WHEN isnumeric(left([Ebene2], 4)) = 1 THEN left([Ebene2], 4)
                               WHEN isnumeric(left([Ebene3], 4)) = 1 THEN left([Ebene3], 4)
                               WHEN isnumeric(left([Ebene4], 4)) = 1 THEN left([Ebene4], 4)
               END
           WHEN [Ebene1] = 'Kosten'
               THEN '3_' + CASE
                               WHEN isnumeric(left([Ebene2], 4)) = 1 THEN left([Ebene2], 4)
                               WHEN isnumeric(left([Ebene3], 4)) = 1 THEN left([Ebene3], 4)
                               WHEN isnumeric(left([Ebene4], 4)) = 1 THEN left([Ebene4], 4)
               END
           WHEN left([Ebene1], 8) = 'Indirekt'
               THEN '4_' + CASE
                               WHEN isnumeric(left([Ebene2], 4)) = 1 THEN left([Ebene2], 4)
                               WHEN isnumeric(left([Ebene3], 4)) = 1 THEN left([Ebene3], 4)
                               WHEN isnumeric(left([Ebene4], 4)) = 1 THEN left([Ebene4], 4)
               END
           WHEN left([Ebene1], 6) = 'Finanz'
               THEN '5_' + CASE
                               WHEN isnumeric(left([Ebene2], 4)) = 1 THEN left([Ebene2], 4)
                               WHEN isnumeric(left([Ebene3], 4)) = 1 THEN left([Ebene3], 4)
                               WHEN isnumeric(left([Ebene4], 4)) = 1 THEN left([Ebene4], 4)
               END
           ELSE 'X?'
    END AS [Sortierung]

     , [KS].[Kostenstelle] AS [Kostenstelle]

FROM [x_data].[Kontenrahmen] [K]
         LEFT JOIN [x_data].[Kostenstelle] [KS] ON [K].[KST-Nr] = [KS].[KST-Nr]
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

GO