mazda/queries/Auftraege_NASA_gruppiert.sql

WITH
    [Auftraege]
        AS (SELECT [T1].[INVOICE_DATE]
                 , [T1].[ORDER_NUMBER]
                 , [T1].[STATUS]
                 , [T1].[CUSTOMER_GROUP]
                 , [T1].[DEPARTMENT]
                 , [T2].[ORDER_LINETYPE]
                 , [T2].[LINES_NET_VALUE]
                 , [T2].[REDUCTION_AMOUNT]
                 , [T2].[REPAIR_GROUP]
                 , [T2].[INV_TIME]
                 , [T2].[PROD_CODE]
            FROM [dbo].[ORDER_HEADER] [T1]
                     LEFT OUTER JOIN [dbo].[ORDER_LINE] [T2] ON [T1].[ORDER_NUMBER] = [T2].[ORDER_NUMBER]
            WHERE [T1].[STATUS] IN ('34', '35', '36', '37', '39', '47', '49', '50', '51')
              AND [T1].[INVOICE_DATE] >= convert(datetime, '2023-01-01 00:00:00.000')
              AND left([T1].[DEPARTMENT], 2) IN ('03'))
  , [Auftraege_Auftragsart]
        AS (SELECT [INVOICE_DATE]
                 , [ORDER_NUMBER]
                 , [STATUS]
                 , [CUSTOMER_GROUP]
                 , [DEPARTMENT]
                 , [ORDER_LINETYPE]
                 , [LINES_NET_VALUE]
                 , [REDUCTION_AMOUNT]
                 , rtrim([REPAIR_GROUP]) AS [REPAIR_GROUP]
                 , [INV_TIME]
                 , [PROD_CODE]
                 , year([INVOICE_DATE]) * 100 + month([INVOICE_DATE]) AS [Periode]
                 , substring([DEPARTMENT], 4, 1) AS [Kostenstelle]
                 , CASE
                       WHEN ([STATUS] BETWEEN '30' AND '39') THEN 'Theke'
                       WHEN (([LINES_NET_VALUE] = .00) AND ([REDUCTION_AMOUNT] <> .00)) THEN 'intern'
                       WHEN ([CUSTOMER_GROUP] LIKE '6%') THEN 'Garantie'
                       WHEN ([CUSTOMER_GROUP] LIKE '9%') THEN 'intern'
                       WHEN (left([CUSTOMER_GROUP], 1) BETWEEN 'A' AND 'Z') THEN 'intern'
                       WHEN ([CUSTOMER_GROUP] IN ('00')) THEN 'intern'
                       ELSE 'extern' END AS [AuftragsArt]
            FROM [Auftraege] [T1])
  , [Auftraege_Auftragstyp]
        AS (SELECT *
                 , CASE
                       WHEN ([Kostenstelle] IN ('3') OR [AuftragsArt] = 'Theke') THEN 'Sonstiges'
                       WHEN ([REPAIR_GROUP] NOT IN ('2', '3', '4')) THEN 'Inspektion'
                       WHEN ([REPAIR_GROUP] IN ('2')) THEN 'Karosseriearbeit'
                       WHEN ([REPAIR_GROUP] IN ('4')) THEN 'Lackierung'
                       ELSE 'Sonstiges' END AS [AuftragsTyp]
            FROM [Auftraege_Auftragsart])
  , [Auftraege_Linetype]
        AS (SELECT [Periode]
                 , [AuftragsArt]
                 , CASE
                       WHEN ([AuftragsArt] IN ('extern')) THEN '1'
                       WHEN ([AuftragsArt] IN ('Garantie')) THEN '2'
                       WHEN ([AuftragsArt] IN ('intern')) THEN '3'
                       WHEN ([AuftragsArt] IN ('Theke')) THEN '4'
            END AS [AuftragsartId]
                 , [AuftragsTyp]
                 , CASE
                       WHEN ([AuftragsTyp] IN ('Inspektion')) THEN '1'
                       WHEN ([AuftragsTyp] IN ('Karosseriearbeit')) THEN '2'
                       WHEN ([AuftragsTyp] IN ('Lackierung')) THEN '3'
                       WHEN ([AuftragsTyp] IN (N'Verschleißteile')) THEN '4'
                       ELSE '5' END AS [AuftragsTypId]
                 , [ORDER_NUMBER]
                 , iif([ORDER_LINETYPE] = '1', [LINES_NET_VALUE], 0.00) AS [Teile]
                 , iif([ORDER_LINETYPE] = '4', iif([LINES_NET_VALUE] <> 0.00, [LINES_NET_VALUE], [REDUCTION_AMOUNT]), 0.00) AS [Lohn]
                 , iif([ORDER_LINETYPE] = '3', [LINES_NET_VALUE], 0.00) AS [Sonstige]
                 , iif([ORDER_LINETYPE] NOT IN ('1', '3', '4'), [LINES_NET_VALUE], 0.00) AS [Rest]
            FROM [Auftraege_Auftragstyp])
  , [Auftraege_gruppiert]
        AS (SELECT [Periode]
                 , [AuftragsArt]
                 , [AuftragsartId]
                 , [AuftragsTyp]
                 , [AuftragsTypId]
                 , [ORDER_NUMBER]
                 , sum([Teile]) AS [TeileUmsatz]
                 , sum([Lohn]) AS [LohnUmsatz]
                 , sum([Sonstige]) AS [SonstigeUmsatz]
                 , sum([Teile] + [Lohn] + [Sonstige]) AS [GesamtUmsatz]
                 , sum([Rest]) AS [RestUmsatz]
                 , 1 AS [AnzahlAuftraege]
            FROM [Auftraege_Linetype]
            GROUP BY [Periode]
                   , [AuftragsArt]
                   , [AuftragsartId]
                   , [AuftragsTyp]
                   , [AuftragsTypId]
                   , [ORDER_NUMBER])
  , [Auftraege_kumuliert]
        AS (SELECT [Periode]
                 , [AuftragsArt]
                 , [AuftragsartId]
                 , [AuftragsTyp]
                 , [AuftragsTypId]
                 , sum([TeileUmsatz]) AS [TeileUmsatz]
                 , sum([LohnUmsatz]) AS [LohnUmsatz]
                 , sum([SonstigeUmsatz]) AS [SonstigeUmsatz]
                 , sum([GesamtUmsatz]) AS [GesamtUmsatz]
                 , sum([RestUmsatz]) AS [RestUmsatz]
                 , sum([AnzahlAuftraege]) AS [AnzahlAuftraege]
            FROM [Auftraege_gruppiert]
            GROUP BY [Periode]
                   , [AuftragsArt]
                   , [AuftragsartId]
                   , [AuftragsTyp]
                   , [AuftragsTypId])

SELECT *
--INTO [Auftraege_NASA_gruppiert]
FROM [Auftraege_kumuliert]
ORDER BY [Periode], [AuftragsArtId], [AuftragsTypId]