ETL_DWH_LOCOSOFT/schema/LOCOSOFT/views/vw_Aftersales_Rechnungen_fuer_tbl.sql

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE VIEW [dbo].[vw_Aftersales_Rechnungen_fuer_tbl] AS
SELECT ' ' AS [Acct Nr]
      ,' ' AS [Ledger Accts Name]
      ,' ' AS [Product Group]
      ,' ' AS [Repair Group]
      ,CAST(Rg.[Order Number]AS VARCHAR (50)) AS [Order Number]
	  ,CAST(Rg.[Invoice Type]AS VARCHAR (50)) AS [RG-Typ] 
	  ,CAST(Rg.[Invoice Number]AS VARCHAR (50)) AS [RG-Nummer]
	  ,Rg.[Invoice_Type_Invoice_Number] AS [RG-Typ-Nr]
      ,Rg.[Hauptbetrieb Id] AS [Rechtseinheit_ID]
      ,Rg.[Hauptbetrieb Name] AS [Rechtseinheit_Name]
      ,' ' AS [Fabrikat_Order_By]
      ,Rg.[Standort Id] AS [Betrieb_ID]
      ,Rg.[Standort Name] AS [Betrieb_Name]
      ,Rg.[Serviceberater] AS [Serviceberater]
      ,Rg.[Umsatzart] AS [Umsatzart]
      ,Rg.[Time Units]/10 AS [verk. Stunden]
--	  ,COUNT(Invoice_Type_Invoice_Number) OVER (PARTITION BY [Order Number]) AS "Anzahl"
  	  ,CAST(ben_AW.Summe_Duration_Minutes / 60.0 AS DECIMAL(26,12)) AS [benutzte Zeit (Auftrag)]
