/*     Hilfen WiHi zum Stichtag letzte Tag Monat */

SELECT 

       stbez1,

       SUM(CASE WHEN (s.bis is null OR s.bis = '' OR s.bis  > getdate ())
			AND (v.endevorgang is null OR v.endevorgang = '' OR v.endevorgang >= getdate ())
			AND (s.von is null OR s.von = '' OR s.von < getdate ())
			AND v.beginnvorgang < getdate ()
			THEN 1 ELSE 0 END) AS aktuell,

       SUM(CASE WHEN (s.bis is null OR s.bis = '' OR s.bis  > CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'0201' AS DATETIME),104)), 104))
			AND (v.endevorgang is null OR v.endevorgang = '' OR v.endevorgang >= CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'0201' AS DATETIME),104)), 104))
			AND (s.von is null OR s.von = '' OR s.von < CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'0201' AS DATETIME),104)), 104))
			AND v.beginnvorgang < CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'0201' AS DATETIME),104)), 104)
			AND CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'0201' AS DATETIME),104)), 104) < getdate ()  
			THEN 1 ELSE 0 END) AS Januar,

       SUM(CASE WHEN (s.bis is null OR s.bis = '' OR s.bis  > CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'0301' AS DATETIME),104)), 104))
			AND (v.endevorgang is null OR v.endevorgang = '' OR v.endevorgang >= CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'0301' AS DATETIME),104)), 104))
			AND (s.von is null OR s.von = '' OR s.von < CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'0301' AS DATETIME),104)), 104))
			AND v.beginnvorgang < CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'0301' AS DATETIME),104)), 104)
			AND CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'0301' AS DATETIME),104)), 104) < getdate ()  
			THEN 1 ELSE 0 END) AS Februar,

       SUM(CASE WHEN (s.bis is null OR s.bis = '' OR s.bis  > CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'0401' AS DATETIME),104)), 104))
			AND (v.endevorgang is null OR v.endevorgang = '' OR v.endevorgang >= CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'0401' AS DATETIME),104)), 104))
			AND (s.von is null OR s.von = '' OR s.von < CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'0401' AS DATETIME),104)), 104))
			AND v.beginnvorgang < CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'0401' AS DATETIME),104)), 104)
			AND CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'0401' AS DATETIME),104)), 104) < getdate ()  
			THEN 1 ELSE 0 END) AS März,

       SUM(CASE WHEN (s.bis is null OR s.bis = '' OR s.bis  > CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'0501' AS DATETIME),104)), 104))
			AND (v.endevorgang is null OR v.endevorgang = '' OR v.endevorgang >= CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'0501' AS DATETIME),104)), 104))
			AND (s.von is null OR s.von = '' OR s.von < CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'0501' AS DATETIME),104)), 104))
			AND v.beginnvorgang < CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'0501' AS DATETIME),104)), 104)
			AND CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'0501' AS DATETIME),104)), 104) < getdate ()  
			THEN 1 ELSE 0 END) AS April,

       SUM(CASE WHEN (s.bis is null OR s.bis = '' OR s.bis  > CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'0601' AS DATETIME),104)), 104))
			AND (v.endevorgang is null OR v.endevorgang = '' OR v.endevorgang >= CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'0601' AS DATETIME),104)), 104))
			AND (s.von is null OR s.von = '' OR s.von < CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'0601' AS DATETIME),104)), 104))
			AND v.beginnvorgang < CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'0601' AS DATETIME),104)), 104)
			AND CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'0601' AS DATETIME),104)), 104) < getdate ()  
			THEN 1 ELSE 0 END) AS Mai,

       SUM(CASE WHEN (s.bis is null OR s.bis = '' OR s.bis  > CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'0701' AS DATETIME),104)), 104))
			AND (v.endevorgang is null OR v.endevorgang = '' OR v.endevorgang >= CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'0201' AS DATETIME),104)), 104))
			AND (s.von is null OR s.von = '' OR s.von < CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'0701' AS DATETIME),104)), 104))
			AND v.beginnvorgang < CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'0701' AS DATETIME),104)), 104)
			AND CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'0701' AS DATETIME),104)), 104) < getdate ()  
			THEN 1 ELSE 0 END) AS Juni,

       SUM(CASE WHEN (s.bis is null OR s.bis = '' OR s.bis  > CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'0801' AS DATETIME),104)), 104))
			AND (v.endevorgang is null OR v.endevorgang = '' OR v.endevorgang >= CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'0801' AS DATETIME),104)), 104))
			AND (s.von is null OR s.von = '' OR s.von < CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'0801' AS DATETIME),104)), 104))
			AND v.beginnvorgang < CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'0801' AS DATETIME),104)), 104)
			AND CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'0801' AS DATETIME),104)), 104) < getdate ()  
			THEN 1 ELSE 0 END) AS Juli,

       SUM(CASE WHEN (s.bis is null OR s.bis = '' OR s.bis  > CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'0901' AS DATETIME),104)), 104))
			AND (v.endevorgang is null OR v.endevorgang = '' OR v.endevorgang >= CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'0901' AS DATETIME),104)), 104))
			AND (s.von is null OR s.von = '' OR s.von < CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'0901' AS DATETIME),104)), 104))
			AND v.beginnvorgang < CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'0901' AS DATETIME),104)), 104)
			AND CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'0901' AS DATETIME),104)), 104) < getdate ()  
			THEN 1 ELSE 0 END) AS August,

       SUM(CASE WHEN (s.bis is null OR s.bis = '' OR s.bis  > CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'1001' AS DATETIME),104)), 104))
			AND (v.endevorgang is null OR v.endevorgang = '' OR v.endevorgang >= CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'1001' AS DATETIME),104)), 104))
			AND (s.von is null OR s.von = '' OR s.von < CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'1001' AS DATETIME),104)), 104))
			AND v.beginnvorgang < CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'1001' AS DATETIME),104)), 104)
			AND CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'1001' AS DATETIME),104)), 104) < getdate ()  
			THEN 1 ELSE 0 END) AS September,

       SUM(CASE WHEN (s.bis is null OR s.bis = '' OR s.bis  > CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'1101' AS DATETIME),104)), 104))
			AND (v.endevorgang is null OR v.endevorgang = '' OR v.endevorgang >= CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'1101' AS DATETIME),104)), 104))
			AND (s.von is null OR s.von = '' OR s.von < CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'1101' AS DATETIME),104)), 104))
			AND v.beginnvorgang < CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'1101' AS DATETIME),104)), 104)
			AND CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'1101' AS DATETIME),104)), 104) < getdate ()  
			THEN 1 ELSE 0 END) AS Oktober,

       SUM(CASE WHEN (s.bis is null OR s.bis = '' OR s.bis  > CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'1201' AS DATETIME),104)), 104))
			AND (v.endevorgang is null OR v.endevorgang = '' OR v.endevorgang >= CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'1201' AS DATETIME),104)), 104))
			AND (s.von is null OR s.von = '' OR s.von < CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'1201' AS DATETIME),104)), 104))
			AND v.beginnvorgang < CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'1201' AS DATETIME),104)), 104)
			AND CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'1201' AS DATETIME),104)), 104) < getdate ()  
			THEN 1 ELSE 0 END) AS November,

       SUM(CASE WHEN (s.bis is null OR s.bis = '' OR s.bis  > CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) +1 AS CHAR(4))+'0101' AS DATETIME),104)), 104)) 
			AND (v.endevorgang is null OR v.endevorgang = '' OR v.endevorgang > CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) +1 AS CHAR(4))+'0101' AS DATETIME),104)), 104))
			AND (s.von is null OR s.von = '' OR s.von < CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) +1 AS CHAR(4))+'0101' AS DATETIME),104)), 104))
			AND v.beginnvorgang < CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) +1 AS CHAR(4))+'0101' AS DATETIME),104)), 104) 
			AND CONVERT(CHAR(10),DATEADD(day, -1,CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () )+1 AS CHAR(4))+'0101' AS DATETIME),104)), 104) < getdate ()
			THEN 1 ELSE 0 END) AS Dezember
  
