USE [msdb]
GO

/****** Object:  Job [ETL Stage to Core]    Script Date: 13.10.2023 23:40:00 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 13.10.2023 23:40:00 ******/
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 Stage to Core', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'Es ist keine Beschreibung verfügbar.', 
		@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 [Dim Benutzer]    Script Date: 13.10.2023 23:40:01 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Dim Benutzer', 
		@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_Core
GO

DECLARE @CurrentDate AS DATETIME = GETDATE();
DECLARE @IsUpdated AS BIT = 0;

----Benutzer in die Core laden und Historisieren

-- Cursor-Variablen-Deklaration
DECLARE @BenutzerID INT, @Benutzername NVARCHAR(255), @Adresse NVARCHAR(1024), @Email NVARCHAR(255), @Telefonnummer NVARCHAR(20);

-- Markieren Sie alle Datensätze als gelöscht, die nicht mehr in der DWH_Stage.dbo.ERP_Benutzer-Tabelle vorhanden sind
UPDATE DimBenutzer
SET GueltigBis = @CurrentDate
WHERE GueltigBis IS NULL AND BenutzerID NOT IN (SELECT BenutzerID FROM DWH_Stage.dbo.ERP_Benutzer);

-- Cursor-Definition
DECLARE BenutzerCursor CURSOR FOR
SELECT
BenutzerID,
Benutzername,
Adresse,
Email,
Telefonnummer
FROM
DWH_Stage.dbo.ERP_Benutzer

OPEN BenutzerCursor;
FETCH NEXT FROM BenutzerCursor INTO @BenutzerID, @Benutzername, @Adresse, @Email, @Telefonnummer;

WHILE @@FETCH_STATUS = 0
BEGIN
-- Aufteilen der Adresse in ihre Komponenten
-- (Hier können Sie weitere Verarbeitungslogik hinzufügen, um die Adresse in ihre Komponenten aufzuteilen)
DECLARE @Strasse NVARCHAR(255), @PLZ NVARCHAR(10), @Ort NVARCHAR(255);
SET @Strasse = LEFT(@Adresse, CHARINDEX('','', @Adresse) - 1);
SET @PLZ = SUBSTRING(@Adresse, CHARINDEX('','', @Adresse) + 2, CHARINDEX('' '', @Adresse, CHARINDEX('','', @Adresse) + 2) - CHARINDEX('','', @Adresse) - 2);
SET @Ort = RIGHT(@Adresse, LEN(@Adresse) - CHARINDEX('' '', @Adresse, CHARINDEX('','', @Adresse) + 2));
-- Prüfen Sie, ob es Änderungen für diesen Benutzer gibt
IF NOT EXISTS (
SELECT 1
FROM DimBenutzer
WHERE
BenutzerID = @BenutzerID
AND Benutzername = @Benutzername
AND Strasse = @Strasse
AND PLZ = @PLZ
AND Ort = @Ort
AND Email = @Email
AND Telefonnummer = @Telefonnummer
AND GueltigBis IS NULL -- Nur aktuelle Datensätze prüfen
)
BEGIN
-- Setzen Sie die Flag, um anzuzeigen, dass Updates vorhanden sind
SET @IsUpdated = 1;
-- Setzen Sie das GültigBis-Datum für den alten Datensatz auf das aktuelle Datum
UPDATE DimBenutzer
SET GueltigBis = @CurrentDate
WHERE BenutzerID = @BenutzerID AND GueltigBis IS NULL;
END
-- Wenn es ein Update gibt oder der Benutzer noch nicht in DimBenutzer existiert, fügen Sie den neuen Datensatz ein
IF @IsUpdated = 1 OR NOT EXISTS (
SELECT 16
FROM DimBenutzer
WHERE BenutzerID = @BenutzerID
)
BEGIN
INSERT INTO DimBenutzer (BenutzerID, Benutzername, Strasse, PLZ, Ort, Email, Telefonnummer, GueltigVon, GueltigBis)
VALUES (@BenutzerID, @Benutzername, @Strasse, @PLZ, @Ort, @Email, @Telefonnummer, @CurrentDate, NULL);
END
SET @IsUpdated = 0;
FETCH NEXT FROM BenutzerCursor INTO @BenutzerID, @Benutzername, @Adresse, @Email, @Telefonnummer;
END

