Wir bauen uns ein eigenes Data Warehouse

USE META
GO

CREATE TABLE dbo.DWH_ETL_Source(
	ID int IDENTITY(1,1) NOT NULL,
	SourceName nvarchar(100) NOT NULL,
	Aktiv int NULL,
	LastImportDateID int NULL
) ON [PRIMARY]
GO

ALTER TABLE dbo.DWH_ETL_Source ADD  CONSTRAINT DF_DWH_ETL_Source_Aktiv  DEFAULT ((0)) FOR Aktiv
GO

--- für die Initialbefüllung nehmen wir alles was älter als 01.01.2020 ist.
INSERT INTO dbo.DWH_ETL_Source (SourceName,Aktiv,LastImportDateID) VALUES ('DEV-ERP',1,20200101);

USE msdb
GO


/****** Object:  Job [ETL ERP to Stage]    Script Date: 09.10.2023 17:10:14 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 09.10.2023 17:10:14 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback


END


DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'ETL ERP to Stage', 
@enabled=1, 
@notify_level_eventlog=0, 
@notify_level_email=0, 
@notify_level_netsend=0, 
@notify_level_page=0, 
@delete_level=0, 
@description=N'mit diesen Job laden wir unsere Daten in die Stage Area. Teils werden die Daten komplett geladen, bei großen Tabellen wird geprüft ob wir Anhand einer Fachlichen Abgrenzung an ein Datum einschränken können.', 
@category_name=N'[Uncategorized (Local)]', 
@owner_login_name=N'FRANK-MASTER\asus', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [ELT  - Daten holen aus dem Operativen System]    Script Date: 09.10.2023 17:10:14 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'ELT  - Daten holen aus dem Operativen System', 
@step_id=1, 
@cmdexec_success_code=0, 
@on_success_action=3, 
@on_success_step_id=0, 
@on_fail_action=2, 
@on_fail_step_id=0, 
@retry_attempts=0, 
@retry_interval=0, 
@os_run_priority=0, @subsystem=N'TSQL', 
@command=N'USE DWH_Stage
GO
Declare @Source int, @LLDateID int, @LastImportDateID int;

--- wir suchen uns die ID für unsere Source aus der Meta Tabelle
Set @Source = (SELECT ID FROM META.dbo.DWH_ETL_Source WHERE SourceName = ''DEV-ERP'' AND Aktiv =1);

--- Damit wir unsere neugeladenen Daten schneller finden können, benötigen wir hier noch eine ID
SET @LLDateID = convert(int,convert(nvarchar(8),getdate(),112)); 

--- hier holen wir uns den letzten Ladetag und nehmen diesen - 10 Tage zurück
SET @LastImportDateID = (SELECT  convert(int,convert(nvarchar(8),DateADD(DAY,-10,CONVERT(date, CONVERT(varchar(8), LastImportDateID), 112)),112)) FROM META.dbo.DWH_ETL_Source WHERE SourceName = ''DEV-ERP'' AND Aktiv =1);

-- Laden von Benutzer Tabelle, diese wird bei jeden Abzug einfach immer komplett geladen
Truncate Table ERP_Benutzer; 
INSERT INTO ERP_Benutzer (BenutzerID, Benutzername, Passwort, Email, Adresse, Telefonnummer, ExtractDate,LLDateID, SourceID)
SELECT BenutzerID, Benutzername, Passwort, Email, Adresse, Telefonnummer, GETDATE(), @LLDateID, @Source
FROM DEV_ERP.dbo.Benutzer;

-- Laden von Kategorie, auch komplett 
Truncate Table ERP_Kategorie; 
INSERT INTO ERP_Kategorie (KategorieID, KategorieName, Beschreibung, ExtractDate,LLDateID, SourceID)
SELECT KategorieID, KategorieName, Beschreibung, GETDATE(), @LLDateID, @Source
FROM DEV_ERP.dbo.Kategorie;

-- Laden von Produkt, auch komplett 
Truncate Table ERP_Produkt; 
INSERT INTO ERP_Produkt (ProduktID, ProduktName, Beschreibung, Preis, MinMenge, KategorieID, ExtractDate, LLDateID, SourceID)
SELECT ProduktID, ProduktName, Beschreibung, Preis, MinMenge, KategorieID, GETDATE(), @LLDateID, @Source
FROM DEV_ERP.dbo.Produkt;

-- Laden von Bestellung, hier grenzen wir ein, nur die veränderten Daten, wir wissen Bestelldatum oder Versanddatum werden aktualisiert hier nehmen das Datum vom letzten Lauf - 10 Tage. 
Truncate Table ERP_Bestellung; 
INSERT INTO ERP_Bestellung (BestellungID, BenutzerID, Bestelldatum, BeginnBearbeitungDatum, Versanddatum, Lieferdatum, VersenderID, Status, ExtractDate, LLDateID, SourceID)
SELECT BestellungID, BenutzerID, Bestelldatum, BeginnBearbeitungDatum, Versanddatum, Lieferdatum, VersenderID, Status, GETDATE(), @LLDateID, @Source
FROM DEV_ERP.dbo.Bestellung WHERE convert(int,convert(nvarchar(8),Bestelldatum,112)) >= @LastImportDateID  OR convert(int,convert(nvarchar(8),Versanddatum,112)) >=@LastImportDateID OR convert(int,convert(nvarchar(8),BeginnBearbeitungDatum,112)) >=@LastImportDateID OR convert(int,convert(nvarchar(8),Lieferdatum,112)) >=@LastImportDateID;

-- Laden von Versender auch komplett,
Truncate Table ERP_Versender; 
INSERT INTO ERP_Versender (VersenderID, VersenderName, Beschreibung, ExtractDate, LLDateID, SourceID)
SELECT VersenderID, VersenderName, Beschreibung, GETDATE(), @LLDateID, @Source
FROM DEV_ERP.dbo.Versender;

-- Laden von BestellDetail abgrenzen nur die Bestellungen die wir zuvor geladen haben. 
Truncate Table ERP_BestellDetail; 
INSERT INTO ERP_BestellDetail (BestellDetailID, BestellungID, ProduktID, Menge, Preis, ExtractDate, LLDateID, SourceID)
SELECT BestellDetailID, BestellungID, ProduktID, Menge, Preis, GETDATE(), @LLDateID, @Source
FROM DEV_ERP.dbo.BestellDetail WHERE BestellungID IN ( Select BestellungID FROM ERP_Bestellung);

-- Laden von Lager, auch komplett 
Truncate Table ERP_Lager; 
INSERT INTO ERP_Lager (LagerID, LagerName, Adresse, Kapazität, ExtractDate, LLDateID, SourceID)
SELECT LagerID, LagerName, Adresse, Kapazität, GETDATE(), @LLDateID, @Source
FROM DEV_ERP.dbo.Lager;

-- Laden von Lagerbestand, auch komplett 
Truncate Table ERP_Lagerbestand; 
INSERT INTO ERP_Lagerbestand (LagerbestandID, LagerID, ProduktID, Lagerbestand, ExtractDate, LLDateID, SourceID)
SELECT LagerbestandID, LagerID, ProduktID, Lagerbestand, GETDATE(), @LLDateID, @Source
FROM DEV_ERP.dbo.Lagerbestand;

-- Laden von ServiceAnfragen, hier wird das Erstellungsdatum immer gesetzt
Truncate Table ERP_ServiceAnfragen; 
INSERT INTO ERP_ServiceAnfragen (AnfrageID, BenutzerID, Erstellungsdatum, Status, Beschreibung, Lösung, ExtractDate, LLDateID, SourceID)
SELECT AnfrageID, BenutzerID, Erstellungsdatum, Status, Beschreibung, Lösung, GETDATE(), @LLDateID, @Source
FROM DEV_ERP.dbo.ServiceAnfragen WHERE convert(int,convert(nvarchar(8),Erstellungsdatum,112)) >=@LastImportDateID;

-- Laden von Bestellung, hier grenzen wir ein, nur die veränderten Daten, wir wissen Bestelldatum oder Versanddatum werden aktualisiert. 
Truncate Table ERP_Zahlung; 
INSERT INTO ERP_Zahlung (ZahlungID, BestellungID, Zahlungsart, Zahlungsbetrag, Zahlungsdatum, ExtractDate, LLDateID, SourceID)
SELECT ZahlungID, BestellungID, Zahlungsart, Zahlungsbetrag, Zahlungsdatum, GETDATE(), @LLDateID, @Source
FROM DEV_ERP.dbo.Zahlung WHERE convert(int,convert(nvarchar(8),Zahlungsdatum,112)) >=@LastImportDateID;

-- Laden von ServiceAnfragen, auch komplett 
Truncate Table ERP_SupportTickets; 
INSERT INTO ERP_SupportTickets (TicketID, AnfrageID, ZuweisenAn, Priorität, ExtractDate, LLDateID, SourceID)
SELECT TicketID, AnfrageID, ZuweisenAn, Priorität, GETDATE(), @LLDateID, @Source
FROM DEV_ERP.dbo.SupportTickets;

', 
@database_name=N'master', 
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Ladedatum hochsetzen]    Script Date: 09.10.2023 17:10:14 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Ladedatum hochsetzen', 
@step_id=2, 
@cmdexec_success_code=0, 
@on_success_action=1, 
@on_success_step_id=0, 
@on_fail_action=2, 
@on_fail_step_id=0, 
@retry_attempts=0, 
@retry_interval=0, 
@os_run_priority=0, @subsystem=N'TSQL', 
@command=N'Update META.dbo.DWH_ETL_Source SET LastImportDateID = convert(int,convert(nvarchar(8),getdate(),112))  WHERE SourceName  = ''DEV-ERP'' AND Aktiv =1 ;', 
@database_name=N'master', 
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'täglicher ETL Schritt to Stage', 
@enabled=1, 
@freq_type=4, 
@freq_interval=1, 
@freq_subday_type=1, 
@freq_subday_interval=0, 
@freq_relative_interval=0, 
@freq_recurrence_factor=0, 
@active_start_date=20231009, 
@active_end_date=99991231, 
@active_start_time=0, 
@active_end_time=235959, 
@schedule_uid=N'9b687e39-9bd1-4d3c-95f8-06ec2448480f'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

CREATE DATABASE DWH_Core
GO
USE DWH_Core
GO

---Die DimDate wird einmal aufgebaut, danach kommen erst wieder nach 2099 Daten hinzu :) 
CREATE TABLE dbo.DimDate(
	DateID int,
	[Date] Date, 
	Date_DE char(10),
	Date_US char(10),
	Date_ANSI char(10),
	Date_ISO8601 char(10),
	YearID int,
	MonthID nvarchar(10),
	QuarterID char(6),
	KwID char(6),
	DayofYear int,
	CountofDayMonth int,
	restofDayYear int,
	DayofWeek int,
	CalendarWeek int,
	Month int,
	Day int,
	Quarter int,
	beginLastWeek date,
	endLastWeek date,
	beginCurrentWeek date,
	endCurrentWeek date,
	beginNextWeek date,
	endNextWeek date,
	beginLastMonth date,
	endLastMonth date,
	beginCurrentMonth date,
	endCurrentMonth date,
	beginNextMonth date,
	endNextMonth date,
	beginLastYear date,
	endLastYear date,
	beginCurrentYear date,
	endCurrentYear date,
	beginNextYear date,
	endNextYear date,
	weekday_DE nvarchar(30),
	MonthName_DE nvarchar(30)
) ;

---Die Benutzertabelle, hier haben wir das Passwort nicht mit in unser DWH überführt, dafür haben wir hier die Adresse richtig aufgelöst. Bundesland und Land fügen wir dann noch von einer anderen Externen Quelle ein.
CREATE TABLE DimBenutzer (
    BenutzerKey INT PRIMARY KEY IDENTITY(1,1),
    BenutzerID INT,
    Benutzername NVARCHAR(255),
    Email NVARCHAR(255),
    Strasse NVARCHAR(255),
    Telefonnummer NVARCHAR(10),
    PLZ NVARCHAR(10),
    Ort NVARCHAR(100),
    Bundesland NVARCHAR(100),
    Land NVARCHAR(100),
    GueltigVon DATETIME,
    GueltigBis DATETIME
);


-- DimProdukt
CREATE TABLE DimProdukt (
    ProduktKey INT PRIMARY KEY IDENTITY(1,1),
    ProduktID INT,
    ProduktName NVARCHAR(255),
    Beschreibung NVARCHAR(1024),
	MinMenge INT,
    KategorieKey INT,
    GueltigVon DATETIME,
    GueltigBis DATETIME
);


-- DimKategorie
CREATE TABLE DimKategorie (
    KategorieKey INT PRIMARY KEY IDENTITY(1,1),
    KategorieID INT,
    KategorieName NVARCHAR(255),
    Beschreibung NVARCHAR(1024),
    GueltigVon DATETIME,
    GueltigBis DATETIME
);


-- DimZahlungsart
CREATE TABLE DimZahlungsart (
    ZahlungsartKey INT PRIMARY KEY IDENTITY(1,1),
    Zahlungsart NVARCHAR(100),
    GueltigVon DATETIME,
    GueltigBis DATETIME
);

CREATE TABLE DimVersender (
    VersenderKey INT PRIMARY KEY IDENTITY(1,1),
	VersenderID int,
    VersenderName NVARCHAR(255),
    Beschreibung NVARCHAR(1024),
    GueltigVon DATETIME,
    GueltigBis DATETIME
);

CREATE TABLE DimStatus (
    StatusKey INT PRIMARY KEY IDENTITY(1,1),
    Status NVARCHAR(255),
    Beschreibung NVARCHAR(1024),
    GueltigVon DATETIME,
    GueltigBis DATETIME
);

--- DimLager 
CREATE TABLE DimLager(
	LagerKey INT PRIMARY KEY IDENTITY(1,1),
	LagerID INT,
	LagerName NVARCHAR(100),
	Adresse NVARCHAR(255),
	Kapazität INT,
    GueltigVon DATETIME,
    GueltigBis DATETIME
);

--- DimServiceAnfrage
CREATE TABLE DimServiceAnfrage (
    ServiceAnfrageKey INT PRIMARY KEY IDENTITY(1,1),
    ServiceAnfrageID INT,
    BenutzerKey INT,
    Erstellungsdatum DATETIME,
    Status NVARCHAR(50),
    Beschreibung NVARCHAR(1000),
    Lösung NVARCHAR(1000),
    GueltigVon DATETIME,
    GueltigBis DATETIME,
   
);

--- DimSupportTicket
CREATE TABLE DimSupportTicket (
    SupportTicketKey INT PRIMARY KEY IDENTITY(1,1),
    SupportTicketID INT,
    ServiceAnfrageKey INT,
    ZuweisenAn NVARCHAR(50),
    Priorität NVARCHAR(50),
    GueltigVon DATETIME,
    GueltigBis DATETIME
);

-- FactBestellungAngelegt 
CREATE TABLE FactBestellungAngelegt (
    BestellungKey INT PRIMARY KEY IDENTITY(1,1),
	BestellungID int,
    AnlegeZeitDateID INT,
    BenutzerKey INT,
    ProduktKey INT,
    Bestelldatum DATETIME,
	BestellDateID INT,
    Menge INT,
    EPN_Preis DECIMAL(10, 2), --- EinzelPreis Netto
	EPB_Preis DECIMAL(10, 2), --- EinzelPreis Brutto
	GPN_Summe DECIMAL(10, 2), --- Gesamtpreis Netto
	GPB_Summe DECIMAL(10, 2), --- Gesamtpreis Brutto
	LLDateID INT,
    ImportDatum DATETIME
);

-- FactBestellungBearbeitet
CREATE TABLE FactBestellungBearbeitet (
    BearbeitungKey INT PRIMARY KEY IDENTITY(1,1),
    BestellungKey INT,
	Bestelldatum DATETIME,
	BeginnBearbeitung DATETIME,
    BeginnBearbeitungZeitDateID INT,
	StatusID INT,
	LLDateID INT,
    ImportDatum DATETIME
);

-- FactBestellungVersendet
CREATE TABLE FactBestellungVersendet (
    VersandKey INT PRIMARY KEY IDENTITY(1,1),
    BestellungKey INT,
    VersandZeitDateID INT,
    VersenderKey INT,
	BeginnBearbeitung DATETIME, 
	VersandZeit DATETIME,
	StatusID INT,
	LLDateID INT,
    ImportDatum DATETIME
);


-- FactBestellungGeliefert
CREATE TABLE FactBestellungGeliefert (
    LieferungKey INT PRIMARY KEY IDENTITY(1,1),
    BestellungKey INT,
    LieferZeitDateID INT,
	VersandZeit DATETIME,
	LieferZeit DATETIME, 
    VersenderKey INT,
	LLDateID INT,
    ImportDatum DATETIME
);


-- FactZahlung 
CREATE TABLE FactZahlung (
    ZahlungKey INT PRIMARY KEY IDENTITY(1,1),
    BestellungKey INT,
    ZahlungsartKey INT,
    Zahlungsbetrag DECIMAL(10, 2),
    Zahlungsdatum DATETIME,
	LLDateID INT,
    ImportDatum DATETIME
);


-- FactLagerbestand
CREATE TABLE FactLagerbestand (
    LagerbestandKey INT PRIMARY KEY IDENTITY(1,1),
    ProduktKey INT,
    LagerKey INT, 
    Datum DATETIME,
    DaumID INT,
    Menge INT,
    Preis DECIMAL(10, 2), -- Preis des Artikels am jeweiligen Tag
	LLDateID INT,
    ImportDatum DATETIME
);

----FactSupportInteraktion
CREATE TABLE FactSupportInteraktion (
    SupportInteraktionKey INT PRIMARY KEY IDENTITY(1,1),
    SupportTicketKey INT,
    InteraktionsDatum DATETIME,
    InteraktionsTyp NVARCHAR(50),
    InteraktionsDauer INT, -- z.B. Dauer eines Anrufs in Minuten
 	LLDateID INT,
    ImportDatum DATETIME
);

USE DWH_Core
GO
DECLARE @StartDate DATE, @EndDate DATE

---wir befüllen die DimDate mal für knapp 200 Jahre
SET @StartDate = '1900-01-01'
SET @EndDate = '2099-12-31';


WITH DateRange(DateData) AS 
(
  SELECT @StartDate as Date
  UNION ALL
  SELECT DATEADD(d,1,DateData)
  FROM DateRange 
  WHERE DateData < @EndDate
)
Insert into dbo.DimDate
SELECT 
      CONVERT(INT,CONVERT(VARCHAR(8),DateData,112)) AS DateID
,DateData
,CONVERT(char(10), DateData, 104) AS Date_DE
,CONVERT(char(10), DateData, 101) AS Date_US 
,CONVERT(char(10), DateData, 102) AS Date_ANSI 
,CONVERT(char(10), DateData, 23) AS Date_ISO8601 
,DATEPART(year, DateData) AS YearID
,FORMAT(DateData,'yyyyMM') AS MonthID
,CAST(year(DateData) AS char(4)) + CAST(DATEPART( QUARTER, DateData ) AS char(2)) AS QuarterID 
,CAST(year(DateData) AS char(4)) + CAST(DATEPART( wk, DateData ) AS char(2)) AS KwID
,DATEPART(dayofyear, DateData) AS DayofYear
,DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,DateData),0))) AS CountofDayMonth
,DATEDIFF(day, DateData, DATEADD(DAY, -1, DATEADD(YEAR, 1, DATEFROMPARTS(YEAR(DateData), 1, 1)))) restofDayYear
,DATEPART(WEEKDAY, DateData) AS DayofWeek
,DATEPART( wk, DateData ) AS CalendarWeek
,DATEPART(MONTH, DateData) AS Month
,DATEPART(DAY, DateData) AS Day
,DATEPART( QUARTER, DateData ) AS Quarter
,CAST(DATEADD(WEEK, -1 ,DATEADD(DAY, -DATEPART(WEEKDAY, DateData) + 1, DateData)) AS DATE) AS beginLastWeek
,CAST(DATEADD(WEEK, -1, DATEADD(DAY, -DATEPART(WEEKDAY, DateData) + 7, DateData)) AS DATE) AS endLastWeek
,CAST(DATEADD(DAY, -DATEPART(WEEKDAY, DateData) + 1, DateData) AS DATE) AS beginCurrentWeek
,CAST(DATEADD(DAY, -DATEPART(WEEKDAY, DateData) + 7, DateData) AS DATE) AS endCurrentWeek
,CAST(DATEADD(WEEK, 1 ,DATEADD(DAY, -DATEPART(WEEKDAY, DateData) + 1, DateData)) AS DATE) AS beginNextWeek
,CAST(DATEADD(WEEK, 1, DATEADD(DAY, -DATEPART(WEEKDAY, DateData) + 7, DateData)) AS DATE) AS endNextWeek
,CAST(DATEADD(MONTH, -1 , DATEFROMPARTS(YEAR(DateData),MONTH(DateData),1)) AS DATE) AS beginLastMonth
,CAST(DATEADD(DAY, -1 , DATEFROMPARTS(YEAR(DateData),MONTH(DateData),1)) AS DATE) AS endLastMonth
,DATEFROMPARTS(YEAR(DateData),MONTH(DateData),1) AS beginCurrentMonth
,DATEADD(DAY, -1, DATEADD(MONTH, 1, DATEFROMPARTS(YEAR(DateData),MONTH(DateData),1))) AS endCurrentMonth
,DATEADD(MONTH, 1, DATEFROMPARTS(YEAR(DateData),MONTH(DateData), 1)) AS beginNextMonth
,DATEADD(DAY, -1, DATEADD(MONTH, 2, DATEFROMPARTS(YEAR(DateData),MONTH(DateData), 1))) AS endNextMonth
,DATEADD(YEAR, -1, DATEFROMPARTS(YEAR(DateData), 1, 1)) AS beginLastYear
,DATEADD(DAY, -1, DATEFROMPARTS(YEAR(DateData), 1, 1)) AS endLastYear
,DATEFROMPARTS(YEAR(DateData), 1, 1) AS beginCurrentYear
,DATEADD(DAY, -1, DATEADD(YEAR, 1, DATEFROMPARTS(YEAR(DateData), 1, 1))) AS endCurrentYear
,DATEADD(YEAR, 1, DATEFROMPARTS(YEAR(DateData), 1, 1)) AS beginNextYear
,DATEADD(DAY, -1, DATEADD(YEAR, 2, DATEFROMPARTS(YEAR(DateData), 1, 1))) AS endNextYear
,DATENAME(dw,DateData) AS weekday_DE
,DATENAME(MONTH,DateData) AS MonthName_DE
FROM DateRange
OPTION (MAXRECURSION 0);

USE DWH_Core
GO

DECLARE @CurrentDate AS DATETIME = GETDATE();

-- MERGE-Befehl zum Einfügen/ Aktualisieren von Daten in DimLager 
MERGE INTO DimLager AS Target
USING DWH_Stage.dbo.ERP_Lager AS Source
ON Target.LagerID = Source.LagerID AND Target.GueltigBis IS NULL -- um nur aktuelle Datensätze zu vergleichen
-- Wenn der Datensatz in der Ziel-Tabelle gefunden wird und Unterschiede aufweist, wird aktualisiert
WHEN MATCHED AND (
    Target.LagerName <> Source.LagerName OR
    Target.Adresse <> Source.Adresse OR
Target.Kapazität <> Source.Kapazität
) THEN
    UPDATE SET 
        Target.GueltigBis = @CurrentDate
-- Wenn der Datensatz in der Ziel-Tabelle nicht gefunden wird, wird eingefügt
WHEN NOT MATCHED BY TARGET THEN
    INSERT (LagerID, LagerName, Adresse, Kapazität, GueltigVon, GueltigBis)
    VALUES (Source.LagerID, Source.LagerName, Source.Adresse, Source.Kapazität, @CurrentDate, NULL)
-- Optional: Wenn der Datensatz in der Quell-Tabelle nicht gefunden wird, wird gelöscht/ deaktiviert
WHEN NOT MATCHED BY SOURCE THEN
    UPDATE SET Target.GueltigBis = @CurrentDate;

-- Einfügen von aktualisierten Daten zurück als neue Versionen
INSERT INTO DimLager (LagerID, LagerName, Adresse, Kapazität, GueltigVon, GueltigBis)
SELECT Source.LagerID, Source.LagerName, Source.Adresse, Source.Kapazität, @CurrentDate, NULL
FROM DWH_Stage.dbo.ERP_Lager AS Source
JOIN DimLager AS Target
ON Target.LagerID = Source.LagerID AND Target.GueltigBis = @CurrentDate;

USE DWH_Core
GO

DECLARE @CurrentDate AS DATETIME = GETDATE();

-----FactBestellungAngelegt 
INSERT INTO DWH_Core.dbo.FactBestellungAngelegt (
    BestellungID,
    AnlegeZeitDateID,
    BenutzerKey,
    ProduktKey,
Bestelldatum,
BestellDateID,
    Menge,
  EPN_Preis,
EPB_Preis,
GPN_Summe,
GPB_Summe,
LLDateID,
ImportDatum
)
SELECT 
    stgBe.BestellungID,
    DimZeit.DateID,
    DimBen.BenutzerKey,
    DimProd.ProduktKey,
stgBe.Bestelldatum,
CONVERT(int, CONVERT(varchar(10),stgBe.Bestelldatum, 112)) ,
    stgBeDe.Menge,
CONVERT(decimal(10,2),(stgBeDe.Preis / 119 * 100  )) AS EPN_Preis,
stgBeDe.Preis AS EPB_Preis,
(CONVERT(decimal(10,2),(stgBeDe.Preis / 119 * 100  )) *stgBeDe.Menge) AS GPN_Summe,
(stgBeDe.Preis*stgBeDe.Menge) AS GPB_Summe,
CONVERT(int, CONVERT(varchar(10),@CurrentDate, 112)),
@CurrentDate
FROM 
    DWH_Stage.dbo.ERP_Bestellung stgBe
JOIN 
    DWH_Stage.dbo.ERP_BestellDetail stgBeDe ON stgBe.BestellungID = stgBeDe.BestellungID
JOIN 
    DWH_Core.dbo.DimDate DimZeit ON  CONVERT(int, CONVERT(varchar(10),stgBe.Bestelldatum, 112)) = DimZeit.DateID
JOIN
    DWH_Core.dbo.DimBenutzer DimBen ON stgBe.BenutzerID = DimBen.BenutzerID AND DimBen.GueltigBis IS NULL
JOIN
    DWH_Core.dbo.DimProdukt DimProd ON stgBeDe.ProduktID = DimProd.ProduktID AND DimProd.GueltigBis IS NULL
WHERE 
    NOT EXISTS (
        SELECT 1 
        FROM DWH_Core.dbo.FactBestellungAngelegt 
        WHERE BestellungID = stgBe.BestellungID
    ); 

USE DWH_Core
GO

DECLARE @CurrentDate AS DATETIME = GETDATE();

INSERT INTO DWH_Core.dbo.FactBestellungBearbeitet (
      BestellungKey
      ,Bestelldatum
      ,BeginnBearbeitung
      ,BeginnBearbeitungZeitDateID
      ,StatusID
      ,LLDateID
      ,ImportDatum
)
SELECT 
    DimBe.BestellungKey,
DimBe.Bestelldatum,
BeginnBearbeitungDatum AS BeginnBearbeitung,
    CONVERT(int, CONVERT(varchar(10),stgBe.BeginnBearbeitungDatum, 112)) AS BeginnBearbeitungZeitDateID,
Stat.StatusKey,
CONVERT(int, CONVERT(varchar(10),@CurrentDate, 112)) AS LLDateID,
@CurrentDate AS ImportDatum
FROM 
 
    DWH_Stage.dbo.ERP_Bestellung stgBe
JOIN
   DWH_Core.dbo.FactBestellungAngelegt DimBe ON stgBe.BestellungID = DimBe.BestellungID 
JOIN 
    DWH_Core.dbo.DimDate AS DimZeit ON  CONVERT(int, CONVERT(varchar(10),stgBe.BeginnBearbeitungDatum, 112))  = DimZeit.DateID
JOIN
    DWH_Core.dbo.DimBenutzer DimBen ON stgBe.BenutzerID = DimBen.BenutzerID AND DimBen.GueltigBis IS NULL
Join 
DWH_Core.dbo.DimStatus Stat ON stgBe.Status = Stat.Status AND Stat.GueltigBis IS NULL
WHERE 
    NOT EXISTS (
        SELECT 1 
        FROM DWH_Core.dbo.FactBestellungBearbeitet 
        WHERE BestellungKey = DimBe.BestellungKey
    )
    AND stgBe.BeginnBearbeitungDatum IS NOT NULL;

USE DWH_Core
GO

DECLARE @CurrentDate AS DATETIME = GETDATE();
INSERT INTO DWH_Core.dbo.FactBestellungVersendet (
BestellungKey
      ,VersandZeitDateID
      ,VersenderKey
      ,BeginnBearbeitung
      ,VersandZeit
      ,StatusID
      ,LLDateID
      ,ImportDatum
)
SELECT 
    DimBe.BestellungKey,
    DimZeit.DateID,
    DimVer.VersenderKey,
BeginnBearbeitungDatum,
Versanddatum,
Stat.StatusKey,
CONVERT(int, CONVERT(varchar(10),@CurrentDate, 112)) AS LLDateID,
@CurrentDate AS ImportDatum
FROM 
    DWH_Stage.dbo.ERP_Bestellung stgBe
JOIN
    DWH_Core.dbo.FactBestellungAngelegt DimBe ON stgBe.BestellungID = DimBe.BestellungID 
JOIN 
    DWH_Core.dbo.DimDate AS DimZeit ON CONVERT(int, CONVERT(varchar(10),stgBe.Versanddatum, 112))  = DimZeit.DateID
JOIN
    DWH_Core.dbo.DimVersender DimVer ON stgBe.VersenderID = DimVer.VersenderID AND DimVer.GueltigBis IS NULL 
JOIN 
DWH_Core.dbo.DimStatus Stat ON stgBe.Status = Stat.Status AND Stat.GueltigBis IS NULL


WHERE 
    NOT EXISTS (
        SELECT 1 
        FROM DWH_Core.dbo.FactBestellungVersendet 
        WHERE BestellungKey = DimBe.BestellungKey
    )
    AND stgBe.Versanddatum IS NOT NULL;

USE DWH_Core
GO


DECLARE @CurrentDate AS DATETIME = GETDATE();


INSERT INTO DWH_Core.dbo.FactBestellungGeliefert (
BestellungKey
      ,LieferZeitDateID
      ,VersandZeit
      ,LieferZeit
      ,VersenderKey
      ,LLDateID
      ,ImportDatum
)
SELECT 
    DimBe.BestellungKey,
    DimZeit.DateID,
    Versanddatum,
Lieferdatum,
DimVer.VersenderKey,
CONVERT(int, CONVERT(varchar(10),@CurrentDate, 112)) AS LLDateID,
@CurrentDate AS ImportDatum
FROM 
    DWH_Stage.dbo.ERP_Bestellung stgBe
JOIN
    DWH_Core.dbo.FactBestellungAngelegt DimBe ON stgBe.BestellungID = DimBe.BestellungID 
JOIN 
    DWH_Core.dbo.DimDate AS DimZeit ON CONVERT(int, CONVERT(varchar(10),stgBe.Lieferdatum, 112))  = DimZeit.DateID
JOIN
    DWH_Core.dbo.DimVersender DimVer ON stgBe.VersenderID = DimVer.VersenderID AND DimVer.GueltigBis IS NULL 
JOIN 
DWH_Core.dbo.DimStatus Stat ON stgBe.Status = Stat.Status AND Stat.GueltigBis IS NULL


WHERE 
    NOT EXISTS (
        SELECT 1 
        FROM DWH_Core.dbo.FactBestellungGeliefert 
        WHERE BestellungKey = DimBe.BestellungKey
    )
    AND stgBe.Lieferdatum IS NOT NULL;

USE DWH_Core
GO


DECLARE @CurrentDate AS DATETIME = GETDATE();


--- Löschen für disen tag, falls unsere Bestände schon einmal gelaufen sind.
DELETE FROM FactLagerbestand WHERE  DaumID = convert(int,convert(nvarchar(8),getdate()-1,112));


--- Laden der Bestände aus der Stage  
INSERT INTO FactLagerbestand (ProduktKey, LagerKey, Datum, DaumID, Menge, Preis, LLDateID, ImportDatum)
SELECT 
    P.ProduktKey,
    L.LagerKey,
    getdate()-1,
convert(int,convert(nvarchar(8),getdate()-1,112)), 
    E.Lagerbestand,
    PP.Preis,
    CONVERT(int, CONVERT(varchar(10),@CurrentDate, 112)) AS LLDateID,
    @CurrentDate AS ImportDatum
FROM 
    [DWH_Stage].[dbo].[ERP_Lagerbestand] E
JOIN 
    DimProdukt P ON E.ProduktID = P.ProduktID AND P.GueltigBis IS NULL
JOIN 
    [DWH_Stage].[dbo].[ERP_Produkt] PP ON E.ProduktID = PP.ProduktID 
JOIN 
    DimLager L ON E.LagerID= L.LagerID AND L.GueltigBis IS NULL