SELECT
    vh.Id,
    IIF(vh.VorhabensartId = 2, DATEDIFF(day, vh.Beginn, GETDATE()), DATEDIFF(day, GETDATE(), vh.Ende)) as DaysCount,
    CASE
      WHEN vh.VorhabensartId = 1 THEN 'Tage bis Projektende'
      WHEN vh.VorhabensartId = 2 THEN 'Tage seit Verfahrensbeginn'
      WHEN vh.VorhabensartId = 3 THEN 'Tage bis Programmende'
    END as DaysPhrase,

    COUNT(af.Id) as Anforderungen,
    COUNT(CASE WHEN af.StatusId = 1 THEN af.Id END) as ErfasstAnf,
    COUNT(CASE WHEN af.StatusId = 2 THEN af.Id END) as AufgenommenAnf,
    COUNT(CASE WHEN af.StatusId = 3 THEN af.Id END) as UmgesetztAnf,
    COUNT(CASE WHEN af.StatusId = 4 THEN af.Id END) as NichtUmgesetztAnf,
    COUNT(CASE WHEN af.StatusId = 5 THEN af.Id END) as ZurueckAnf,

    COUNT(aa.Id) as Aenderungsantraegen,
    COUNT(CASE WHEN aa.EntscheidungId is null THEN aa.Id END) as ErfasstAen,
    COUNT(CASE WHEN aa.EntscheidungId = 1 THEN aa.Id END) as BearbeitungAen,
    COUNT(CASE WHEN aa.EntscheidungId = 2 THEN aa.Id END) as VorgelegtAen,
    COUNT(CASE WHEN aa.EntscheidungId = 3 THEN aa.Id END) as GenehmigtAen,
    COUNT(CASE WHEN aa.EntscheidungId = 4 THEN aa.Id END) as AbgelehntAen,

    COUNT(ri.Id) as Risiken,
    COUNT(CASE WHEN ri.StatusId = 1 THEN ri.Id END) as ErstelltRis,
    COUNT(CASE WHEN ri.StatusId = 2 THEN ri.Id END) as AnalysiertRis,
    COUNT(CASE WHEN ri.StatusId = 3 THEN ri.Id END) as RegistriertRis,
    COUNT(CASE WHEN ri.StatusId = 4 THEN ri.Id END) as AktiviertRis,
    COUNT(CASE WHEN ri.StatusId = 5 THEN ri.Id END) as GeschlossenRis,
    COUNT(CASE WHEN ri.StatusId = 2 and ri.SystemUpdatedOn <= DATEADD(week,-2,GETDATE()) THEN ri.Id END) as LaengerAlsAnalysiertRis,
    COUNT(CASE WHEN ri.StatusId != 5 and ri.SystemUpdatedOn <= DATEADD(month,-1,GETDATE()) THEN ri.Id END) as LaengerAlsBearbeitetRis,

    COUNT(DISTINCT ugm.UserId) as Mitarbeiter,
    ISNULL(SUM(CASE WHEN eb.KostenartId = 2 and vh.IsBudgetRequired = 1 THEN eb.Verbrauch END), 0) as Personalkosten,
    ISNULL(SUM(CASE WHEN eb.KostenartId = 3 and vh.IsBudgetRequired = 1 THEN eb.Verbrauch END), 0) as Fremdleistung
FROM dbo.Vorhaben vh
left join dbo.Anforderung af on af.VorhabenId = vh.Id
left join dbo.Aenderungsantrag aa on aa.VorhabenId = vh.Id
left join dbo.Risiko ri on ri.VorhabenId = vh.Id
left join dbo.Usergroupmembership ugm on ugm.SiteUrl = vh.SystemSPSiteUrl
left join dbo.[User] u on u.Id = ugm.UserId
left join dbo.Usergroup ug on ug.Id = ugm.UsergroupId
left join dbo.ExtendedBudget eb on eb.VorhabenId = vh.Id
WHERE vh.SystemIsActive = 1
and (af.SystemIsActive is null or af.SystemIsActive = 1)
and (aa.SystemIsActive is null or aa.SystemIsActive = 1)
and (ri.SystemIsActive is null or ri.SystemIsActive = 1)
and (ugm.SystemIsActive is null or ugm.SystemIsActive = 1)
and (u.SystemIsActive is null or u.SystemIsActive = 1)
and (ug.SystemIsActive is null or ug.SystemIsActive = 1)
and (eb.SystemIsActive is null or eb.SystemIsActive = 1)

and (eb.SystemCrudStatus is null or eb.SystemCrudStatus = 1)
and (ugm.UsergroupId is null or ugm.UsergroupId = 1 or ugm.UsergroupId = 2 or ugm.UsergroupId = 3)
GROUP BY vh.Id, vh.VorhabensartId, vh.Beginn, vh.Ende