CLOSE BenutzerCursor;
DEALLOCATE BenutzerCursor;

--hier ergänzen wir die Daten noch mit dem Bundeslad und dem Lankreis
UPDATE DWH_Core.dbo.DimBenutzer
SET Bundesland = PLZ.Bundesland, Land = PLZ.Landkreis
FROM DWH_Core.dbo.DimBenutzer Benutzer
INNER JOIN TestDaten.dbo.Adressen PLZ
ON Benutzer.PLZ = PLZ.Postleitzahl AND Benutzer.Ort = PLZ.Ort
WHERE Benutzer.Bundesland IS NULL
AND Benutzer.Land IS NULL ;', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Dim Status etc]    Script Date: 13.10.2023 23:40:01 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Dim Status etc', 
		@step_id=2, 
		@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_Core
GO
---Diese Dimensionen werden nie einen Status wie geschlossen herstellen können, von daher ist hier eine Versionierung nicht möglich, und auch nicht Sinnvoll.
DECLARE @CurrentDate AS DATETIME = GETDATE();

-- MERGE-Befehl zum Einfügen/Aktualisieren von Daten in DimStatus
MERGE INTO DimStatus AS Target
USING (
SELECT DISTINCT Status
FROM DWH_Stage.dbo.ERP_Bestellung
) AS Source
ON Target.Status = Source.Status AND Target.GueltigBis IS NULL
-- Wenn der Datensatz in der Ziel-Tabelle nicht gefunden wird, wird eingefügt
WHEN NOT MATCHED BY TARGET THEN
INSERT (Status, GueltigVon, GueltigBis)
VALUES (Source.Status, @CurrentDate, NULL);


-- MERGE-Befehl zum Einfügen/Aktualisieren von Daten in DimZahlungsart
MERGE INTO DimZahlungsart AS Target
USING (
SELECT DISTINCT Zahlungsart
FROM DWH_Stage.dbo.ERP_Zahlung
) AS Source
ON Target.Zahlungsart = Source.Zahlungsart AND Target.GueltigBis IS NULL
-- Wenn der Datensatz in der Ziel-Tabelle nicht gefunden wird, wird eingefügt
WHEN NOT MATCHED BY TARGET THEN
INSERT (Zahlungsart, GueltigVon, GueltigBis)
VALUES (Source.Zahlungsart, @CurrentDate, NULL);', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Dim Produkt]    Script Date: 13.10.2023 23:40:01 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Dim Produkt', 
		@step_id=3, 
		@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_Core
GO

