/* Entwicklung der Anzahl Zahlungsempfänger und
        Ausgaben (Finanzkonto) im laufenden Jahr  */

declare @aktJahr char (4)
set @aktJahr = year (getdate () )

SELECT
        h.hnr AS [Produktsachkonto],
        h.bez AS [Produktbezeichnung],

       SUM(CAST (( CASE ASCII(z.typ) WHEN 69 THEN 0 WHEN 97 THEN 0 WHEN 65 THEN round(z.betrag,2) WHEN 101 THEN round(-z.betrag,2) END) AS decimal(20, 2))) AS [Ausgaben - aktuell],
       SUM(CASE WHEN month(buchdat) = 1  THEN (CAST (( CASE ASCII(z.typ) WHEN 69 THEN 0 WHEN 97 THEN 0 WHEN 65 THEN round(z.betrag,2) WHEN 101 THEN round(-z.betrag,2) END) AS decimal(20, 2))) ELSE 0 END) AS 'Januar',
       SUM(CASE WHEN month(buchdat) = 2  THEN (CAST (( CASE ASCII(z.typ) WHEN 69 THEN 0 WHEN 97 THEN 0 WHEN 65 THEN round(z.betrag,2) WHEN 101 THEN round(-z.betrag,2) END) AS decimal(20, 2))) ELSE 0 END) AS 'Februar',
       SUM(CASE WHEN month(buchdat) = 3  THEN (CAST (( CASE ASCII(z.typ) WHEN 69 THEN 0 WHEN 97 THEN 0 WHEN 65 THEN round(z.betrag,2) WHEN 101 THEN round(-z.betrag,2) END) AS decimal(20, 2))) ELSE 0 END) AS 'März',
       SUM(CASE WHEN month(buchdat) = 4  THEN (CAST (( CASE ASCII(z.typ) WHEN 69 THEN 0 WHEN 97 THEN 0 WHEN 65 THEN round(z.betrag,2) WHEN 101 THEN round(-z.betrag,2) END) AS decimal(20, 2))) ELSE 0 END) AS 'April',
       SUM(CASE WHEN month(buchdat) = 5  THEN (CAST (( CASE ASCII(z.typ) WHEN 69 THEN 0 WHEN 97 THEN 0 WHEN 65 THEN round(z.betrag,2) WHEN 101 THEN round(-z.betrag,2) END) AS decimal(20, 2))) ELSE 0 END) AS 'Mai',
       SUM(CASE WHEN month(buchdat) = 6  THEN (CAST (( CASE ASCII(z.typ) WHEN 69 THEN 0 WHEN 97 THEN 0 WHEN 65 THEN round(z.betrag,2) WHEN 101 THEN round(-z.betrag,2) END) AS decimal(20, 2))) ELSE 0 END) AS 'Juni',
       SUM(CASE WHEN month(buchdat) = 7  THEN (CAST (( CASE ASCII(z.typ) WHEN 69 THEN 0 WHEN 97 THEN 0 WHEN 65 THEN round(z.betrag,2) WHEN 101 THEN round(-z.betrag,2) END) AS decimal(20, 2))) ELSE 0 END) AS 'Juli',
       SUM(CASE WHEN month(buchdat) = 8  THEN (CAST (( CASE ASCII(z.typ) WHEN 69 THEN 0 WHEN 97 THEN 0 WHEN 65 THEN round(z.betrag,2) WHEN 101 THEN round(-z.betrag,2) END) AS decimal(20, 2))) ELSE 0 END) AS 'August',
       SUM(CASE WHEN month(buchdat) = 9  THEN (CAST (( CASE ASCII(z.typ) WHEN 69 THEN 0 WHEN 97 THEN 0 WHEN 65 THEN round(z.betrag,2) WHEN 101 THEN round(-z.betrag,2) END) AS decimal(20, 2))) ELSE 0 END) AS 'September',
       SUM(CASE WHEN month(buchdat) = 10 THEN (CAST (( CASE ASCII(z.typ) WHEN 69 THEN 0 WHEN 97 THEN 0 WHEN 65 THEN round(z.betrag,2) WHEN 101 THEN round(-z.betrag,2) END) AS decimal(20, 2))) ELSE 0 END) AS 'Oktober',
       SUM(CASE WHEN month(buchdat) = 11 THEN (CAST (( CASE ASCII(z.typ) WHEN 69 THEN 0 WHEN 97 THEN 0 WHEN 65 THEN round(z.betrag,2) WHEN 101 THEN round(-z.betrag,2) END) AS decimal(20, 2))) ELSE 0 END) AS 'November',
       SUM(CASE WHEN month(buchdat) = 12 THEN (CAST (( CASE ASCII(z.typ) WHEN 69 THEN 0 WHEN 97 THEN 0 WHEN 65 THEN round(z.betrag,2) WHEN 101 THEN round(-z.betrag,2) END) AS decimal(20, 2))) ELSE 0 END) AS 'Dezember'


FROM buhhst h
         left join buzahl z ON (z.hhst = h.lnr)

WHERE (h.typ = 'A' OR h.typ = 'D')
        AND (year (buchdat) = @aktJahr AND z.hhjahr between @aktJahr-1 AND @aktJahr)
        AND z.sachgeb = 3


GROUP BY h.hnr ,
        h.bez


ORDER BY h.hnr ,
        h.bez