Python-Projekte/planner/Belege_Planung_Ist_FC.sql

WITH V1 (c1, c2, c3, c4, c5, c6, c7) as (
    SELECT 'desk01' as c1, 
        CASE WHEN (T1.[DEPARTMENT_TYPE_ID] IS NOT NULL) THEN ((left(T1.[DEPARTMENT_TYPE_ID],2))) ELSE ('01') END as c2, 
        rtrim(substring(T1.[DEPARTMENT_TYPE_ID], 3, 1)) as c3, 
        rtrim(T2.[ACCT_NR]) as c4, 
        CAST(T3.[BOOKKEEP_PERIOD] as varchar(6)) as c5, 
        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, 
        T3.[DEBIT_QUANTITY] + T3.[CREDIT_QUANTITY] as c7
    FROM [desk01].[dbo].[ACCT_DOC_KEY] T3
    INNER JOIN [desk01].[dbo].[ACCOUNT_INFO] T2 on T2.[ACCT_NR] = T3.[ACCT_NO]
    INNER JOIN [desk01].[dbo].[DEPARTMENT_TYPE] T1 on T3.[DEPARTMENT] = T1.[DEPARTMENT_TYPE_ID]
    WHERE T2.[TYPE_ACCTT] IN ('2','1') and year(T3.[BOOKKEEP_DATE]) >= year(getdate()) - 2)

SELECT c1 as 'Datenbank', c2 as 'Betrieb_Nr', c3 as 'Marke', c4 as 'Konto_Nr', c5 as 'Bookkeep_Period', sum(c6) as 'Betrag', sum(c7) as 'Menge' 
FROM V1
GROUP BY c1, c2, c3, c4, c5
ORDER BY c1, c2, c3, c4, c5