mazda/queries/Mitarbeiter_NASA.sql

WITH
    "Jahre"
        AS (SELECT DISTINCT ([T1].[number] + 2000) AS "Jahr"
            FROM [master].[dbo].[spt_values] [T1]
            WHERE ([T1].[number] + 2000) BETWEEN '2020' AND year(getdate())
              AND [T1].[type] = 'P')
  , "Monate"
        AS (SELECT DISTINCT [T1].[number] AS "Monat"
            FROM [master].[dbo].[spt_values] [T1]
            WHERE ([T1].[number] BETWEEN 1 AND 12)
              AND [T1].[type] = 'P')
  , "Periode"
        AS (SELECT [Jahr] * 100 + [Monat] AS [Periode]
                 , datefromparts([Jahr], [Monat], 1) AS [Datum]
            FROM [Jahre]
                     CROSS JOIN [Monate])
  , "Punch_min_max"
        AS (SELECT [PROFILE_CODE]
                 , min([START_DATE_TIME]) AS [START_DATE]
                 , max([END_DATE_TIME]) AS [END_DATE]
            FROM [dbo].[PUNCH]
			WHERE left([DONE_FOR_DEPARTMENT], 2) IN ('03')
            GROUP BY [PROFILE_CODE])
  , "Punch_produktiv"
        AS (SELECT DISTINCT [PROFILE_CODE]
                          , year([START_DATE_TIME]) * 100 + month([START_DATE_TIME]) AS [Periode]
                          , 1 AS [produktiv]
            FROM [dbo].[PUNCH]
            WHERE [ACTIVITY_CODE] = '1060')
  , "Mitarbeiter_aktiv"
        AS (SELECT [T3].[PERSON_ID]
                 , [T3].[JOB_START_DATE]
                 , [T3].[JOB_END_DATE]
                 , [T3].[PROFESSION_GROUP_ID]
                 , [T3].[WORK_LEADER_GROUP_ID]
                 , [T3].[EMPLOYEE_GROUP]
                 , [T2].*
            FROM [dbo].[PROFILE] [T1]
                     INNER JOIN [dbo].[EMPLOYEE] [T3] ON [T1].[PERSON_ID] = [T3].[PERSON_ID]
                     LEFT JOIN [Punch_min_max] [T2] ON [T1].[PROFILE_CODE] = [T2].[PROFILE_CODE])
  , "Mitarbeiter_Periode"
        AS (SELECT *
            FROM [Mitarbeiter_aktiv] [T1]
                     CROSS JOIN [Periode] [T2]
            WHERE [T2].[Datum] BETWEEN [T1].[START_DATE] AND [T1].[END_DATE])

SELECT [T1].*
     , isnull([T2].[produktiv], 0) AS [produktiv]
--INTO Mitarbeiter_NASA
FROM "Mitarbeiter_Periode" [T1]
         LEFT JOIN "Punch_produktiv" [T2] ON [T1].[PROFILE_CODE] = [T2].[PROFILE_CODE] AND [T1].[Periode] = [T2].[Periode]