DECLARE @CurrentDate AS DATETIME = GETDATE();
-- MERGE-Befehl zum Einfügen/ Aktualisieren von Daten in DimProdukt
MERGE INTO DimProdukt AS Target
USING DWH_Stage.dbo.ERP_Produkt AS Source
ON Target.ProduktID = Source.ProduktID 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.ProduktName <> Source.ProduktName OR
Target.Beschreibung <> Source.Beschreibung OR
Target.MinMenge <> Source.MinMenge OR
Target.KategorieKey <> Source.KategorieID
) 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 (ProduktID, ProduktName, Beschreibung, MinMenge, KategorieKey, GueltigVon, GueltigBis)
VALUES (Source.ProduktID, Source.ProduktName, Source.Beschreibung, Source.MinMenge, Source.KategorieID, @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 DimProdukt (ProduktID, ProduktName, Beschreibung, MinMenge, KategorieKey, GueltigVon, GueltigBis)
SELECT Source.ProduktID, Source.ProduktName, Source.Beschreibung, Source.MinMenge, Source.KategorieID, @CurrentDate, NULL
FROM DWH_Stage.dbo.ERP_Produkt AS Source
JOIN DimProdukt AS Target
ON Target.ProduktID = Source.ProduktID AND Target.GueltigBis = @CurrentDate;', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Dim Kategorie]    Script Date: 13.10.2023 23:40:01 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Dim Kategorie', 
		@step_id=4, 
		@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_Core
GO

DECLARE @CurrentDate AS DATETIME = GETDATE();
-- MERGE-Befehl zum Einfügen/ Aktualisieren von Daten in DimKategorie
MERGE INTO DimKategorie AS Target
USING DWH_Stage.dbo.ERP_Kategorie AS Source
ON Target.KategorieID = Source.KategorieID 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.KategorieName <> Source.KategorieName OR
Target.Beschreibung <> Source.Beschreibung
) 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 (KategorieID, KategorieName, Beschreibung, GueltigVon, GueltigBis)
VALUES (Source.KategorieID, Source.KategorieName, Source.Beschreibung, @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 DimKategorie (KategorieID, KategorieName, Beschreibung, GueltigVon, GueltigBis)
SELECT Source.KategorieID, Source.KategorieName, Source.Beschreibung, @CurrentDate, NULL
FROM DWH_Stage.dbo.ERP_Kategorie AS Source
JOIN DimKategorie AS Target
ON Target.KategorieID = Source.KategorieID AND Target.GueltigBis = @CurrentDate;', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Dim Lager]    Script Date: 13.10.2023 23:40:01 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Dim Lager', 
		@step_id=5, 
		@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_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;', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Dim Versender]    Script Date: 13.10.2023 23:40:01 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Dim Versender', 
		@step_id=6, 
		@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_Core
GO

DECLARE @CurrentDate AS DATETIME = GETDATE();

-- MERGE-Befehl zum Einfügen/ Aktualisieren von Daten in DimVersender
MERGE INTO DimVersender AS Target
USING DWH_Stage.dbo.ERP_Versender AS Source
ON Target.VersenderID = Source.VersenderID 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.VersenderName <> Source.VersenderName OR
Target.Beschreibung <> Source.Beschreibung
) 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 (VersenderID, VersenderName, Beschreibung, GueltigVon, GueltigBis)
VALUES (Source.VersenderID, Source.VersenderName, Source.Beschreibung, @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 DimVersender (VersenderID, VersenderName, Beschreibung, GueltigVon, GueltigBis)
SELECT Source.VersenderID, Source.VersenderName, Source.Beschreibung, @CurrentDate, NULL
FROM DWH_Stage.dbo.ERP_Versender AS Source
JOIN DimVersender AS Target
ON Target.VersenderID = Source.VersenderID AND Target.GueltigBis = @CurrentDate;', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Fakt Bestellung]    Script Date: 13.10.2023 23:40:01 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Fakt Bestellung', 
		@step_id=7, 
		@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_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
    ); ', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Fakt Bestellung bearbeitet]    Script Date: 13.10.2023 23:40:01 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Fakt Bestellung bearbeitet', 
		@step_id=8, 
		@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_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;', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Fakt Bestellung versand]    Script Date: 13.10.2023 23:40:01 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Fakt Bestellung versand', 
		@step_id=9, 
		@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_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;', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Fakt Bestellung geliefert]    Script Date: 13.10.2023 23:40:01 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Fakt Bestellung geliefert', 
		@step_id=10, 
		@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_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;', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Fakt Bestand]    Script Date: 13.10.2023 23:40:01 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Fakt Bestand', 
		@step_id=11, 
		@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'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', 
		@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'ETL STage to Core', 
		@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=20231013, 
		@active_end_date=99991231, 
		@active_start_time=1500, 
		@active_end_time=235959, 
		@schedule_uid=N'b7480eaf-d4ce-425f-89df-d7d8fc58c588'
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


