C11/System/ARI/SQL/schema/Kalender/views/kalender.Kalender_Mandant.sql

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE VIEW [kalender].[Kalender_Mandant] AS

WITH
    [Kalender_Mandant_mit_Arbeitstag]
        AS (SELECT [T1].[Hauptbetrieb_ID]
                 , [T1].[Standort_ID]
                 , [T1].[Bundesland_ID]
                 , [T2].[Datum]
                 , [T2].[Tag_im_Jahr]
                 , [T2].[Wochentag]
                 , [T2].[Wochentag_int]
                 , [T2].[Periode]
                 , [T2].[Kalenderwoche]
                 , [T2].[Quartal]
                 , [T2].[Datum_str]
                 , [T2].[Datum_int]
                 , [T1].[Samstag_Arbeitstag]
                 , iif([T1].[Samstag_Arbeitstag] = 1, [T2].[MoSa], [T2].[MoFr]) AS [Arbeitstag]
            FROM [kalender].[Konfiguration_Mandant] [T1]
                     CROSS JOIN [kalender].[Kalender_Tage] [T2])
  , [Kalender_Mandant_mit_Feiertag]
        AS (SELECT [T1].[Hauptbetrieb_ID]
                 , [T1].[Standort_ID]
                 , [T1].[Bundesland_ID]
--                  , [T2].[Bundesland_Kuerzel]
                 , [T1].[Datum]
                 , [T1].[Tag_im_Jahr]
                 , [T1].[Wochentag]
                 , [T1].[Wochentag_int]
                 , [T1].[Periode]
                 , [T1].[Kalenderwoche]
                 , [T1].[Quartal]
                 , [T1].[Datum_str]
                 , [T1].[Datum_int]
                 , [T2].[Feiertag_Name]
                 , iif([T2].[Feiertag_ID] IS NOT NULL, 1, 0) AS [Feiertag]
                 , [T1].[Samstag_Arbeitstag]
                 , [T1].[Arbeitstag] * iif([T2].[Feiertag_ID] IS NULL, 1, 0) AS [Arbeitstag]
            FROM [Kalender_Mandant_mit_Arbeitstag] [T1]
                     LEFT JOIN [kalender].[Feiertage_Berechnet] [T2] ON [T1].[Bundesland_ID] = [T2].[Bundesland_ID]
                AND [T1].[Datum] = [T2].[Datum])

  , [Kalender_Mandant_mit_Summe]
        AS (SELECT [Hauptbetrieb_ID]
                 , [Standort_ID]
                 , [Bundesland_ID]
--                  , [Bundesland_Kuerzel]
                 , [Datum]
                 , [Tag_im_Jahr]
                 , [Wochentag]
                 , [Wochentag_int]
                 , [Periode]
                 , [Kalenderwoche]
                 , [Quartal]
                 , [Datum_str]
                 , [Datum_int]
                 , [Feiertag_Name]
                 , [Feiertag]
                 , [Samstag_Arbeitstag]
                 , [Arbeitstag]
                 , rank() OVER (
            PARTITION BY [Arbeitstag],
            [Hauptbetrieb_ID],
            [Standort_ID],
            [Periode] ORDER BY [Datum]
            ) * [Arbeitstag] AS [Arbeitstag_Zaehler_Monat]
                 , rank() OVER (
            PARTITION BY [Arbeitstag],
            [Hauptbetrieb_ID],
            [Standort_ID],
            year([Datum]) ORDER BY [Datum]
            ) * [Arbeitstag] AS [Arbeitstag_Zaehler_Jahr]
                 , rank() OVER (
            PARTITION BY [Arbeitstag],
            [Hauptbetrieb_ID],
            [Standort_ID] ORDER BY [Datum]
            ) * [Arbeitstag] AS [Arbeitstag_Zaehler_Absolut]
                 , sum([Arbeitstag]) OVER (
            PARTITION BY [Hauptbetrieb_ID],
            [Standort_ID],
            [Periode]
            ) AS [Arbeitstag_Summe_Monat]
                 , sum([Arbeitstag]) OVER (
            PARTITION BY [Hauptbetrieb_ID],
            [Standort_ID],
            year([Datum])
            ) AS [Arbeitstag_Summe_Jahr]
            FROM [Kalender_Mandant_mit_Feiertag])

  , [Kalender_Mandant_mit_produktiv]
        AS (SELECT [Hauptbetrieb_ID]
                 , [Standort_ID]
                 , [Bundesland_ID]
--                  , [Bundesland_Kuerzel]
                 , [T1].[Datum]
                 , [T1].[Datum] AS "Invoice Date"
                 , [T1].[Datum] AS "Bookkeep Date"
                 , [Tag_im_Jahr]
                 , [Wochentag]
                 , [Wochentag_int]
                 , [Periode]
                 , [Kalenderwoche]
                 , [Quartal]
                 , [Datum_str]
                 , [Datum_int]
                 , [Feiertag_Name]
                 , [Feiertag]
                 , [Samstag_Arbeitstag]
                 , [Arbeitstag]
                 , [Arbeitstag_Zaehler_Absolut]
                 , [Arbeitstag_Zaehler_Jahr]
                 , [Arbeitstag_Summe_Jahr]
                 , 1.0 * [Arbeitstag] / [Arbeitstag_Summe_Jahr] AS [ProduktivFaktorJahr]
                 , [Arbeitstag_Zaehler_Monat]
                 , [Arbeitstag_Summe_Monat]
                 , 1.0 * [Arbeitstag] / [Arbeitstag_Summe_Monat] AS [ProduktivFaktorMonat]
                 , month([T1].[Datum]) AS [Monat]
            FROM [Kalender_Mandant_mit_Summe] [T1]
                     --                     CROSS JOIN [kalender].[Buchungsbelege] [T2]
--            WHERE [T1].[Datum] BETWEEN datefromparts(year([T2].[Buchung_von]), 1, 1) AND eomonth([T2].[Buchung_bis]))
                     INNER JOIN [kalender].[Datum_Heute] [T2] ON [T1].[Datum] <= datefromparts(year([T2].[Datum]), 12, 31))
