SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE   VIEW [transform].[Bilanzsalden_kumuliert] AS

-- Bilanzsalden_kumuliert
WITH
    "Config_Jahreswechsel"
        -- In welchem Monat startet das Geschäftsjahr?
        AS (SELECT *
                 , iif([Monat] >= 7, 1, 0) AS [Offset1]
                 , iif([Monat] >= 7, 0, -1) AS [Offset2]
            FROM (VALUES (1, 1)
                       , (2, 1)
                       , (3, 1)
                       , (4, 1)
                       , (5, 1)
                       , (6, 1)
                       , (7, 1)) AS [T1] ([Client_DB], [Monat]))
  , "Sachkonto_Salden_Bilanzkonten"
        -- Für alle weiteren Berechnungen sind nur Bilanzkonten interessant
        AS (SELECT [T1].[Client_DB]
                 , [T1].[Branch Code]
                 , [T1].[G_L Account No_] AS [Sachkontonr_]
                 , month([T1].[Posting Date]) AS [Monat]
                 , year([T1].[Posting Date]) AS [Jahr]
                 , year([T1].[Posting Date]) * 100 + month([T1].[Posting Date]) AS [Periode]
                 , [T1].[Posting Date] AS [Buchungsdatum]
                 , convert(decimal(18, 2), [T1].[Amount]) AS [Betrag]
            FROM [xtract].[G_L_Entry_Salden] [T1]
                     INNER JOIN [xtract].[G_L_Account] [T2] ON [T1].[Client_DB] = [T2].[Client_DB] AND [T1].[G_L Account No_] = [T2].[No_]
            WHERE [T2].[Income_Balance] = '1')
  , [Buchungsperiode_alle]
        -- alle Buchungsperioden
        AS (SELECT DISTINCT [Periode]
                          , [Jahr]
                          , [Monat]
            FROM [Sachkonto_Salden_Bilanzkonten])

  , [Sachkonto_alle]
        -- Liste aller bebuchten Bilanzkonten
        AS (SELECT DISTINCT [Client_DB]
                          , [Branch Code]
                          , [Sachkontonr_]
            FROM [Sachkonto_Salden_Bilanzkonten])

  , [Sachkonto_Salden_Null]
        -- Kreuzprodukt über alle Bilanzkonten und alle Perioden
        AS (SELECT [T1].[Client_DB]
                 , [T1].[Branch Code]
                 , [T1].[Sachkontonr_]
                 , [T2].[Monat]
                 , [T2].[Jahr]
                 , [T2].[Periode]
                 , 0.0 AS [Betrag]
            FROM [Sachkonto_alle] [T1]
                     CROSS JOIN [Buchungsperiode_alle] [T2])

  , [Sachkonto_Salden_aufgefuellt]
        -- Kombination aus dem Kreuzprodukt von oben mit den tatsächlichen Buchungswerten, um eine vollständige Liste aller Konten und Buchungsperioden zu erhalten
        AS (SELECT [T1].[Client_DB]
                 , [T1].[Branch Code]
                 , [T1].[Sachkontonr_]
                 , [T1].[Monat]
                 , [T1].[Jahr]
                 , [T1].[Periode]
                 , isnull([T2].[Betrag], [T1].[Betrag]) AS [Betrag]
            FROM [Sachkonto_Salden_Null] [T1]
                     LEFT JOIN [Sachkonto_Salden_Bilanzkonten] [T2] ON [T1].[Client_DB] = [T2].[Client_DB] AND T1.[Branch Code] = T2.[Branch Code] AND [T1].[Sachkontonr_] = [T2].[Sachkontonr_] AND [T1].[Periode] = [T2].[Periode])

  , [Sachkonto_Salden_Details]
        -- Berechnung Geschäftsjahr (Mandant 1, 6, 7 ab Januar, Mandant 9 ab September)
        -- Berechnung Eröffnungsbuchung, falls Feld Origin = 0
        AS (SELECT [T1].*
                 , iif([T1].[Monat] >= [T2].[Monat], [T1].[Jahr] + [T2].[Offset1], [T1].[Jahr] + [T2].[Offset2]) AS [Geschaeftsjahr]
                 , 0.0 AS [EB]
                 , [Betrag] AS [Saldo_ohne_EB]
            FROM [Sachkonto_Salden_aufgefuellt] [T1]
                     INNER JOIN [Config_Jahreswechsel] [T2] ON [T1].[Client_DB] = [T2].[Client_DB])

  , "EB_berechnet"
        -- Summe über das Vorjahr (Geschäftsjahr)
        AS (SELECT [Client_DB]
                 , [Branch Code]
                 , [Geschaeftsjahr]
                 , min([Periode]) + 100 AS [Periode]
                 , [Sachkontonr_]
                 , sum([Betrag]) AS [EB_berechnet]
            FROM [Sachkonto_Salden_Details]
            GROUP BY [Client_DB]
                   , [Branch Code]
                   , [Geschaeftsjahr]
                   , [Sachkontonr_])
  , [EB_berechnet_seit_Anbeginn]
        -- Summe über das Vorjahr (Geschäftsjahr) ab dem 1. Monat des Geschäftsjahrs
        AS (SELECT [Client_DB]
                 , [Branch Code]
                 , [Geschaeftsjahr]
                 , [Periode]
                 , [Sachkontonr_]
                 , [EB_berechnet] AS [EB_berechnet_ein_Jahr]
                 , sum([EB_berechnet]) OVER (
            PARTITION BY [Client_DB], [Branch Code], [Sachkontonr_]
            ORDER BY [Geschaeftsjahr]
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            ) AS [EB_berechnet]
            FROM [EB_berechnet])


  , [Sachkonto_Salden_Summe]
        -- Gruppierung/Summierung nach Periode, um Konto und Periode eindeutig zu haben.
        AS (SELECT [Client_DB]
                 , [Branch Code]
                 , [Sachkontonr_]
                 , [Geschaeftsjahr]
                 , [Periode]
                 , sum([EB]) AS [EB]
                 , sum([Saldo_ohne_EB]) AS [Saldo_ohne_EB]
                 , sum([Betrag]) AS [Saldo]
            FROM [Sachkonto_Salden_Details]
            GROUP BY [Client_DB]
                   , [Branch Code]
                   , [Sachkontonr_]
                   , [Geschaeftsjahr]
                   , [Periode])

  , [Sachkonto_Salden_EB_berechnet]
        -- Join mit dem berechneten EB über Buchungsperiode und Kontonummer
        AS (SELECT [T1].*
                 , isnull([T2].[EB_berechnet], 0.0) AS [EB_berechnet]
                 , [T1].[Saldo_ohne_EB] + isnull([T2].[EB_berechnet], 0.0) AS [Saldo_berechnet]
            FROM [Sachkonto_Salden_Summe] [T1]
                     LEFT JOIN [EB_berechnet_seit_Anbeginn] [T2] ON [T1].[Client_DB] = [T2].[Client_DB] AND T1.[Branch Code] = T2.[Branch Code]
                AND [T1].[Periode] = [T2].[Periode]
                AND [T1].[Sachkontonr_] = [T2].[Sachkontonr_])

  , [Sachkonto_Salden_EB_Prio]
        -- Falls der EB fehlt, wird der berechnete Saldo verwendet
        AS (SELECT [T1].*
                 , [T1].[Saldo_berechnet] AS [Saldo_kombiniert]
            FROM [Sachkonto_Salden_EB_berechnet] [T1])


  , [Sachkonto_Salden_kumuliert]
        -- Kumulierter Saldo ab dem 1. Monat des Geschäftsjahrs
        AS (SELECT *
                 , eomonth(datefromparts([Periode] / 100, [Periode] % 100, 1)) AS [Buchungsdatum]
                 , sum([Saldo]) OVER (PARTITION BY [Client_DB], [Branch Code], [Sachkontonr_], [Geschaeftsjahr] ORDER BY [Periode]) AS [Saldo_kumuliert_gebucht]
                 , sum([Saldo_kombiniert]) OVER (PARTITION BY [Client_DB], [Branch Code], [Sachkontonr_], [Geschaeftsjahr] ORDER BY [Periode]) AS [Saldo_kumuliert]
            FROM [Sachkonto_Salden_EB_Prio])

  , [Sachkonto_Aktiva_Passiva]
        -- Bei Konten, die mit 0 oder 1 beginnen, wird abhängig vom Saldo ein "_A" oder "_P" angehängt
        AS (SELECT *
                 , CASE
                       WHEN left([Sachkontonr_], 1) NOT IN ('0', '1') THEN [Sachkontonr_]
                       WHEN [Saldo_kumuliert] >= 0 THEN [Sachkontonr_] + '_A'
                       ELSE [Sachkontonr_] + '_P'
            END AS [Konto_Bilanz]
            FROM [Sachkonto_Salden_kumuliert])

  , [Sachkonto_Aktiva_Passiva_SKR51]
        -- Acct_Nr mit Kostenrechnungsmerkmalen für Join mit SKR51_Uebersetzung.csv
        AS (SELECT *
                 , '00-00-' + [Konto_Bilanz] + '-00-00-00' AS [Acct_Nr]
            FROM [Sachkonto_Aktiva_Passiva])


SELECT [Client_DB]
     , [Branch Code]
     , [Sachkontonr_]
     , [Geschaeftsjahr]
     , [Periode]
     , [Buchungsdatum]
     --, [EB]
     , [EB_berechnet]
     , [Saldo_ohne_EB]
     --, [Saldo_kumuliert_gebucht]
     --, [Saldo_kombiniert]
     --, [Saldo]
     --, [Saldo_berechnet]
     , [Saldo_kumuliert]
     , [Konto_Bilanz]
FROM [Sachkonto_Aktiva_Passiva]
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

GO