COGNOS QUERY
STRUCTURE,1,1
DATABASE,O21
DATASOURCENAME,C:\GAPS\Portal\System\IQD\Belege\Belege_Planung_Ist_FC.imr
TITLE,Belege_Planung_Ist_FC.imr
BEGIN SQL
select (database()) as c1,
	   CASE WHEN (T1."DEPARTMENT_TYPE_ID" IS NOT NULL) THEN ((od_left(T1."DEPARTMENT_TYPE_ID",2))) ELSE ('01') END as c2,
	   (substring(T1."DEPARTMENT_TYPE_ID" from 3 for 1)) as c3,
	   T2."ACCT_NR" as c4,
	   T3."BOOKKEEP_PERIOD" as c5,
	   SUM(CASE WHEN ((((T2."ACCT_NR" LIKE '8%') and (not T2."ACCT_NR" BETWEEN '89050000' AND '89500001')) and (not T2."ACCT_NR" BETWEEN '8812000' AND '8812002')) or ((T2."ACCT_NR" LIKE '9%') and (T2."ACCT_NR" <> '99999999'))) THEN ((T3."DEBIT_AMOUNT" + T3."CREDIT_AMOUNT") * -1) ELSE (T3."DEBIT_AMOUNT" + T3."CREDIT_AMOUNT") END) as c6,
	   SUM(T3."DEBIT_QUANTITY" + T3."CREDIT_QUANTITY") as c7
from "deop01"."dbo"."ACCOUNT_INFO" T2,
	("deop01"."dbo"."ACCT_DOC_KEY" T3 left outer join "deop01"."dbo"."DEPARTMENT_TYPE" T1 on T3."DEPARTMENT" = T1."DEPARTMENT_TYPE_ID")
where (T2."ACCT_NR" = T3."ACCT_NO")
and ((T2."TYPE_ACCTT" IN ('2','1')) and (T3."BOOKKEEP_DATE" >= TIMESTAMP '2018-01-01 00:00:00.000'))
group by (database()),CASE WHEN (T1."DEPARTMENT_TYPE_ID" IS NOT NULL) THEN ((od_left(T1."DEPARTMENT_TYPE_ID",2))) ELSE ('01') END,(substring(T1."DEPARTMENT_TYPE_ID" from 3 for 1)),T2."ACCT_NR",T3."BOOKKEEP_PERIOD"
order by c1 asc,c2 asc,c3 asc,c4 asc,c5 asc

END SQL
COLUMN,0,Mandant
COLUMN,1,Betrieb_Nr
COLUMN,2,Marke
COLUMN,3,Konto_Nr
COLUMN,4,Bookkeep_Period
COLUMN,5,Betrag
COLUMN,6,Stk