--WHERE [Datum] <= datefromparts(year(getdate()), 12, 31))
  , [Kalender_Mandant_produktiv_Monatswerte]
        AS (SELECT *
                 , iif([Monat] = 1, [ProduktivFaktorMonat], 0.0) AS [Monat_01]
                 , iif([Monat] = 2, [ProduktivFaktorMonat], 0.0) AS [Monat_02]
                 , iif([Monat] = 3, [ProduktivFaktorMonat], 0.0) AS [Monat_03]
                 , iif([Monat] = 4, [ProduktivFaktorMonat], 0.0) AS [Monat_04]
                 , iif([Monat] = 5, [ProduktivFaktorMonat], 0.0) AS [Monat_05]
                 , iif([Monat] = 6, [ProduktivFaktorMonat], 0.0) AS [Monat_06]
                 , iif([Monat] = 7, [ProduktivFaktorMonat], 0.0) AS [Monat_07]
                 , iif([Monat] = 8, [ProduktivFaktorMonat], 0.0) AS [Monat_08]
                 , iif([Monat] = 9, [ProduktivFaktorMonat], 0.0) AS [Monat_09]
                 , iif([Monat] = 10, [ProduktivFaktorMonat], 0.0) AS [Monat_10]
                 , iif([Monat] = 11, [ProduktivFaktorMonat], 0.0) AS [Monat_11]
                 , iif([Monat] = 12, [ProduktivFaktorMonat], 0.0) AS [Monat_12]

            FROM [Kalender_Mandant_mit_produktiv])
SELECT *
FROM [Kalender_Mandant_produktiv_Monatswerte]
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

GO