ETL_DWH_ARI/schema/GC_ETL_ARI/views/transform.Kalender_C11.sql

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

WITH
    [Jahre] AS
        (SELECT DISTINCT ([T1].[number] + 2000) AS "Jahr"
         FROM [master].[dbo].[spt_values] [T1]
         WHERE ([T1].[number] + 2000) BETWEEN year(getdate()) - 10 AND year(getdate()) + 10
           AND [T1].[type] = 'P')
  , [Kalender_Tage_im_Jahr] AS
        (SELECT dateadd(DAY, [T1].[number], datefromparts([T2].[Jahr], 1, 1)) AS [Datum]
         FROM [master].[dbo].[spt_values] [T1]
                  INNER JOIN [Jahre] [T2] ON dateadd(DAY, [T1].[number], datefromparts([T2].[Jahr], 1, 1)) <= datefromparts([T2].[Jahr], 12, 31)
         WHERE [T1].[type] = 'P')
  , [Kalender_mit_erster_und_letzter] AS
        (SELECT [Datum]
              , format([Datum], 'yyyy/MM') AS [Jahr/Monat]
              , cast(dateadd(MONTH, datediff(MONTH, 0, [Datum]), 0) AS date) AS [Monatserster]
              , eomonth([Datum]) AS [Monatsletzter]
         FROM [Kalender_Tage_im_Jahr])
  , [Monate] AS
        (SELECT *
         FROM (VALUES (1, 'Jan.')
                    , (2, 'Feb.')
                    , (3, N'März')
                    , (4, 'Apr.')
                    , (5, 'Mai')
                    , (6, 'Juni')
                    , (7, 'Juli')
                    , (8, 'Aug.')
                    , (9, 'Sep.')
                    , (10, 'Okt.')
                    , (11, 'Nov.')
                    , (12, 'Dez.')) AS [T1] ([Monat_int], [Monat_Name]))

SELECT cast([Datum] AS datetime) AS [Inv_Date]
     , [Datum]
     , [Jahr/Monat]
     , [Monatserster]
     , [Monatsletzter]
     , '("' + convert(varchar, [Monatserster], 23) + '","' + convert(varchar, [Monatsletzter], 23) + '")' AS [MUN_DATE]
     , convert(varchar, [Monatserster], 112) + '-' + convert(varchar, [Monatsletzter], 112) AS [Mitgliedsname]
     , datefromparts(year([Datum]), 1, 1) AS [Beginn Geschäftsjahr]
     , [Monate].[Monat_Name] + '/' + convert(varchar, year([Datum])) AS [Monat_Jahr]
     , format([Datum], 'yyyy') AS [Jahr]
     , format([Datum], 'MM') AS [Monat]
     , day([Datum]) AS [Tag]
     , [Monate].[Monat_Name] AS [Monat_kurz]

FROM [Kalender_mit_erster_und_letzter]
         INNER JOIN [Monate] ON [Monate].[Monat_int] = month([Datum])
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

GO