/* Summe der Leistungstage (ambulant) in Hilfearten an Empfänger nach Jahren aus Budgetstammdaten - hebush  -
	
	SQL Server 2000
	Prosoz 14plus version 5.02

Die Abfrage berücksichtigt sowohl ein offenes Ende oder ein Ende in der Zukunft in den Budgetstammdaten bzw.
auch ein Datum Endezahlung mit vergessener Beendigung der Budgetstammdaten.

Diese Abfrage benötigt eine Hilfetabelle 'Jahre', die als View anzulegen ist mit:

	CREATE VIEW lip_htab_jahr AS
	SELECT DISTINCT hhjahr FROM buzahl

*/

declare @aktJahr char (4)
declare @aktDatum AS datetime

set @aktJahr = year (getdate () )
set @aktDatum = getdate ()

SELECT

        ad.nachname AS [Empfänger Nachname],
        ad.vorname AS [Empfänger Vorname],
                ad.ort AS [Ort],
                jj.hhjahr AS [Jahr],


SUM (     CASE
            WHEN year (vg.endezahlung) = jj.hhjahr
                 THEN datediff (dd, CAST('31.12.' + CAST(YEAR(vg.endezahlung)-1 AS CHAR(4)) AS DATETIME), vg.endezahlung)
                 ELSE (CASE
                      WHEN year (vg.endezahlung) < jj.hhjahr
                          THEN '0'
                          ELSE CASE
                               WHEN hs.datbis IS NULL
                                     THEN CASE
                                          WHEN hs.datvon>CAST('01.01.' + CAST(jj.hhjahr AS CHAR(4)) AS DATETIME)
                                              THEN (datediff(dd, hs.datvon,
                                                   CASE
                                                        WHEN jj.hhjahr=year(getdate())
                                                             THEN GETDATE()
                                                             ELSE CAST('31.12.' + CAST(jj.hhjahr AS CHAR(4)) AS DATETIME)
                                                  END )+ 1)
                                                  ELSE (datediff(dd, CAST('01.01.' + CAST(jj.hhjahr AS CHAR(4)) AS DATETIME),
                                                       CASE
                                                             WHEN jj.hhjahr=year(getdate())
                                                                  THEN GETDATE()
                                                                  ELSE CAST('31.12.' + CAST(jj.hhjahr AS CHAR(4)) AS DATETIME)
                                                       END )+ 1)
                                     END
                                        ELSE CASE
                                                 WHEN hs.datvon>CAST('01.01.' + CAST(jj.hhjahr AS CHAR(4)) AS DATETIME)
                                                 THEN (datediff(dd, hs.datvon,
                                                       CASE
                                                           WHEN jj.hhjahr=year(hs.datbis)
                                                                 THEN hs.datbis
                                                                 ELSE CAST('31.12.' + CAST(jj.hhjahr AS CHAR(4)) AS DATETIME)
                                                       END )+ 1)
                                                       ELSE (datediff(dd, CAST('01.01.' + CAST(jj.hhjahr AS CHAR(4)) AS DATETIME),
                                                             CASE
                                                                 WHEN jj.hhjahr=year(hs.datbis)
                                                                       THEN hs.datbis
                                                                       ELSE CAST('31.12.' + CAST(jj.hhjahr AS CHAR(4)) AS DATETIME)
                                                             END )+ 1)
                                                 END
                                      END
                         END)
        END) AS [Leistungstage ambulant],

       COUNT (distinct (ad2.adressnummer))AS [Fallzahl]


FROM lip_htab_jahr jj,
                hebush hs
        left join hebuch hb ON (hs.vlnr = hb.lnr)
        left join n_vorgbeteiligte vb ON (vb.beteiligtennummer = hb.n_vorgbet_fk)
        left join n_adressen ad ON (ad.adressnummer = vb.adresse)
        left join n_vorgang vg ON (vg.vorgangsnummer = vb.vorgang)
        left join n_personendaten pd ON (ad.adressnummer = pd.zuordnungsnummer)
        left join land la ON (pd.nationalitaet = la.lns)
        left join bere be ON (be.brs = vg.bereich)
        left join n_beteiligtenarten ba ON (vb.beteiligtenart = ba.betnummer)
        left join fewe fe ON (fe.fwnr = hs.fwnr)
        left join hilfeart ha ON (hs.hifanr = ha.lnr)
        left join n_adressen ad2 ON (ad2.adressnummer = (SELECT distinct (adresse)
                                                FROM n_vorgbeteiligte
                                                WHERE vorgang = vg.vorgangsnummer
                                                AND vg.muendelnr_alt = hb.mnr
                                                AND beteiligtenart = 7))

    WHERE
-- nur relevante Jahre
        jj.hhjahr >= year(hs.datvon)
    AND jj.hhjahr <= year(isnull(vg.endezahlung, CAST('31.12.2999' AS DATETIME)))
    AND jj.hhjahr <= year(isnull(hs.datbis, CAST('31.12.2999' AS DATETIME)))

-- Fälle WiHi
    AND vg.bereich BETWEEN 3000 AND 3999
        AND hb.typ = 'E'

-- ambulante Hilfen
         AND hs.hifanr IN (11, 21, 23, 42, 45, 138, 206, 207, 208, 209, 210, 212, 213, 214, 223, 224, 22, 43, 44, 211, 215, 46)

-- Fall muss schon begonnen haben
        AND vg.beginnvorgang < getdate ()


GROUP BY   ad.nachname, ad.vorname, jj.hhjahr, ad.ort

-- für mindestens 10 junge Menschen wurden an den Empfänger im Jahr Leistungen gewährt

HAVING   COUNT (distinct (ad2.adressnummer)) > 10

ORDER BY  ad.nachname, ad.vorname, jj.hhjahr