#
#		ITSV GmbH
#	CCDB - Command and Control Database
#
#	FILE:		dquermfile_cfdgdmonstat.txt
#	DESCRIPTION:	DQUERY definition for CCDB DQUERY "cfdgdmonstat"
#
@querytitle			Monatlicher Trend Anzahl Clearingfälle (DATEN UNAKTUELL)
@querydescription
@group				SVCLEARCFSTATDGD
@querytype			dbselect
@sql				select * from CCVMDGDEOTMC
@title				Monatliche Entwicklung der Anzahl von Clearingfällen in DGDIALOG
#
# the following is not used by the query. It is the definition of the view the query depends on. The view must be defined before executing the query
#	TODO: convert this query to a "seqtrans" and create the view if not already existing
#
@viewdefsql			CREATE ALGORITHM=UNDEFINED DEFINER="root"@"localhost" SQL SECURITY DEFINER VIEW "ccvmdgdeotmc" AS 
					select	"m"."JAHR" AS "JAHR","m"."MONAT" AS "MONAT",
							ifnull((select "t"."ANZAHL" 
							          from "ccmdgdeotmc" "t" where (("t"."JAHR" = "m"."JAHR") and ("t"."MONAT" = "m"."MONAT") and ("t"."EVENT" = 'ERSTELLT'))),0) AS "ANZAHL_ERSTELLT",
							ifnull((select "t"."ANZAHL" 
									  from "ccmdgdeotmc" "t" 
									 where (("t"."JAHR" = "m"."JAHR") and ("t"."MONAT" = "m"."MONAT") and ("t"."EVENT" = 'OBSOLET_GESETZT'))),0) AS "ANZAHL_OBSOLET_GESETZT",
							ifnull((select "t"."LAUFENDE_SUMME" 
									  from "ccmdgdeotmc" "t" 
									 where (("t"."JAHR" = "m"."JAHR") and ("t"."MONAT" = "m"."MONAT") and ("t"."EVENT" = 'OBSOLET_GESETZT'))),
							ifnull((select "t"."LAUFENDE_SUMME" 
							          from "ccmdgdeotmc" "t" 
									 where (("t"."JAHR" = "m"."JAHR") and ("t"."MONAT" = "m"."MONAT") and ("t"."EVENT" = 'ERSTELLT'))),0)) AS "LAUFENDE_SUMME" 
					  from "ccmdgdeotmc" "m" group by "m"."JAHR","m"."MONAT"