from n_vorgang v
        left join n_vorgbeteiligte b ON (b.vorgang=v.vorgangsnummer)
        left join n_adressen a ON (b.adresse=a.adressnummer)
        left join n_personendaten p ON (a.adressnummer=p.zuordnungsnummer)
        left join bere e ON (v.bereich=e.brs)
        left join n_vorg_statkennz s ON (v.vorgangsnummer=s.vorgangsnummer)
        left join stat t ON (s.statid=t.stkz)
        left join n_beteiligtenarten g ON (b.beteiligtenart=g.betnummer) 

where (v.bereich between 3000 AND 3999
        and g.bezeichnung = 'Junger Mensch'

       AND (s.bis is null OR s.bis = '' OR s.bis >= CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'0101' AS DATETIME),104))
       AND (v.endevorgang is null OR v.endevorgang = '' OR v.endevorgang >= CONVERT(CHAR (10), CAST(CAST(YEAR(Getdate () ) AS CHAR(4))+'0101' AS DATETIME),104))
	   AND v.beginnvorgang <= getdate () ) 
	   AND stbez1 IS NOT NULL	

group by stbez1
		

/*
HAVING  SUM(CASE WHEN (s.bis is null OR s.bis = '' OR s.bis  >= '31.01.2008') 
		  AND (v.endevorgang is null OR v.endevorgang = '' OR v.endevorgang >= '31.01.2008')
		  AND s.von <= '31.01.2008'  
		  AND v.beginnvorgang <= '31.01.2008'  
		  THEN 1 ELSE 0 END) > 1
*/
order by stbez1