C11/System/OPTIMA/IQD/Belege/current_date_Prognose.sql

SELECT "Bundeslaender Id" AS "Bundeslaender Id",
	"Datum" AS "Datum",
	"Wochentage Id" AS "Wochentage Id",
	"Arbeitstag Mofr" AS "Arbeitstag Mofr",
	"Zaehler Mofr" AS "Zaehler Mofr",
	"Summe Mofr" AS "Summe Mofr",
	"Arbeitstag Mosa" AS "Arbeitstag Mosa",
	"Zaehler Mosa" AS "Zaehler Mosa",
	"Summe Mosa" AS "Summe Mosa",
	"Feiertage Id" AS "Feiertage Id",
	"Jahr" AS "Jahr",
	CASE 
		WHEN ("Arbeitstag Mofr" = 1)
			THEN (RCOUNT("Arbeitstag Mofr"))
		ELSE NULL
		END AS "Arbeitstag Nr Jahr",
	"Gesamt Arbeitstage" AS "Gesamt Arbeitstage"
FROM (
	SELECT T1."bundeslaender_id" AS "Bundeslaender Id",
		T1."datum" AS "Datum",
		T1."wochentage_id" AS "Wochentage Id",
		T1."arbeitstag_mofr" AS "Arbeitstag Mofr",
		T1."zaehler_mofr" AS "Zaehler Mofr",
		T1."summe_mofr" AS "Summe Mofr",
		T1."arbeitstag_mosa" AS "Arbeitstag Mosa",
		T1."zaehler_mosa" AS "Zaehler Mosa",
		T1."summe_mosa" AS "Summe Mosa",
		T1."feiertage_id" AS "Feiertage Id",
		(getdate()) - 1 AS "Jahr",
		CASE 
			WHEN (T1."datum" BETWEEN convert(DATETIME, '2008-01-01 00:00:00.000') AND convert(DATETIME, '2008-12-31 00:00:00.000'))
				THEN (252)
			WHEN (T1."datum" BETWEEN convert(DATETIME, '2009-01-01 00:00:00.000') AND convert(DATETIME, '2009-12-31 00:00:00.000'))
				THEN (252)
			WHEN (T1."datum" BETWEEN convert(DATETIME, '2010-01-01 00:00:00.000') AND convert(DATETIME, '2010-12-31 00:00:00.000'))
				THEN (253)
			WHEN (T1."datum" BETWEEN convert(DATETIME, '2011-01-01 00:00:00.000') AND convert(DATETIME, '2011-12-31 00:00:00.000'))
				THEN (250)
			WHEN (T1."datum" BETWEEN convert(DATETIME, '2012-01-01 00:00:00.000') AND convert(DATETIME, '2012-12-31 00:00:00.000'))
				THEN (249)
			ELSE (250)
			END AS "Gesamt Arbeitstage"
	FROM "Arbeitstage" T1
	WHERE (
			(
				(
					(
						(
							(T1."bundeslaender_id" = '7')
							AND (T1."arbeitstag_mofr" = 1)
							)
						AND ((year(T1."datum")) = (year(((getdate()) - 1))))
						)
					AND (T1."datum" <= ((getdate()) - 1))
					)
				AND (T1."datum" <= ((getdate()) - 1))
				)
			OR (
				(
					(
						(T1."bundeslaender_id" = '7')
						AND ((year(T1."datum")) = (year(((getdate()) - 1))))
						)
					AND (T1."datum" <= ((getdate()) - 1))
					)
				AND (T1."datum" IN (convert(DATETIME, '2019-01-01 00:00:00.000'), convert(DATETIME, '2020-01-01 00:00:00.000'), convert(DATETIME, '2021-01-01 00:00:00.000'), convert(DATETIME, '2022-01-01 00:00:00.000'), convert(DATETIME, '2023-01-01 00:00:00.000')))
				)
			)
		-- order by "Bundeslaender Id" asc,"Datum" asc,"Wochentage Id" asc,"Arbeitstag Mofr" asc,"Zaehler Mofr" asc,"Summe Mofr" asc,"Arbeitstag Mosa" asc,"Zaehler Mosa" asc,"Summe Mosa" asc,"Feiertage Id" asc,"Gesamt Arbeitstage" asc
	) D1