--	  ,CAST(ben_AW.Summe_Duration_Minutes / 60.0 AS DECIMAL(26,12)) / COUNT(Invoice_Type_Invoice_Number) OVER (PARTITION BY [Order Number]) AS [benutzte Zeit (Auftrag)]
      ,0 AS [DG]
      ,' ' AS [Zuordnung_Funktion]
      ,Rg.[Zuordnung] AS [Zuordnung_Produktbuchungsgruppe]
      ,Rg.[Produktbuchungsgruppe] AS [Produktbuchungsgruppe]
      ,Rg.[Kostenstelle] AS [Kostenstelle]
      ,Rg.[Auftragsart] AS [Auftragsart]
      ,Rg.[Kunde_Auftrag] AS [Kunde]
      ,' ' AS [Kundenart]
      ,CAST(Rg.[Order Number] AS VARCHAR(50)) + ' - ' + CAST(Rg.[Kunde_Auftrag] AS VARCHAR(255)) AS [Order_Desc_100]
	  ,Rg.[Auftragsposition] AS [Auftragsposition]
      ,' ' AS [Repair_Group_Desc]
      ,'Rechnung' AS [Rechnung/Gutschrift]
      ,' ' AS [FZG-Altersstaffel]
      ,' ' AS [VK < EK]
      ,' ' AS [PLZ_1_Stelle]
      ,' ' AS [PLZ_2_Stelle]
      ,' ' AS [PLZ_3_Stelle]
      ,' ' AS [PLZ_4_Stelle]
      ,' ' AS [PLZ]
      ,Rg.[Invoice Date] AS [Invoice Date]
      ,Rg.[Lohn]
      ,Rg.[NL Lohn]
      ,Rg.[Fremdl.]
      ,Rg.[EW Fremdl.]
      ,Rg.[Teile] AS [Teile_gesamt]
      ,Rg.[EW Teile] AS [EW Teile_gesamt]
      ,Rg.[NL Teile]
      ,Rg.[Mietw.]
      ,Rg.[Sonst.]
      ,0 AS [EW Sonst.]
      ,0 AS [EW Mietw.]
	  ,Rg.[Rebate Percent] AS [NL %]
	  ,Rg.[Goodwill Percent] AS [Kulanz %]
	  ,CASE 
		   WHEN Rg.[Source Table] = 'labours' AND Rg.[NL Lohn] <> 0 THEN Rg.[Rebate Percent]
		   ELSE NULL
	   END AS [NL Lohn %]
	  ,CASE 
		   WHEN Rg.[Source Table] = 'parts' AND Rg.[NL Teile] <> 0 THEN Rg.[Rebate Percent]
		   ELSE NULL
	   END AS [NL Teile %]
      ,CASE 
           WHEN Rg.[Source Table] = 'labours' AND Rg.[NL Lohn] <> 0 THEN Rg.[Goodwill Percent]
           ELSE NULL
       END AS [Kul. Lohn %]
      ,CASE 
           WHEN Rg.[Source Table] = 'parts' AND Rg.[NL Teile] <> 0 THEN Rg.[Goodwill Percent]
           ELSE NULL
       END AS [Kul. Teile %]
      ,Rg.[Time Units]/10 AS [verk. Std. Monteur]
      ,Rg.[Hauptbetrieb Id] AS [Gruppe Id]
      ,' ' AS [Woche]
      ,Rg.[Standort Name] AS [Gruppe Name]
      ,Rg.[Fabrikat] AS [Fabrikat operativ]
      ,' ' AS [Fabrikat_GC_Marken_Order_Line]
      ,Rg.[Position] AS [Position]
      ,Rg.[Kunde] AS [Kunde_RG]
	  ,Rg.[Source Table]
	  ,CAST (Rg.[Invoice Date] as DATE) AS [Datum]
  	,FORMAT(Rg.[Invoice Date], 'yyyy/MM') AS [Jahr/Monat]
	,CAST(GETDATE() AS DATE) AS [Aktueller Tag]
	,CAST (DATEADD(month, DATEDIFF(month, 0, Rg.[Invoice Date]), 0) as Date) AS [Monatserster]
	,EOMONTH(Rg.[Invoice Date]) AS Monatsletzter
    ,'("' + 
    CONVERT(VARCHAR, DATEADD(month, DATEDIFF(month, 0, Rg.[Invoice Date]), 0), 23) + 
    '","' + 
    CONVERT(VARCHAR, EOMONTH(Rg.[Invoice Date]), 23) + 
    '")' AS MUN_DATE

--[20250201-20250228]
,CONVERT(VARCHAR, DATEADD(month, DATEDIFF(month, 0, Rg.[Invoice Date]), 0), 112) + 
'-' + 
CONVERT(VARCHAR, EOMONTH(Rg.[Invoice Date]), 112) AS Mitgliedsname
    ,DATEFROMPARTS(YEAR(Rg.[Invoice Date]), 1, 1) AS [Beginn Geschäftsjahr]
	,FORMAT(Rg.[Invoice Date], 'MMM', 'de') + './'   + FORMAT(Rg.[Invoice Date], 'yyyy', 'de') AS Monat_Jahr
	,AnzahlRechnungen.[Anz. Rg.] AS [Anz. Rg.]
    ,'Anz. Rg.: ' + CAST(AnzahlRechnungen.[Anz. Rg.] AS VARCHAR(10)) AS [Anz. Rg. / Auftr.]
  FROM [LOCOSOFT].[dbo].[vw_Aftersales_Rechnungen_operativ] Rg
  LEFT JOIN [LOCOSOFT].[ims].[ben_AW_Order_Number] ben_AW
    ON ben_AW.Order_Number = Rg.[Order Number]
  CROSS APPLY (
    SELECT COUNT(DISTINCT Rg2.[Invoice Number]) AS [Anz. Rg.]
    FROM [LOCOSOFT].[dbo].[vw_Aftersales_Rechnungen_operativ] Rg2
    WHERE Rg2.[Order Number] = Rg.[Order Number]
) AS AnzahlRechnungen
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

GO