C11/System/LOCOSOFT/SQL/schema/LOCOSOFT/views/load.current_date_prognose_operativ.sql

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE VIEW [load].[current_date_prognose_operativ]
AS
SELECT "current_date_Prognose_operativ"."Bundeslaender Id" AS "Bundeslaender_Id",
	"current_date_Prognose_operativ"."Invoice Date" AS "Invoice_Date",
	"current_date_Prognose_operativ"."Wochentage Id" AS "Wochentage_Id",
	"current_date_Prognose_operativ"."Arbeitstag Mofr" AS "Arbeitstag_Mofr",
	"current_date_Prognose_operativ"."Zaehler Mofr" AS "Zaehler_Mofr",
	"current_date_Prognose_operativ"."Summe Mofr" AS "Summe_Mofr",
	"current_date_Prognose_operativ"."Arbeitstag Mosa" AS "Arbeitstag_Mosa",
	"current_date_Prognose_operativ"."Zaehler Mosa" AS "Zaehler_Mosa",
	"current_date_Prognose_operativ"."Summe Mosa" AS "Summe_Mosa",
	"current_date_Prognose_operativ"."Feiertage Id" AS "Feiertage_Id",
	"current_date_Prognose_operativ"."Jahr" AS "Jahr",
	"current_date_Prognose_operativ"."Arbeitstag Nr Jahr" AS "Arbeitstag_Nr_Jahr",
	"current_date_Prognose_operativ"."Gesamt Arbeitstage" AS "Gesamt_Arbeitstage"
FROM "GC"."data"."current_date_Prognose_operativ" "current_date_Prognose_operativ"
WHERE "current_date_Prognose_operativ"."Invoice Date" < convert(DATETIME, convert(CHAR(8), current_timestamp, 112), 112)
	AND "current_date_Prognose_operativ"."Bundeslaender Id" = 10
	AND (
		"current_date_Prognose_operativ"."Arbeitstag Mosa" = 1
		OR CASE 
			WHEN DATEDIFF(DY, DATEADD(DY, 3, DATEADD(YY, 1, DATENAME(YY, convert(DATETIME, convert(CHAR(8), current_timestamp, 112), 112)))), convert(DATETIME, convert(CHAR(8), current_timestamp, 112), 112)) + ((DATEPART(DW, DATEADD(DY, 3, DATEADD(YY, 1, DATENAME(YY, convert(DATETIME, convert(CHAR(8), current_timestamp, 112), 112))))) + 5 - @@DATEFIRST) % 7 + 7) % 7 >= 0
				THEN 1
			WHEN DATEDIFF(DY, DATEADD(DY, 3, DATENAME(YY, convert(DATETIME, convert(CHAR(8), current_timestamp, 112), 112))), convert(DATETIME, convert(CHAR(8), current_timestamp, 112), 112)) + ((DATEPART(DW, DATEADD(DY, 3, DATENAME(YY, convert(DATETIME, convert(CHAR(8), current_timestamp, 112), 112)))) + 5 - @@DATEFIRST) % 7 + 7) % 7 >= 0
				THEN (DATEDIFF(DY, DATEADD(DY, 3, DATENAME(YY, convert(DATETIME, convert(CHAR(8), current_timestamp, 112), 112))), convert(DATETIME, convert(CHAR(8), current_timestamp, 112), 112)) + ((DATEPART(DW, DATEADD(DY, 3, DATENAME(YY, convert(DATETIME, convert(CHAR(8), current_timestamp, 112), 112)))) + 5 - @@DATEFIRST) % 7 + 7) % 7) / 7 + 1
			ELSE (DATEDIFF(DY, DATEADD(DY, 3, DATEADD(YY, - 1, DATENAME(YY, convert(DATETIME, convert(CHAR(8), current_timestamp, 112), 112)))), convert(DATETIME, convert(CHAR(8), current_timestamp, 112), 112)) + ((DATEPART(DW, DATEADD(DY, 3, DATEADD(YY, - 1, DATENAME(YY, convert(DATETIME, convert(CHAR(8), current_timestamp, 112), 112))))) + 5 - @@DATEFIRST) % 7 + 7) % 7) / 7 + 1
			END = 1
		)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

GO