C11/System/LOCOSOFT/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",
	   (@CURRENT_DATE) - INTERVAL '001 00:00:00.000' 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" = '2') and (T1."arbeitstag_mofr" = 1)) and ((od_year(T1."datum")) = (year(((@CURRENT_DATE) - INTERVAL '001 00:00:00.000'))))) and (T1."datum" <= ((@CURRENT_DATE) - INTERVAL '001 00:00:00.000'))) or ((((T1."bundeslaender_id" = '2') and ((od_year(T1."datum")) = (year(((@CURRENT_DATE) - INTERVAL '001 00:00:00.000'))))) and (T1."datum" <= ((@CURRENT_DATE) - INTERVAL '001 00:00:00.000'))) and (T1."datum" IN (convert(datetime, '2022-01-01 00:00:00.000'),convert(datetime, '2023-01-01 00:00:00.000'),convert(datetime, '2024-01-01 00:00:00.000'),convert(datetime, '2025-01-01 00:00:00.000'),convert(datetime, '2026-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