
ALTER PROCEDURE [dbo].[rpt_VOCA]
	(@StartDate SMALLDATETIME, @EndDate SMALLDATETIME, @ReportNumber INT)
	AS       
    -- The clients retrieved in the victims and services queries are in four groups:
    -- Participants, Children, Community Members, and Callers
    -- The retrieved records will have a unique Client ID as follows:
    -- ParticipantID, ChildID + 1000000, MemberID + 2000000, CallID + 3000000

    -- TOTAL VICTIMS SERVED
    -- Includes all victims served during the report period.
    -- Victims with more than one victim type will have a row for each type, otherwise the rows are distinct (UNION, not UNION ALL)
    -- Subsequent occurrences for the same client will be marked as duplicates so the report
    -- can filter them out of totals.
        DECLARE @False BIT SET @False = CAST(0 AS BIT);

        -- Resident Participant Victims with intakes in VOCA grant related shelter during report period
        SELECT ivt.VictimTypeID, g.GrantID, g.Prorate, g.ContractNumber, p.ParticipantID AS ClientID,
            Deaf = CAST(i.Deaf AS BIT), 
            Homeless = CAST(p.ChronicallyHomeless AS BIT),
            Immigrant = CAST(CASE WHEN i.CitizenshipID > 2 THEN 1 ELSE 0 END AS BIT),
            LGBT = CAST(p.LGBT AS BIT),
            Veteran = CAST(p.Veteran AS BIT),
            LimitedEnglish = CAST(CASE WHEN i.LanguageID > 2 THEN 1 ELSE 0 END AS BIT),
            Disability = CAST(CASE WHEN i.MentalIllness = 1 OR i.PhysDisability = 1 OR i.DevDisability = 1 OR i.HIVAIDS = 1
                OR i.ChronicHealthCondition = 1 THEN 1 ELSE 0 END AS BIT), IsDuplicate = @False,
            p.LastName + ', ' + p.FirstName + ' ' + p.MI AS ClientName, 
            i.AbusedAsChild, ChildPhysicalAbuse = @False, ChildSexualAbuse = @False
        FROM Participants p INNER JOIN Intakes i ON p.ParticipantID = i.ParticipantID
            INNER JOIN IntakeVictimType ivt ON i.IntakeID = ivt.IntakeID
            INNER JOIN GrantTargets gt ON i.ShelterID = gt.TargetID AND gt.TargetTypeID = 1
            INNER JOIN Grants g ON gt.GrantID = g.GrantID
        WHERE i.EntryDate <= @EndDate AND (i.ExitDate IS NULL OR i.ExitDate >= @StartDate)
            AND i.DeletedDate IS NULL AND g.DeletedDate IS NULL AND p.DeletedDate IS NULL
            AND g.GrantorID = 9 AND @StartDate BETWEEN g.GrantStart AND g.GrantEnd
			AND i.IntakeTypeID IN (1, 2, 3, 5)
		UNION
		-- Non-Resident Participant Victims with intakes in VOCA grant related shelter during report period
		-- who received a VOCA-related service during the period
        SELECT ivt.VictimTypeID, g.GrantID, g.Prorate, g.ContractNumber, p.ParticipantID AS ClientID,
            Deaf = CAST(i.Deaf AS BIT), 
            Homeless = CAST(p.ChronicallyHomeless AS BIT),
            Immigrant = CAST(CASE WHEN i.CitizenshipID > 2 THEN 1 ELSE 0 END AS BIT),
            LGBT = CAST(p.LGBT AS BIT),
            Veteran = CAST(p.Veteran AS BIT),
            LimitedEnglish = CAST(CASE WHEN i.LanguageID > 2 THEN 1 ELSE 0 END AS BIT),
            Disability = CAST(CASE WHEN i.MentalIllness = 1 OR i.PhysDisability = 1 OR i.DevDisability = 1 OR i.HIVAIDS = 1
                OR i.ChronicHealthCondition = 1 THEN 1 ELSE 0 END AS BIT), IsDuplicate = @False,
            p.LastName + ', ' + p.FirstName + ' ' + p.MI AS ClientName, 
            i.AbusedAsChild, ChildPhysicalAbuse = @False, ChildSexualAbuse = @False
        FROM Participants p INNER JOIN Intakes i ON p.ParticipantID = i.ParticipantID
            INNER JOIN IntakeVictimType ivt ON i.IntakeID = ivt.IntakeID
            INNER JOIN GrantTargets gt ON i.ShelterID = gt.TargetID AND gt.TargetTypeID = 1
            INNER JOIN Grants g ON gt.GrantID = g.GrantID
        WHERE i.EntryDate <= @EndDate AND (i.ExitDate IS NULL OR i.ExitDate >= @StartDate)
            AND i.DeletedDate IS NULL AND g.DeletedDate IS NULL AND p.DeletedDate IS NULL
            AND g.GrantorID = 9 AND @StartDate BETWEEN g.GrantStart AND g.GrantEnd
			AND i.IntakeTypeID NOT IN (1, 2, 3, 5)
			AND (SELECT TOP 1 cs.SessionID
				FROM ClientSessions cs INNER JOIN Sessions s ON cs.SessionID = s.SessionID
					INNER JOIN ServiceFocus sf ON s.FocusID = sf.FocusID AND s.ServiceID = sf.ServiceID
					INNER JOIN ServiceMap sm ON sf.ServiceFocusID = sm.ServiceFocusID
				WHERE cs.ClientID = p.ParticipantID AND cs.ClientTypeID = 1
					AND s.SessionDate BETWEEN @StartDate AND @EndDate
					AND sm.GrantorID = 9) IS NOT NULL
        UNION
        -- Resident Child Victims with intakes in VOCA grant related shelter during report period
        SELECT ivt.VictimTypeID, g.GrantID, g.Prorate, g.ContractNumber, ci.ChildID + 1000000 AS ClientID,
            Deaf = @False, Homeless =CAST(p.ChronicallyHomeless AS BIT),
            Immigrant = CAST(CASE WHEN i.CitizenshipID > 2 THEN 1 ELSE 0 END AS BIT),
            LGBT = @False, Veteran = @False, LimitedEnglish = @False,
            Disability = CAST(CASE WHEN ci.MentalIllness = 1 OR ci.PhysDisability = 1 OR ci.DevDisability = 1 OR ci.HIVAIDS = 1
                OR ci.ChronicHealthCondition = 1 THEN 1 ELSE 0 END AS BIT), IsDuplicate = @False,
            c.LastName + ', ' + c.FirstName + ' ' + c.MI AS ClientName,
            AbusedAsChild = @False, ci.AbusePhysical AS ChildPhysicalAbuse, ci.AbuseSexual AS ChildSexualAbuse
        FROM Children c INNER JOIN ChildIntakes ci ON c.ChildID = ci.ChildID
            INNER JOIN Intakes i ON ci.IntakeID = i.IntakeID
            INNER JOIN Participants p ON i.ParticipantID = p.ParticipantID
            INNER JOIN IntakeVictimType ivt ON i.IntakeID = ivt.IntakeID
            INNER JOIN GrantTargets gt ON i.ShelterID = gt.TargetID AND gt.TargetTypeID = 1
            INNER JOIN Grants g ON gt.GrantID = g.GrantID
        WHERE ci.EntryDate <= @EndDate AND (ci.ExitDate IS NULL OR ci.ExitDate >= @StartDate)
            AND i.DeletedDate IS NULL AND g.DeletedDate IS NULL AND ci.DeletedDate IS NULL
            AND g.GrantorID = 9 AND @StartDate BETWEEN g.GrantStart AND g.GrantEnd
			AND i.IntakeTypeID IN (1, 2, 3, 5)
		UNION
        -- Non-Resident Child Victims with intakes in VOCA grant related shelter during report period
		-- who received a VOCA-related service
        SELECT ivt.VictimTypeID, g.GrantID, g.Prorate, g.ContractNumber, ci.ChildID + 1000000 AS ClientID,
            Deaf = @False, Homeless =CAST(p.ChronicallyHomeless AS BIT),
            Immigrant = CAST(CASE WHEN i.CitizenshipID > 2 THEN 1 ELSE 0 END AS BIT),
            LGBT = @False, Veteran = @False, LimitedEnglish = @False,
            Disability = CAST(CASE WHEN ci.MentalIllness = 1 OR ci.PhysDisability = 1 OR ci.DevDisability = 1 OR ci.HIVAIDS = 1
                OR ci.ChronicHealthCondition = 1 THEN 1 ELSE 0 END AS BIT), IsDuplicate = @False,
            c.LastName + ', ' + c.FirstName + ' ' + c.MI AS ClientName,
            AbusedAsChild = @False, ci.AbusePhysical AS ChildPhysicalAbuse, ci.AbuseSexual AS ChildSexualAbuse
        FROM Children c INNER JOIN ChildIntakes ci ON c.ChildID = ci.ChildID
            INNER JOIN Intakes i ON ci.IntakeID = i.IntakeID
            INNER JOIN Participants p ON i.ParticipantID = p.ParticipantID
            INNER JOIN IntakeVictimType ivt ON i.IntakeID = ivt.IntakeID
            INNER JOIN GrantTargets gt ON i.ShelterID = gt.TargetID AND gt.TargetTypeID = 1
            INNER JOIN Grants g ON gt.GrantID = g.GrantID
        WHERE ci.EntryDate <= @EndDate AND (ci.ExitDate IS NULL OR ci.ExitDate >= @StartDate)
            AND i.DeletedDate IS NULL AND g.DeletedDate IS NULL AND ci.DeletedDate IS NULL
            AND g.GrantorID = 9 AND @StartDate BETWEEN g.GrantStart AND g.GrantEnd
			AND i.IntakeTypeID NOT IN (1, 2, 3, 5)
			AND (SELECT TOP 1 cs.SessionID
				FROM ClientSessions cs INNER JOIN Sessions s ON cs.SessionID = s.SessionID
					INNER JOIN ServiceFocus sf ON s.FocusID = sf.FocusID AND s.ServiceID = sf.ServiceID
					INNER JOIN ServiceMap sm ON sf.ServiceFocusID = sm.ServiceFocusID
				WHERE cs.ClientID = c.ChildID AND cs.ClientTypeID = 2
					AND s.SessionDate BETWEEN @StartDate AND @EndDate
					AND sm.GrantorID = 9) IS NOT NULL
        UNION
        -- Participant victims served by grant-related PROGRAM
        SELECT ivt.VictimTypeID, g.GrantID, g.Prorate, g.ContractNumber, p.ParticipantID AS ClientID,
            Deaf = CAST(i.Deaf AS BIT),
            Homeless = CAST(p.ChronicallyHomeless AS BIT),
            Immigrant = CAST(CASE WHEN i.CitizenshipID > 2 THEN 1 ELSE 0 END AS BIT),
            LGBT = CAST(p.LGBT AS BIT),
            Veteran = CAST(p.Veteran AS BIT),
            LimitedEnglish = CAST(CASE WHEN i.LanguageID > 2 THEN 1 ELSE 0 END AS BIT),
            Disability = CAST(CASE WHEN i.MentalIllness = 1 OR i.PhysDisability = 1 OR i.DevDisability = 1 OR i.HIVAIDS = 1
                OR i.ChronicHealthCondition = 1 THEN 1 ELSE 0 END AS BIT), IsDuplicate = @False,
            p.LastName + ', ' + p.FirstName + ' ' + p.MI AS ClientName,
            i.AbusedAsChild, ChildPhysicalAbuse = @False, ChildSexualAbuse = @False
        FROM IntakeVictimType ivt INNER JOIN Intakes i ON ivt.IntakeID = i.IntakeID
            INNER JOIN	Participants p ON p.ParticipantID = i.ParticipantID
            INNER JOIN ClientSessions cs ON p.ParticipantID = cs.ClientID AND cs.ClientTypeID = 1
            INNER JOIN Sessions s ON cs.SessionID = s.SessionID
            INNER JOIN GrantTargets gt ON s.ProgramID = gt.TargetID AND gt.TargetTypeID = 2
            INNER JOIN Grants g ON gt.GrantID = g.GrantID
        WHERE s.SessionDate BETWEEN @StartDate and @EndDate
            AND i.EntryDate <= @EndDate AND (i.ExitDate IS NULL OR i.ExitDate >= @StartDate)
            AND p.DeletedDate IS NULL AND i.DeletedDate IS NULL AND s.DeletedDate IS NULL AND g.DeletedDate IS NULL
            AND g.GrantorID = 9 AND @StartDate BETWEEN g.GrantStart AND g.GrantEnd
        UNION 
        -- Child victims served by grant-related PROGRAM
        SELECT ivt.VictimTypeID, g.GrantID, g.Prorate, g.ContractNumber, ci.ChildID + 1000000 AS ClientID,
            Deaf = @False, Homeless =CAST(p.ChronicallyHomeless AS BIT),
            Immigrant = CAST(CASE WHEN i.CitizenshipID > 2 THEN 1 ELSE 0 END AS BIT),
            LGBT = @False, Veteran = @False, LimitedEnglish = @False,
            Disability = CAST(CASE WHEN ci.MentalIllness = 1 OR ci.PhysDisability = 1 OR ci.DevDisability = 1 OR ci.HIVAIDS = 1
                OR ci.ChronicHealthCondition = 1 THEN 1 ELSE 0 END AS BIT), IsDuplicate = @False,
            c.LastName + ', ' + c.FirstName + ' ' + c.MI AS ClientName,
            AbusedAsChild = @False, ci.AbusePhysical AS ChildPhysicalAbuse, ci.AbuseSexual AS ChildSexualAbuse
        FROM Children c INNER JOIN ChildIntakes ci ON c.ChildID = ci.ChildID
            INNER JOIN Intakes i ON ci.IntakeID = i.IntakeID
            INNER JOIN Participants p ON i.ParticipantID = p.ParticipantID
            INNER JOIN IntakeVictimType ivt ON i.IntakeID = ivt.IntakeID
            INNER JOIN ClientSessions cs ON cs.ClientID = ci.ChildID AND cs.ClientTypeID = 2
            INNER JOIN Sessions s ON cs.SessionID = s.SessionID
            INNER JOIN GrantTargets gt ON s.ProgramID = gt.TargetID AND gt.TargetTypeID = 2
            INNER JOIN Grants g ON gt.GrantID = g.GrantID
        WHERE s.SessionDate BETWEEN @StartDate and @EndDate
            AND ci.EntryDate <= @EndDate AND (ci.ExitDate IS NULL OR ci.ExitDate >= @StartDate)
            AND ci.DeletedDate IS NULL AND i.DeletedDate IS NULL AND s.DeletedDate IS NULL AND g.DeletedDate IS NULL
            AND g.GrantorID = 9 AND @StartDate BETWEEN g.GrantStart AND g.GrantEnd
        UNION
        -- Community member victims served by VOCA grant-related PROGRAM
        SELECT cm.VictimTypeID, g.GrantID, g.Prorate, g.ContractNumber, cm.MemberID + 2000000 AS ClientID,
            Deaf = @False, Homeless = @False, Immigrant = @False, LGBT = @False, Veteran = @False,
            LimitedEnglish = @False, Disability = @False, IsDuplicate = @False,
            cm.LastName + ', ' + cm.FirstName + ' ' + cm.MI AS ClientName,
            @False, @False, @False
        FROM CommunityMembers cm INNER JOIN ClientSessions cs ON cm.MemberID = cs.ClientID AND cs.ClientTypeID = 4
            INNER JOIN Sessions s ON cs.SessionID = s.SessionID
            INNER JOIN GrantTargets gt ON s.ProgramID = gt.TargetID AND gt.TargetTypeID = 2
            INNER JOIN Grants g ON gt.GrantID = g.GrantID
        WHERE cm.VictimTypeID BETWEEN 3 AND 99
            AND s.SessionDate BETWEEN @StartDate AND @EndDate
            AND cm.DeletedDate IS NULL AND s.DeletedDate IS NULL AND g.DeletedDate IS NULL
            AND g.GrantorID = 9 AND @StartDate BETWEEN g.GrantStart AND g.GrantEnd
        UNION
        -- Caller victims served by grant-related SHELTER hotline (no linked intake)
        SELECT c.CallSubjectID AS VictimTypeID, g.GrantID, g.Prorate, g.ContractNumber, c.CallID + 3000000 AS ClientID,
            Deaf = @False, Homeless = @False, Immigrant = @False, LGBT = @False, Veteran = @False,
            LimitedEnglish = @False, Disability = @False, IsDuplicate = @False,
            c.LastName + ', ' + c.FirstName + ' ' + c.MI AS ClientName,
            @False, @False, @False
        FROM Calls c INNER JOIN Shelters s ON c.ShelterID = s.ShelterID
            INNER JOIN GrantTargets gt ON s.ShelterID = gt.TargetID AND gt.TargetTypeID = 1
            INNER JOIN Grants g ON gt.GrantID = g.GrantID
            LEFT OUTER JOIN IntakeCalls ic ON c.CallID = ic.CallID
        WHERE CallDate BETWEEN @StartDate AND @EndDate
            AND CallSubjectID BETWEEN 3 AND 99
            AND c.DeletedDate IS NULL AND g.DeletedDate IS NULL
            AND c.CallTypeID < 4
            AND ic.IntakeID IS NULL
            AND g.GrantorID = 9 AND @StartDate BETWEEN g.GrantStart AND g.GrantEnd
        UNION
        -- Participant victims served by grant-funded staff
        SELECT ivt.VictimTypeID, g.GrantID, g.Prorate, g.ContractNumber, p.ParticipantID AS ClientID,
            Deaf = CAST(i.Deaf AS BIT),
            Homeless = CAST(p.ChronicallyHomeless AS BIT),
            Immigrant = CAST(CASE WHEN i.CitizenshipID > 2 THEN 1 ELSE 0 END AS BIT),
            LGBT = CAST(p.LGBT AS BIT),
            Veteran = CAST(p.Veteran AS BIT),
            LimitedEnglish = CAST(CASE WHEN i.LanguageID > 2 THEN 1 ELSE 0 END AS BIT),
            Disability = CAST(CASE WHEN i.MentalIllness = 1 OR i.PhysDisability = 1 OR i.DevDisability = 1 OR i.HIVAIDS = 1
                OR i.ChronicHealthCondition = 1 THEN 1 ELSE 0 END AS BIT), IsDuplicate = @False,
            p.LastName + ', ' + p.FirstName + ' ' + p.MI AS ClientName,
            i.AbusedAsChild, ChildPhysicalAbuse = @False, ChildSexualAbuse = @False
        FROM Participants p INNER JOIN Intakes i ON p.ParticipantID = i.ParticipantID
            INNER JOIN IntakeVictimType ivt ON i.IntakeID = ivt.IntakeID
            INNER JOIN ClientSessions cs ON i.ParticipantID = cs.ClientID AND cs.ClientTypeID = 1
            INNER JOIN Sessions s ON cs.SessionID = s.SessionID
            INNER JOIN StaffSessions ss ON ss.SessionID = s.SessionID
            INNER JOIN GrantTargets gt ON ss.StaffID = gt.TargetID AND gt.TargetTypeID = 4
            INNER JOIN Grants g ON gt.GrantID = g.GrantID
        WHERE s.SessionDate BETWEEN @StartDate AND @EndDate
            AND i.EntryDate <= @EndDate AND (i.ExitDate IS NULL OR i.ExitDate >= @StartDate)
            AND p.DeletedDate IS NULL AND i.DeletedDate IS NULL AND s.DeletedDate IS NULL AND g.DeletedDate IS NULL
            AND g.GrantorID = 9 AND @StartDate BETWEEN g.GrantStart AND g.GrantEnd
        UNION
        -- Child victims served by grant-funded staff
        SELECT ivt.VictimTypeID, g.GrantID, g.Prorate, g.ContractNumber, ci.ChildID + 1000000 AS ClientID,
            Deaf = @False, Homeless =CAST(p.ChronicallyHomeless AS BIT), 
            Immigrant = CAST(CASE WHEN i.CitizenshipID > 2 THEN 1 ELSE 0 END AS BIT),
            LGBT = @False, Veteran = @False, LimitedEnglish = @False,
            Disability = CAST(CASE WHEN ci.MentalIllness = 1 OR ci.PhysDisability = 1 OR ci.DevDisability = 1 OR ci.HIVAIDS = 1
                OR ci.ChronicHealthCondition = 1 THEN 1 ELSE 0 END AS BIT), IsDuplicate = @False,
            c.LastName + ', ' + c.FirstName + ' ' + c.MI AS ClientName,
            AbusedAsChild = @False, ci.AbusePhysical AS ChildPhysicalAbuse, ci.AbuseSexual AS ChildSexualAbuse
        FROM Children c INNER JOIN ChildIntakes ci ON c.ChildID = ci.ChildID
            INNER JOIN Intakes i ON ci.IntakeID = i.IntakeID
            INNER JOIN Participants p ON i.ParticipantID = p.ParticipantID
            INNER JOIN IntakeVictimType ivt ON i.IntakeID = ivt.IntakeID
            INNER JOIN ClientSessions cs ON cs.ClientID = ci.ChildID AND cs.ClientTypeID = 2
            INNER JOIN Sessions s ON cs.SessionID = s.SessionID
            INNER JOIN StaffSessions ss ON ss.SessionID = s.SessionID
            INNER JOIN GrantTargets gt ON ss.StaffID = gt.TargetID AND gt.TargetTypeID = 4
            INNER JOIN Grants g ON gt.GrantID = g.GrantID
        WHERE s.SessionDate BETWEEN @StartDate AND @EndDate
            AND ci.EntryDate <= @EndDate AND (ci.ExitDate IS NULL OR ci.ExitDate >= @StartDate)
            AND ci.DeletedDate IS NULL AND i.DeletedDate IS NULL AND s.DeletedDate IS NULL AND g.DeletedDate IS NULL
            AND g.GrantorID = 9 AND @StartDate BETWEEN g.GrantStart AND g.GrantEnd
        UNION
        -- Community member victims served by grant-related staff
        SELECT cm.VictimTypeID, g.GrantID, g.Prorate, g.ContractNumber, cm.MemberID + 2000000 AS ClientID,
            Deaf = @False, Homeless = @False, Immigrant = @False, LGBT = @False, Veteran = @False,
            LimitedEnglish = @False, Disability = @False, IsDuplicate = @False,
            cm.LastName + ', ' + cm.FirstName + ' ' + cm.MI AS ClientName,
            @False, @False, @False
        FROM CommunityMembers cm INNER JOIN ClientSessions cs ON cm.MemberID = cs.ClientID AND cs.ClientTypeID = 4
            INNER JOIN Sessions s ON cs.SessionID = s.SessionID
            INNER JOIN StaffSessions ss ON s.SessionID = ss.SessionID
            INNER JOIN GrantTargets gt ON ss.StaffID = gt.TargetID AND gt.TargetTypeID = 4
            INNER JOIN Grants g ON gt.GrantID = g.GrantID
        WHERE s.SessionDate BETWEEN @StartDate AND @EndDate
            AND cm.VictimTypeID BETWEEN 3 AND 99
            AND cm.DeletedDate IS NULL AND s.DeletedDate IS NULL AND g.DeletedDate IS NULL
            AND g.GrantorID = 9 AND @StartDate BETWEEN g.GrantStart AND g.GrantEnd
        ORDER BY g.GrantID, ClientID;

	-- ANONYMOUS VICTIMS
		SELECT COUNT(c.CallID) AS Anonymous, g.Prorate, g.ContractNumber
		FROM Calls c INNER JOIN Shelters s ON c.ShelterID = s.ShelterID
			INNER JOIN GrantTargets gt ON s.ShelterID = gt.TargetID AND gt.TargetTypeID = 1
			INNER JOIN Grants g ON gt.GrantID = g.GrantID
			LEFT OUTER JOIN IntakeCalls ic ON c.CallID = ic.CallID
		WHERE CallDate BETWEEN @StartDate AND @EndDate
			AND CallSubjectID BETWEEN 3 AND 99
			AND c.DeletedDate IS NULL AND g.DeletedDate IS NULL
			AND c.CallTypeID < 4
			AND g.GrantorID = 9 AND @StartDate BETWEEN g.GrantStart AND g.GrantEnd
			AND (c.LastName = '' OR c.FirstName = '')
		GROUP BY ContractNumber, Prorate

    -- NEW VICTIMS
        -- ALL CLIENTS DURING GRANT PERIOD BEFORE REPORT END
        -- THOSE WITH EVENT DATES BEFORE THE REPORT START WILL BE FILTERED
        -- OUT LATER

        -- Resident Participant Victims with intakes in VOCA grant related shelter during grant period
        SELECT g.GrantID, g.ContractNumber, g.Prorate, p.ParticipantID AS ClientID, p.EthnicityID, p.Sex,
            Age = DATEDIFF(year, p.DOB, i.EntryDate), EventDate = i.EntryDate, i.EntryDate, p.DOB,
            p.LastName + ', ' + p.FirstName + ' ' + p.MI AS ClientName
        FROM Participants p INNER JOIN Intakes i ON p.ParticipantID = i.ParticipantID
            INNER JOIN GrantTargets gt ON i.ShelterID = gt.TargetID AND gt.TargetTypeID = 1
            INNER JOIN Grants g ON gt.GrantID = g.GrantID
            LEFT OUTER JOIN IntakeVictimType ivt ON i.IntakeID = ivt.IntakeID
        WHERE i.EntryDate <= @EndDate AND (i.ExitDate IS NULL OR i.ExitDate >= g.GrantStart)
            AND ivt.VictimTypeID IS NOT NULL
            AND p.DeletedDate IS NULL AND i.DeletedDate IS NULL AND g.DeletedDate IS NULL
            AND g.GrantorID = 9 AND @StartDate BETWEEN g.GrantStart AND g.GrantEnd
			AND i.IntakeTypeID IN (1, 2, 3, 5)
		UNION ALL
        -- Non-Resident Participant Victims with intakes in VOCA grant related shelter during grant period
		-- who received a VOCA-related service during the period
        SELECT g.GrantID, g.ContractNumber, g.Prorate, p.ParticipantID AS ClientID, p.EthnicityID, p.Sex,
            Age = DATEDIFF(year, p.DOB, i.EntryDate), EventDate = i.EntryDate, i.EntryDate, p.DOB,
            p.LastName + ', ' + p.FirstName + ' ' + p.MI AS ClientName
        FROM Participants p INNER JOIN Intakes i ON p.ParticipantID = i.ParticipantID
            INNER JOIN GrantTargets gt ON i.ShelterID = gt.TargetID AND gt.TargetTypeID = 1
            INNER JOIN Grants g ON gt.GrantID = g.GrantID
            LEFT OUTER JOIN IntakeVictimType ivt ON i.IntakeID = ivt.IntakeID
        WHERE i.EntryDate <= @EndDate AND (i.ExitDate IS NULL OR i.ExitDate >= g.GrantStart)
            AND ivt.VictimTypeID IS NOT NULL
            AND p.DeletedDate IS NULL AND i.DeletedDate IS NULL AND g.DeletedDate IS NULL
            AND g.GrantorID = 9 AND @StartDate BETWEEN g.GrantStart AND g.GrantEnd
			AND i.IntakeTypeID NOT IN (1, 2, 3, 5)
			AND (SELECT TOP 1 cs.SessionID
				FROM ClientSessions cs INNER JOIN Sessions s ON cs.SessionID = s.SessionID
					INNER JOIN ServiceFocus sf ON s.FocusID = sf.FocusID AND s.ServiceID = sf.ServiceID
					INNER JOIN ServiceMap sm ON sf.ServiceFocusID = sm.ServiceFocusID
				WHERE cs.ClientID = p.ParticipantID AND cs.ClientTypeID = 1
					AND s.SessionDate BETWEEN @StartDate AND @EndDate
					AND sm.GrantorID = 9) IS NOT NULL
        UNION ALL
        -- Resident Child Victims with intakes in VOCA grant-related shelters during grant period
        SELECT g.GrantID, g.ContractNumber, g.Prorate, c.ChildID + 1000000 AS ClientID, c.EthnicityID, c.Sex,
            Age = DATEDIFF(year, c.DOB, ci.EntryDate), EventDate = ci.EntryDate, ci.EntryDate, c.DOB,
            c.LastName + ', ' + c.FirstName + ' ' + c.MI AS ClientName
        FROM Children c INNER JOIN ChildIntakes ci ON c.ChildID = ci.ChildID
            INNER JOIN Intakes i ON ci.IntakeID = i.IntakeID
            INNER JOIN GrantTargets gt ON i.ShelterID = gt.TargetID AND gt.TargetTypeID = 1
            INNER JOIN Grants g ON gt.GrantID = g.GrantID
            LEFT OUTER JOIN IntakeVictimType ivt ON i.IntakeID = ivt.IntakeID
        WHERE ci.EntryDate <= @EndDate AND (ci.ExitDate IS NULL OR ci.ExitDate >= g.GrantStart)
            AND ivt.VictimTypeID IS NOT NULL
            AND c.DeletedDate IS NULL AND ci.DeletedDate IS NULL AND i.DeletedDate IS NULL AND g.DeletedDate IS NULL
            AND g.GrantorID = 9 AND @StartDate BETWEEN g.GrantStart AND g.GrantEnd
			AND i.IntakeTypeID IN (1, 2, 3, 5)
		UNION ALL
        -- Non-Resident Child Victims with intakes in VOCA grant-related shelters during grant period
		-- who received a VOCA-related service
        SELECT g.GrantID, g.ContractNumber, g.Prorate, c.ChildID + 1000000 AS ClientID, c.EthnicityID, c.Sex,
            Age = DATEDIFF(year, c.DOB, ci.EntryDate), EventDate = ci.EntryDate, ci.EntryDate, c.DOB,
            c.LastName + ', ' + c.FirstName + ' ' + c.MI AS ClientName
        FROM Children c INNER JOIN ChildIntakes ci ON c.ChildID = ci.ChildID
            INNER JOIN Intakes i ON ci.IntakeID = i.IntakeID
            INNER JOIN GrantTargets gt ON i.ShelterID = gt.TargetID AND gt.TargetTypeID = 1
            INNER JOIN Grants g ON gt.GrantID = g.GrantID
            LEFT OUTER JOIN IntakeVictimType ivt ON i.IntakeID = ivt.IntakeID
        WHERE ci.EntryDate <= @EndDate AND (ci.ExitDate IS NULL OR ci.ExitDate >= g.GrantStart)
            AND ivt.VictimTypeID IS NOT NULL
            AND c.DeletedDate IS NULL AND ci.DeletedDate IS NULL AND i.DeletedDate IS NULL AND g.DeletedDate IS NULL
            AND g.GrantorID = 9 AND @StartDate BETWEEN g.GrantStart AND g.GrantEnd
			AND i.IntakeTypeID NOT IN (1, 2, 3, 5)
			AND (SELECT TOP 1 cs.SessionID
				FROM ClientSessions cs INNER JOIN Sessions s ON cs.SessionID = s.SessionID
					INNER JOIN ServiceFocus sf ON s.FocusID = sf.FocusID AND s.ServiceID = sf.ServiceID
					INNER JOIN ServiceMap sm ON sf.ServiceFocusID = sm.ServiceFocusID
				WHERE cs.ClientID = c.ChildID AND cs.ClientTypeID = 2
					AND s.SessionDate BETWEEN @StartDate AND @EndDate
					AND sm.GrantorID = 9) IS NOT NULL
        UNION ALL
        -- Non-Anonymous Caller Victims served by VOCA grant-realted Shelter hotline (no linked intake)
        SELECT g.GrantID, g.ContractNumber, g.Prorate, c.CallID + 3000000 AS ClientID, EthnicityID = 1, c.Sex,
            Age = -1, c.CallDate AS EventDate, c.CallDate, DOB = NULL,
            c.LastName + ', ' + c.FirstName AS ClientName
        FROM Calls c INNER JOIN Shelters s ON c.ShelterID = s.ShelterID
            INNER JOIN GrantTargets gt ON s.ShelterID = gt.TargetID AND gt.TargetTypeID = 1
            INNER JOIN Grants g ON gt.GrantID = g.GrantID
            LEFT OUTER JOIN IntakeCalls ic ON c.CallID = ic.CallID
        WHERE c.CallDate BETWEEN @StartDate AND @EndDate
            AND c.CallTypeID < 4 AND c.CallSubjectID BETWEEN 3 AND 99
            AND ic.IntakeID IS NULL
            AND c.DeletedDate IS NULL AND g.DeletedDate IS NULL
            AND g.GrantorID = 9 AND @StartDate BETWEEN g.GrantStart AND g.GrantEnd
			AND (c.LastName <> '' AND c.FirstName <> '')
        UNION ALL
        -- Participant victims served by VOCA grant-related Program
        SELECT g.GrantID, g.ContractNumber, g.Prorate, p.ParticipantID AS ClientID, p.EthnicityID, p.Sex,
            Age = DATEDIFF(year, p.DOB, i.EntryDate), EventDate = i.EntryDate, i.EntryDate, p.DOB,
            p.LastName + ', ' + p.FirstName + ' ' + p.MI AS ClientName
        FROM IntakeVictimType ivt INNER JOIN Intakes i ON ivt.IntakeID = i.IntakeID
            INNER JOIN	Participants p ON p.ParticipantID = i.ParticipantID
            INNER JOIN ClientSessions cs ON p.ParticipantID = cs.ClientID AND cs.ClientTypeID = 1
            INNER JOIN Sessions s ON cs.SessionID = s.SessionID
            INNER JOIN GrantTargets gt ON s.ProgramID = gt.TargetID AND gt.TargetTypeID = 2
            INNER JOIN Grants g ON gt.GrantID = g.GrantID
        WHERE s.SessionDate BETWEEN g.GrantStart AND @EndDate
            AND i.EntryDate <= @EndDate AND (i.ExitDate IS NULL OR i.ExitDate >= g.GrantStart)
            AND p.DeletedDate IS NULL AND i.DeletedDate IS NULL AND s.DeletedDate IS NULL AND g.DeletedDate IS NULL
            AND g.GrantorID = 9 AND @StartDate BETWEEN g.GrantStart AND g.GrantEnd
        UNION ALL
        -- Child victims served by VOCA grant-related Program
        SELECT g.GrantID, g.ContractNumber, g.Prorate, c.ChildID + 1000000 AS ClientID, c.EthnicityID, c.Sex,
            Age = DATEDIFF(year, c.DOB, ci.EntryDate), EventDate = ci.EntryDate, ci.EntryDate, c.DOB,
            c.LastName + ', ' + c.FirstName + ' ' + c.MI AS ClientName
        FROM Children c INNER JOIN 	ChildIntakes ci ON c.ChildID = ci.ChildID
            INNER JOIN Intakes i ON ci.IntakeID = i.IntakeID
            INNER JOIN Participants p ON i.ParticipantID = p.ParticipantID
            INNER JOIN IntakeVictimType ivt ON i.IntakeID = ivt.IntakeID
            INNER JOIN ClientSessions cs ON cs.ClientID = ci.ChildID AND cs.ClientTypeID = 2
            INNER JOIN Sessions s ON cs.SessionID = s.SessionID
            INNER JOIN GrantTargets gt ON s.ProgramID = gt.TargetID AND gt.TargetTypeID = 2
            INNER JOIN Grants g ON gt.GrantID = g.GrantID
        WHERE s.SessionDate BETWEEN @StartDate and @EndDate
            AND ci.EntryDate <= @EndDate AND (ci.ExitDate IS NULL OR ci.ExitDate >= @StartDate)
            AND ci.DeletedDate IS NULL AND i.DeletedDate IS NULL AND s.DeletedDate IS NULL AND g.DeletedDate IS NULL
            AND g.GrantorID = 9 AND @StartDate BETWEEN g.GrantStart AND g.GrantEnd
        UNION ALL
        -- Community member victims served by VOCA grant-related Program
        SELECT g.GrantID, g.ContractNumber, g.Prorate, cm.MemberID + 2000000 AS ClientID, cm.EthnicityID, cm.Sex,
            Age = CASE WHEN cm.DOB IS NULL THEN -1 ELSE DATEDIFF(year, cm.DOB, s.SessionDate) END, EventDate = s.SessionDate,
            s.SessionDate AS EventDate, cm.DOB,
            cm.LastName + ', ' + cm.FirstName + ' ' + cm.MI AS ClientName
        FROM CommunityMembers cm INNER JOIN ClientSessions cs ON cm.MemberID = cs.ClientID AND cs.ClientTypeID = 4
            INNER JOIN Sessions s ON cs.SessionID = s.SessionID
            INNER JOIN GrantTargets gt ON s.ProgramID = gt.TargetID AND gt.TargetTypeID = 2
            INNER JOIN Grants g ON gt.GrantID = g.GrantID
        WHERE cm.VictimTypeID BETWEEN 3 AND 99
            AND s.SessionDate BETWEEN g.GrantStart AND @EndDate
            AND cm.DeletedDate IS NULL AND s.DeletedDate IS NULL AND g.DeletedDate IS NULL
            AND g.GrantorID = 9 AND @StartDate BETWEEN g.GrantStart AND g.GrantEnd
        UNION ALL
        -- Participant victims served by grant-related staff
        SELECT g.GrantID, g.ContractNumber, g.Prorate, p.ParticipantID AS ClientID, p.EthnicityID, p.Sex,
            Age = DATEDIFF(year, p.DOB, i.EntryDate), s.SessionDate AS EventDate, i.EntryDate, p.DOB,
            p.LastName + ', ' + p.FirstName + ' ' + p.MI AS ClientName
        FROM Participants p INNER JOIN Intakes i ON p.ParticipantID = i.ParticipantID
            INNER JOIN ClientSessions cs ON p.ParticipantID = cs.ClientID AND cs.ClientTypeID = 1
            INNER JOIN Sessions s ON cs.SessionID = s.SessionID
            INNER JOIN StaffSessions ss ON s.SessionID = ss.SessionID
            INNER JOIN GrantTargets gt ON ss.StaffID = gt.TargetID AND gt.TargetTypeID = 4
            INNER JOIN Grants g ON gt.GrantID = g.GrantID
            LEFT OUTER JOIN IntakeVictimType ivt ON ivt.IntakeID = i.IntakeID
        WHERE ivt.VictimTypeID IS NOT NULL
            AND s.SessionDate BETWEEN g.GrantStart AND @EndDate
            AND i.DeletedDate IS NULL AND p.DeletedDate IS NULL AND g.DeletedDate IS NULL
            AND s.DeletedDate IS NULL
            AND g.GrantorID = 9 AND @StartDate BETWEEN g.GrantStart AND g.GrantEnd
        UNION ALL
        -- Child victims served by grant-related staff
        SELECT g.GrantID, g.ContractNumber, g.Prorate, c.ChildID + 1000000 AS ClientID, c.EthnicityID, c.Sex,
            Age = DATEDIFF(year, c.DOB, ci.EntryDate), EventDate = s.SessionDate, ci.EntryDate, c.DOB,
            c.LastName + ', ' + c.FirstName + ' ' + c.MI AS ClientName
        FROM Children c INNER JOIN ChildIntakes ci ON c.ChildID = ci.ChildID
            INNER JOIN Intakes i ON ci.IntakeID = i.IntakeID
            INNER JOIN ClientSessions cs ON c.ChildID = cs.ClientID AND cs.ClientTypeID = 2
            INNER JOIN Sessions s ON cs.SessionID = s.SessionID
            INNER JOIN StaffSessions ss ON s.SessionID = ss.SessionID
            INNER JOIN GrantTargets gt ON ss.StaffID = gt.TargetID AND gt.TargetTypeID = 4
            INNER JOIN Grants g ON gt.GrantID = g.GrantID
            LEFT OUTER JOIN IntakeVictimType ivt ON ivt.IntakeID = i.IntakeID
        WHERE ivt.VictimTypeID IS NOT NULL
            AND s.SessionDate BETWEEN g.GrantStart AND @EndDate
            AND c.DeletedDate IS NULL AND ci.DeletedDate IS NULL AND i.DeletedDate IS NULL
            AND s.DeletedDate IS NULL AND g.DeletedDate IS NULL
            AND g.GrantorID = 9 AND @StartDate BETWEEN g.GrantStart AND g.GrantEnd
        UNION ALL
        -- Community member victims served by grant-related staff
        SELECT g.GrantID, g.ContractNumber, g.Prorate, cm.MemberID + 2000000 AS ClinetID, cm.EthnicityID, cm.Sex,
            Age = CASE WHEN cm.DOB IS NULL THEN -1 ELSE DATEDIFF(year, cm.DOB, s.SessionDate) END, EventDate = s.SessionDate,
            s.SessionDate AS EntryDate, cm.DOB,
            cm.LastName + ', ' + cm.FirstName + ' ' + cm.MI AS ClientName
        FROM CommunityMembers cm INNER JOIN ClientSessions cs ON cm.MemberID = cs.ClientID AND cs.ClientTypeID = 4
            INNER JOIN Sessions s ON cs.SessionID = s.SessionID
            INNER JOIN StaffSessions ss ON s.SessionID = ss.SessionID
            INNER JOIN GrantTargets gt ON ss.StaffID = gt.TargetID AND gt.TargetTypeID = 4
            INNER JOIN Grants g ON gt.GrantID = g.GrantID
        WHERE cm.VictimTypeID BETWEEN 3 AND 99
            AND s.SessionDate BETWEEN g.GrantStart AND @EndDate
            AND cm.DeletedDate IS NULL AND s.DeletedDate IS NULL AND g.DeletedDate IS NULL
            AND g.GrantorID = 9 AND @StartDate BETWEEN g.GrantStart AND g.GrantEnd
        ORDER BY g.GrantID, ClientID, EventDate; 

    -- DIRECT SERVICES
        -- Services to Participant victims served by VOCA grant-related SHELTER
        SELECT ByShelter = CAST(1 AS BIT), g.GrantID, g.ContractNumber, g.Prorate, sm.GrantorServiceID, p.ParticipantID AS ClientID,
            ClientType = 1, p.LastName + ', ' + p.FirstName + ' ' + p.MI AS ClientName, s.SessionDate AS EventDate, s.SessionID AS EventID,
            ServiceUnits = 1
        FROM Grants g INNER JOIN GrantorServices gs ON g.GrantorID = gs.GrantorID
            INNER JOIN ServiceMap sm on gs.GrantorServiceID = sm.GrantorServiceID
            INNER JOIN ServiceFocus sf ON sm.ServiceFocusID = sf.ServiceFocusID
            INNER JOIN [Sessions] s ON sf.ServiceID = s.ServiceID AND sf.FocusID = s.FocusID
            INNER JOIN ClientSessions cs ON cs.SessionID = s.SessionID
            INNER JOIN Intakes i ON i.IntakeID = cs.IntakeID AND cs.ClientTypeID = 1
            INNER JOIN Participants p ON i.ParticipantID = p.ParticipantID
            INNER JOIN GrantTargets gt ON i.ShelterID = gt.TargetID AND gt.TargetTypeID = 1 AND gt.GrantID = g.GrantID
            LEFT OUTER JOIN IntakeVictimType ivt ON i.IntakeID = ivt.IntakeID
        WHERE g.GrantorID = 9 AND gs.GrantorServiceID BETWEEN 901 AND 932
            AND @StartDate BETWEEN g.GrantStart AND g.GrantEnd
            AND s.SessionDate BETWEEN @StartDate AND @EndDate
            AND ivt.VictimTypeID IS NOT NULL
            AND g.DeletedDate IS NULL AND s.DeletedDate IS NULL AND cs.DeletedDate IS NULL
            AND @StartDate BETWEEN g.GrantStart ANd g.GrantEnd
        UNION ALL
        -- Services to Participant victims served by VOCA grant-related PROGRAM
        SELECT ByShelter = CAST(0 AS BIT), g.GrantID, g.ContractNumber, g.Prorate, sm.GrantorServiceID, p.ParticipantID AS ClientID,
            ClientType = 1, p.LastName + ', ' + p.FirstName + ' ' + p.MI AS ClientName, s.SessionDate AS EventDate, s.SessionID AS EventID,
            ServiceUnits = 1
        FROM Grants g INNER JOIN GrantorServices gs ON g.GrantorID = gs.GrantorID
            INNER JOIN ServiceMap sm on gs.GrantorServiceID = sm.GrantorServiceID
            INNER JOIN ServiceFocus sf ON sm.ServiceFocusID = sf.ServiceFocusID
            INNER JOIN [Sessions] s ON sf.ServiceID = s.ServiceID AND sf.FocusID = s.FocusID
            INNER JOIN ClientSessions cs ON cs.SessionID = s.SessionID
            INNER JOIN Intakes i ON i.IntakeID = cs.IntakeID AND cs.ClientTypeID = 1
            INNER JOIN Participants p ON i.ParticipantID = p.ParticipantID
            INNER JOIN GrantTargets gt ON s.ProgramID = gt.TargetID AND gt.TargetTypeID = 2 AND gt.GrantID = g.GrantID
            LEFT OUTER JOIN IntakeVictimType ivt ON i.IntakeID = ivt.IntakeID
        WHERE g.GrantorID = 9 AND gs.GrantorServiceID BETWEEN 901 AND 932
            AND @StartDate BETWEEN g.GrantStart AND g.GrantEnd
            AND s.SessionDate BETWEEN @StartDate AND @EndDate
            AND ivt.VictimTypeID IS NOT NULL
            AND g.DeletedDate IS NULL AND s.DeletedDate IS NULL AND cs.DeletedDate IS NULL
            AND @StartDate BETWEEN g.GrantStart ANd g.GrantEnd
        UNION ALL
        -- Services to Participant victims served by VOCA grant-related STAFF
        SELECT ByShelter = CAST(0 AS BIT), g.GrantID,  g.ContractNumber, g.Prorate, sm.GrantorServiceID, p.ParticipantID AS ClientID,
            ClientType = 1, p.LastName + ', ' + p.FirstName + ' ' + p.MI AS ClientName, s.SessionDate AS EventDate, s.SessionID AS EventID,
            ServiceUnits = 1
        FROM Grants g INNER JOIN GrantorServices gs ON g.GrantorID = gs.GrantorID
            INNER JOIN ServiceMap sm ON gs.GrantorServiceID = sm.GrantorServiceID
            INNER JOIN ServiceFocus sf ON sm.ServiceFocusID = sf.ServiceFocusID
            INNER JOIN [Sessions] s ON sf.ServiceID = s.ServiceID AND sf.FocusID = s.FocusID
            INNER JOIN ClientSessions cs ON cs.SessionID = s.SessionID
            INNER JOIN StaffSessions ss ON cs.SessionID = ss.SessionID
            INNER JOIN Intakes i ON i.IntakeID = cs.IntakeID AND cs.ClientTypeID = 1
            INNER JOIN Participants p ON i.ParticipantID = p.ParticipantID
            INNER JOIN GrantTargets gt ON ss.StaffID = gt.TargetID AND gt.TargetTypeID = 4 AND gt.GrantID = g.GrantID
            LEFT OUTER JOIN IntakeVictimType ivt ON i.IntakeID = ivt.IntakeID
        WHERE g.GrantorID = 9 AND gs.GrantorServiceID BETWEEN 901 and 932
            AND @StartDate BETWEEN g.GrantStart and g.GrantEnd
            AND s.SessionDate BETWEEN @StartDate and @EndDate
            AND ivt.VictimTypeID IS NOT NULL
            AND g.DeletedDate IS NULL and s.DeletedDate IS NULL AND cs.DeletedDate IS NULL
        UNION ALL
        -- Services to Community Member victims served by VOCA grant-related PROGRAM
        SELECT ByShelter = CAST(0 AS BIT), g.GrantID, g.ContractNumber, g.Prorate, sm.GrantorServiceID, cm.MemberID + 3000000 AS ClientID,
            ClientType = 1, cm.LastName + ', ' + cm.FirstName + ' ' + cm.MI AS ClientName, s.SessionDate AS EventDate, s.SessionID AS EventID,
            ServiceUnits = 1
        FROM CommunityMembers cm INNER JOIN ClientSessions cs ON cm.MemberID = cs.ClientID AND cs.ClientTypeID = 4
            INNER JOIN [Sessions] s ON cs.SessionID = s.SessionID
            INNER JOIN ServiceFocus sf ON s.ServiceID = sf.ServiceID AND s.FocusID = sf.FocusID
            INNER JOIN ServiceMap sm ON sf.ServiceFocusID = sm.ServiceFocusID
            INNER JOIN GrantorServices gs ON sm.GrantorServiceID = gs.GrantorServiceID
            INNER JOIN Grants g ON gs.GrantorID = g.GrantorID
            INNER JOIN GrantTargets gt ON gt.GrantID = g.GrantID AND gt.TargetTypeID = 2 AND gt.TargetID = s.ProgramID
        WHERE g.GrantorID = 9 AND gs.GrantorServiceID BETWEEN 901 AND 932
            AND @StartDate BETWEEN g.GrantStart AND g.GrantEnd
            AND s.SessionDate BETWEEN @StartDate AND @EndDate
            AND cm.VictimTypeID BETWEEN 3 AND 99
            AND g.DeletedDate IS NULL AND s.DeletedDate IS NULL AND cs.DeletedDate IS NULL
            AND @StartDate BETWEEN g.GrantStart ANd g.GrantEnd
        UNION ALL
        -- Services to Community Member victims served by VOCA grant-related STAFF
        SELECT ByShelter = CAST(0 AS BIT), g.GrantID, g.ContractNumber, g.Prorate, sm.GrantorServiceID, cm.MemberID + 3000000 AS ClientID,
            ClientType = 1, cm.LastName + ', ' + cm.FirstName + ' ' + cm.MI AS ClientName, s.SessionDate AS EventDate, s.SessionID AS EventID,
            ServiceUnits = 1
        FROM CommunityMembers cm INNER JOIN ClientSessions cs ON cm.MemberID = cs.ClientID AND cs.ClientTypeID = 4
            INNER JOIN [Sessions] s ON cs.SessionID = s.SessionID
        --    INNER JOIN ServiceMap sm ON s.ServiceID = sm.ServiceID
            INNER JOIN ServiceFocus sf ON s.ServiceID = sf.ServiceID AND s.FocusID = sf.FocusID
            INNER JOIN ServiceMap sm ON sf.ServiceFocusID = sm.ServiceFocusID
            INNER JOIN StaffSessions ss ON cs.SessionID = ss.SessionID
            INNER JOIN GrantorServices gs ON sm.GrantorServiceID = gs.GrantorServiceID
            INNER JOIN Grants g ON gs.GrantorID = g.GrantorID
            INNER JOIN GrantTargets gt ON gt.GrantID = g.GrantID AND gt.TargetTypeID = 4 AND gt.TargetID = ss.StaffID
        WHERE g.GrantorID = 9 AND gs.GrantorServiceID BETWEEN 901 AND 932
            AND @StartDate BETWEEN g.GrantStart AND g.GrantEnd
            AND s.SessionDate BETWEEN @StartDate AND @EndDate
            AND cm.VictimTypeID BETWEEN 3 AND 99
            AND g.DeletedDate IS NULL AND s.DeletedDate IS NULL AND cs.DeletedDate IS NULL
            AND @StartDate BETWEEN g.GrantStart AND g.GrantEnd
        UNION ALL
        -- Services to Child victims served by VOCA grant-related SHELTER
        SELECT ByShelter = CAST(1 AS BIT), g.GrantID, g.ContractNumber, g.Prorate, sm.GrantorServiceID, c.ChildID + 1000000 AS ClientID,
            ClientType = 1, c.LastName + ', ' + c.FirstName + ' ' + c.MI AS ClientName, s.SessionDate AS EventDate, s.SessionID AS EventID,
            ServiceUnits = 1
        FROM Grants g INNER JOIN GrantorServices gs on g.GrantorID = gs.GrantorID
            INNER JOIN ServiceMap sm ON gs.GrantorServiceID = sm.GrantorServiceID
            INNER JOIN ServiceFocus sf ON sf.ServiceFocusID = sm.ServiceFocusID
            INNER JOIN [Sessions] s on sf.ServiceID = s.ServiceID AND s.FocusID = sf.FocusID
            INNER JOIN ClientSessions cs ON cs.SessionID = s.SessionID
            INNER JOIN ChildIntakes ci ON cs.IntakeID = ci.ChildIntakeID AND cs.ClientTypeID = 2
            INNER JOIN Intakes i ON i.IntakeID = ci.IntakeID
            INNER JOIN Children c ON ci.ChildID = c.ChildID
            INNER JOIN GrantTargets gt ON i.ShelterID = gt.TargetID AND gt.TargetTypeID = 1 AND gt.GrantID = g.GrantID
            LEFT OUTER JOIN IntakeVictimType ivt ON i.IntakeID = ivt.IntakeID
        WHERE g.GrantorID = 9 AND gs.GrantorServiceID BETWEEN 901 AND 932
            AND @StartDate BETWEEN g.GrantStart AND g.GrantEnd
            AND s.SessionDate BETWEEN @StartDate AND @EndDate
            AND ivt.VictimTypeID IS NOT NULL
            AND g.DeletedDate IS NULL AND s.DeletedDate IS NULL AND cs.DeletedDate IS NULL
            AND @StartDate BETWEEN g.GrantStart ANd g.GrantEnd
        UNION ALL
        -- Service to Child victims served by VOCA grant-related PROGRAM
        SELECT ByShelter = CAST(0 AS BIT), g.GrantID, g.ContractNumber, g.Prorate, sm.GrantorServiceID, c.ChildID + 1000000 AS ClientID,
            ClientType = 1, c.LastName + ', ' + c.FirstName + ' ' + c.MI AS ClientName, s.SessionDate AS EventDate, s.SessionID AS EventID,
            ServiceUnits = 1
        FROM Grants g INNER JOIN GrantorServices gs on g.GrantorID = gs.GrantorID
            INNER JOIN ServiceMap sm ON gs.GrantorServiceID = sm.GrantorServiceID
        --    INNER JOIN [Sessions] s on sm.ServiceID = s.ServiceID
            INNER JOIN ServiceFocus sf ON sf.ServiceFocusID = sm.ServiceFocusID
            INNER JOIN [Sessions] s on sf.ServiceID = s.ServiceID AND s.FocusID = sf.FocusID
            INNER JOIN ClientSessions cs ON cs.SessionID = s.SessionID
            INNER JOIN ChildIntakes ci ON cs.IntakeID = ci.ChildIntakeID AND cs.ClientTypeID = 2
            INNER JOIN Intakes i ON i.IntakeID = ci.IntakeID
            INNER JOIN Children c ON ci.ChildID = c.ChildID
            INNER JOIN GrantTargets gt ON s.ProgramID = gt.TargetID AND gt.TargetTypeID = 2 AND gt.GrantID = g.GrantID
            LEFT OUTER JOIN IntakeVictimType ivt ON i.IntakeID = ivt.IntakeID
        WHERE g.GrantorID = 9 AND gs.GrantorServiceID BETWEEN 901 AND 932
            AND @StartDate BETWEEN g.GrantStart AND g.GrantEnd
            AND s.SessionDate BETWEEN @StartDate AND @EndDate
            AND ivt.VictimTypeID IS NOT NULL
            AND g.DeletedDate IS NULL AND s.DeletedDate IS NULL AND cs.DeletedDate IS NULL
            AND @StartDate BETWEEN g.GrantStart ANd g.GrantEnd
        UNION ALL
        -- Service to Child victims served by VOCA grant-related STAFF
        SELECT ByShelter = CAST(0 AS BIT), g.GrantID, g.ContractNumber, g.Prorate, sm.GrantorServiceID, c.ChildID + 1000000 AS ClientID,
            ClientType = 1, c.LastName + ', ' + c.FirstName + ' ' + c.MI AS ClientName, s.SessionDate AS EventDate, s.SessionID AS EventID,
            ServiceUnits = 1
        FROM Grants g INNER JOIN GrantorServices gs on g.GrantorID = gs.GrantorID
            INNER JOIN ServiceMap sm ON gs.GrantorServiceID = sm.GrantorServiceID
        --    INNER JOIN [Sessions] s on sm.ServiceID = s.ServiceID
            INNER JOIN ServiceFocus sf ON sf.ServiceFocusID = sm.ServiceFocusID
            INNER JOIN [Sessions] s on sf.ServiceID = s.ServiceID AND s.FocusID = sf.FocusID
            INNER JOIN ClientSessions cs ON cs.SessionID = s.SessionID
            INNER JOIN StaffSessions ss ON cs.SessionID = ss.SessionID
            INNER JOIN ChildIntakes ci ON cs.IntakeID = ci.ChildIntakeID AND cs.ClientTypeID = 2
            INNER JOIN Intakes i ON i.IntakeID = ci.IntakeID
            INNER JOIN Children c ON ci.ChildID = c.ChildID
            INNER JOIN GrantTargets gt ON ss.StaffID = gt.TargetID AND gt.TargetTypeID = 4 AND gt.GrantID = g.GrantID
            LEFT OUTER JOIN IntakeVictimType ivt ON i.IntakeID = ivt.IntakeID
        WHERE g.GrantorID = 9 AND gs.GrantorServiceID BETWEEN 901 AND 932
            AND @StartDate BETWEEN g.GrantStart AND g.GrantEnd
            AND s.SessionDate BETWEEN @StartDate AND @EndDate
            AND ivt.VictimTypeID IS NOT NULL
            AND g.DeletedDate IS NULL AND s.DeletedDate IS NULL AND cs.DeletedDate IS NULL
            AND @StartDate BETWEEN g.GrantStart ANd g.GrantEnd
        UNION ALL
        -- Services provided via Hotline mapped to VOCA grant services and hotline linked to VOCA shelter
        SELECT ByShelter = CAST(1 AS BIT), g.GrantID, g.ContractNumber, g.Prorate, sm.GrantorServiceID, c.CallID + 2000000 AS ClientID,
            ClientType = 3, c.LastName + ', ' + c.FirstName + ' ' + c.MI AS ClientName, c.CallDate AS EventDate, c.CallID AS EventID,
            ServiceUnits = 1
        FROM Grants g INNER JOIN GrantorServices gs ON g.GrantorID = gs.GrantorID
            INNER JOIN ServiceMap sm ON gs.GrantorServiceID =sm.GrantorServiceID
            INNER JOIN ServiceFocus sf ON sf.ServiceFocusID = sm.ServiceFocusID
            INNER JOIN CallServices cs ON sf.ServiceID = cs.ServiceID AND sf.FocusID = cs.FocusID
            INNER JOIN Calls c ON cs.CallID = c.CallID
            INNER JOIN GrantTargets gt ON gt.GrantID = g.GrantID AND gt.TargetTypeID = 1 AND gt.TargetID = c.ShelterID
        WHERE g.GrantorID = 9 AND gs.GrantorServiceID BETWEEN 901 AND 932
            AND @StartDate BETWEEN g.GrantStart AND g.GrantEnd
            AND c.CallSubjectID BETWEEN 3 AND 99
            AND c.CallDate BETWEEN @StartDate AND @EndDate
            AND c.DeletedDate IS NULL AND g.DeletedDate IS NULL
        UNION ALL
        -- Services provided via Hotline where the Program is linked to VOCA grant
        -- and the service is mapped to VOCA grant services
        SELECT ByShelter = CAST(0 AS BIT), g.GrantID, g.ContractNumber, g.Prorate, sm.GrantorServiceID, c.CallID + 2000000 AS ClientID,
            ClientType = 3, c.LastName + ', ' + c.FirstName + ' ' + c.MI AS ClientName, c.CallDate AS EventDate, c.CallID AS EventID,
            ServiceUnits = 1
        FROM Grants g INNER JOIN GrantorServices gs ON g.GrantorID = gs.GrantorID
            INNER JOIN ServiceMap sm ON gs.GrantorServiceID = sm.GrantorServiceID
            INNER JOIN ServiceFocus sf ON sf.ServiceFocusID = sm.ServiceFocusID
            INNER JOIN CallServices cs ON sf.ServiceID = cs.ServiceID AND sf.FocusID = cs.FocusID
            INNER JOIN Calls c ON cs.CallID = c.CallID
            INNER JOIN GrantTargets gt ON gt.GrantID = g.GrantID AND gt.TargetTypeID = 2 AND gt.TargetID = cs.ProgramID
        WHERE g.GrantorID = 9 AND gs.GrantorServiceID BETWEEN 901 AND 932
            AND @StartDate BETWEEN g.GrantStart AND g.GrantEnd
            AND c.CallSubjectID BETWEEN 3 AND 99
            AND c.CallDate BETWEEN @StartDate AND @EndDate
            AND c.DeletedDate IS NULL AND g.DeletedDate IS NULL  
        UNION ALL
        -- Info/Referral calls provided via Hotline mapped to VOCA grant shelter, 
        -- reported as Referral to other services, supports, and resources (936)
        SELECT ByShelter = CAST(1 AS BIT), g.GrantID, g.ContractNumber, g.Prorate, GrantorServiceID = 936, c.CallID + 2000000 AS ClientID,
            ClientType = 3, c.LastName + ', ' + c.FirstName + ' ' + c.MI AS ClientName, c.CallDate AS EventDate, c.CallID AS EventID,
            ServiceUnits = 1
        FROM Grants g INNER JOIN GrantTargets gt ON g.GrantID = gt.GrantID
            INNER JOIN Calls c ON gt.TargetID = c.ShelterID and gt.TargetTypeID = 1
            LEFT OUTER JOIN IntakeCalls ic on c.CallID = ic.CallID
        WHERE CallDate BETWEEN @StartDate And @EndDate 
            AND CallSubjectID BETWEEN 3 AND 99
            AND CallTypeID = 2 AND ic.CallID IS NULL
            AND c.DeletedDate IS NULL AND g.DeletedDate IS NULL
            AND @StartDate BETWEEN g.GrantStart AND g.GrantEnd AND g.GrantorID = 9
        UNION ALL
        -- Crisis calls NOT linked to an intake, reported as Hotline/crisis line counseling (932)
        SELECT ByShelter = CAST(1 AS BIT), g.GrantID, g.ContractNumber, g.Prorate, GrantorServiceID = 932, c.CallID + 2000000 AS ClientID,
            ClientType = 3, c.LastName + ', ' + c.FirstName + ' ' + c.MI AS ClientName, c.CallDate AS EventDate, c.CallID AS EventID,
            ServiceUnits = 1
        FROM Grants g INNER JOIN GrantTargets gt ON g.GrantID = gt.GrantID
            INNER JOIN Calls c ON gt.TargetID = c.ShelterID and gt.TargetTypeID = 1
            LEFT OUTER JOIN IntakeCalls ic on c.CallID = ic.CallID
        WHERE CallDate BETWEEN @StartDate And @EndDate 
            AND CallSubjectID BETWEEN 3 AND 99
            AND CallTypeID IN (1, 3) AND ic.CallID IS NULL
            AND c.DeletedDate IS NULL AND g.DeletedDate IS NULL
            AND @StartDate BETWEEN g.GrantStart AND g.GrantEnd AND g.GrantorID = 9
        UNION ALL
        -- Participant referrals to victim service programs (935)
        SELECT ByShelter = CAST(1 AS BIT), g.GrantID, g.ContractNumber, g.Prorate, GrantorServiceID = 935, p.ParticipantID AS ClientID,
            ClientType = 1, p.LastName + ', ' + p.FirstName + ' ' + p.MI AS ClientName, r.ReferralDate AS EventDate, r.ReferralID AS EventID,
            ServiceUnits = r.NumberReferrals
        FROM Participants p INNER JOIN Referrals r ON p.ParticipantID = r.ClientID AND r.ClientTypeID = 1
            INNER JOIN Intakes i ON r.IntakeID = i.IntakeID
            INNER JOIN GrantTargets gt ON i.ShelterID = gt.TargetID AND gt.TargetTypeID = 1
            INNER JOIN Grants g ON gt.GrantID = g.GrantID
            LEFT OUTER JOIN IntakeVictimType ivt ON i.IntakeID = ivt.IntakeID
        WHERE ivt.VictimTypeID IS NOT NULL AND r.ReferralTypeID IN (9, 10, 11, 13, 17, 18, 19)
            AND r.ReferralDate BETWEEN @StartDate ANd @EndDate
            AND r.DeletedDate IS NULL AND g.DeletedDate IS NULL AND i.DeletedDate IS NULL AND p.DeletedDate IS NULL
            AND @StartDate BETWEEN g.GrantStart AND g.GrantEnd AND g.GrantorID = 9
        UNION ALL
        -- Participant referrals to other services, supports, and resources (936)
        SELECT ByShelter = CAST(1 AS BIT), g.GrantID, g.ContractNumber, g.Prorate, GrantorServiceID = 936, p.ParticipantID AS ClientID,
            ClientType = 1, p.LastName + ', ' + p.FirstName + ' ' + p.MI AS ClientName, r.ReferralDate AS EventDate, r.ReferralID AS EventID,
            ServiceUnits = r.NumberReferrals
        FROM Participants p INNER JOIN Referrals r ON p.ParticipantID = r.ClientID AND r.ClientTypeID = 1
            INNER JOIN Intakes i ON r.IntakeID = i.IntakeID
            INNER JOIN GrantTargets gt ON i.ShelterID = gt.TargetID AND gt.TargetTypeID = 1
            INNER JOIN Grants g ON gt.GrantID = g.GrantID
            LEFT OUTER JOIN IntakeVictimType ivt ON i.IntakeID = ivt.IntakeID
        WHERE ivt.VictimTypeID IS NOT NULL AND r.ReferralTypeID NOT IN (8, 9, 10, 11, 13, 17, 18, 19)
            AND r.ReferralDate BETWEEN @StartDate ANd @EndDate
            AND r.DeletedDate IS NULL AND g.DeletedDate IS NULL AND i.DeletedDate IS NULL AND p.DeletedDate IS NULL
            AND @StartDate BETWEEN g.GrantStart AND g.GrantEnd AND g.GrantorID = 9
        UNION ALL
        -- Child referrals to victim service programs (935)
        SELECT ByShelter = CAST(1 AS BIT), g.GrantID, g.ContractNumber, g.Prorate, GrantorServiceID = 935, c.ChildID + 1000000 AS ClientID,
            ClientType = 1, c.LastName + ', ' + c.FirstName + ' ' + c.MI AS ClientName, r.ReferralDate AS EventDate, r.ReferralID AS EventID,
            ServiceUnits = r.NumberReferrals
        FROM Children c INNER JOIN ChildIntakes ci ON c.ChildID = ci.ChildID
            INNER JOIN Intakes i ON ci.IntakeID = i.IntakeID
            INNER JOIN Referrals r ON ci.ChildIntakeID = r.IntakeID AND r.ClientTypeID = 2
            INNER JOIN GrantTargets gt ON i.ShelterID = gt.TargetID AND TargetTypeID = 1
            INNER JOIN Grants g ON gt.GrantID = g.GrantID
            LEFT OUTER JOIN IntakeVictimType ivt ON i.IntakeID = ivt.IntakeID
        WHERE ivt.VictimTypeID IS NOT NULL AND r.ReferralTypeID IN (9, 10, 11, 13, 17, 18, 19)
            AND r.ReferralDate BETWEEN @StartDate AND @EndDate
            AND r.DeletedDate IS NULL AND g.DeletedDate IS NULL AND i.DeletedDate IS NULL AND c.DeletedDate IS NULL
            AND @StartDate BETWEEN g.GrantStart AND g.GrantEnd AND g.GrantorID = 9
        UNION ALL
        -- Child referrals to other services, supports, and resources (936)
        SELECT ByShelter = CAST(1 AS BIT), g.GrantID, g.ContractNumber, g.Prorate, GrantorServiceID = 936, c.ChildID + 1000000 AS ClientID,
            ClientType = 1, c.LastName + ', ' + c.FirstName + ' ' + c.MI AS ClientName, r.ReferralDate AS EventDate, r.ReferralID AS EventID,
            ServiceUnits = r.NumberReferrals
        FROM Children c INNER JOIN ChildIntakes ci ON c.ChildID = ci.ChildID
            INNER JOIN Intakes i ON ci.IntakeID = i.IntakeID
            INNER JOIN Referrals r ON ci.ChildIntakeID = r.IntakeID AND r.ClientTypeID = 2
            INNER JOIN GrantTargets gt ON i.ShelterID = gt.TargetID AND TargetTypeID = 1
            INNER JOIN Grants g ON gt.GrantID = g.GrantID
            LEFT OUTER JOIN IntakeVictimType ivt ON i.IntakeID = ivt.IntakeID
        WHERE ivt.VictimTypeID IS NOT NULL AND r.ReferralTypeID NOT IN (9, 10, 11, 13, 17, 18, 19)
            AND r.ReferralDate BETWEEN @StartDate AND @EndDate
            AND r.DeletedDate IS NULL AND g.DeletedDate IS NULL AND i.DeletedDate IS NULL AND c.DeletedDate IS NULL
            AND @StartDate BETWEEN g.GrantStart AND g.GrantEnd AND g.GrantorID = 9
        UNION ALL
        -- Participant victims provided emergency shelter in VOCA grant-related shelter (933)
        SELECT ByShelter = CAST(1 AS BIT), g.GrantID, g.ContractNumber, g.Prorate, GrantorServiceID = 933, p.ParticipantID AS ClientID,
            ClientType = 1, p.LastName + ', ' + p.FirstName + ' ' + p.MI AS ClientName, i.EntryDate AS EventDate, i.IntakeID AS EventID,
            ServiceUnits = CASE
                WHEN i.EntryDate = i.ExitDate THEN 1
                WHEN EntryDate >= @StartDate AND ExitDate <= @EndDate THEN DATEDIFF(day, EntryDate, ExitDate)
                WHEN EntryDate < @StartDate AND ExitDate <= @EndDate THEN DATEDIFF(day, @StartDate, ExitDate)
                WHEN EntryDate >= @StartDate AND (ExitDate IS NULL OR ExitDate > @EndDate) THEN DATEDIFF(day, EntryDate, @EndDate) + 1
                WHEN EntryDate < @StartDate AND (ExitDate IS NULL OR ExitDate > @EndDate) THEN DATEDIFF(day, @StartDate, @EndDate) + 1
                END
        FROM Participants p INNER JOIN Intakes i ON p.ParticipantID = i.ParticipantID
            INNER JOIN GrantTargets gt ON i.ShelterID = gt.TargetID AND gt.TargetTypeID = 1
            INNER JOIN Grants g ON gt.GrantID = g.GrantID
            LEFT OUTER JOIN IntakeVictimType ivt ON i.IntakeID = ivt.IntakeID
        WHERE ivt.VictimTypeID IS NOT NULL AND i.IntakeTypeID IN (1, 3, 5)
            AND i.EntryDate <= @EndDate AND (i.ExitDate IS NULL OR i.ExitDate >= @StartDate)
            AND p.DeletedDate IS NULL AND i.DeletedDate IS NULL AND g.DeletedDate IS NULL
            AND g.GrantorID = 9
            AND @StartDate BETWEEN g.GrantStart AND g.GrantEnd AND g.GrantorID = 9
        UNION ALL
        -- Participant victims provided transitional shelter in VOCA grant-related shelter (934)
        SELECT ByShelter = CAST(1 AS BIT), g.GrantID, g.ContractNumber, g.Prorate, GrantorServiceID = 934, p.ParticipantID AS ClientID,
            ClientType = 1, p.LastName + ', ' + p.FirstName + ' ' + p.MI AS ClientName, i.EntryDate AS EventDate, i.IntakeID AS EventID,
            ServiceUnits = CASE
                WHEN i.EntryDate = i.ExitDate THEN 1
                WHEN EntryDate >= @StartDate AND ExitDate <= @EndDate THEN DATEDIFF(day, EntryDate, ExitDate)
                WHEN EntryDate < @StartDate AND ExitDate <= @EndDate THEN DATEDIFF(day, @StartDate, ExitDate)
                WHEN EntryDate >= @StartDate AND (ExitDate IS NULL OR ExitDate > @EndDate) THEN DATEDIFF(day, EntryDate, @EndDate) + 1
                WHEN EntryDate < @StartDate AND (ExitDate IS NULL OR ExitDate > @EndDate) THEN DATEDIFF(day, @StartDate, @EndDate) + 1
                END
        FROM Participants p INNER JOIN Intakes i ON p.ParticipantID = i.ParticipantID
            INNER JOIN GrantTargets gt ON i.ShelterID = gt.TargetID AND gt.TargetTypeID = 1
            INNER JOIN Grants g ON gt.GrantID = g.GrantID
            LEFT OUTER JOIN IntakeVictimType ivt ON i.IntakeID = ivt.IntakeID
        WHERE ivt.VictimTypeID IS NOT NULL AND i.IntakeTypeID = 2
            AND i.EntryDate <= @EndDate AND (i.ExitDate IS NULL OR i.ExitDate >= @StartDate)
            AND p.DeletedDate IS NULL AND i.DeletedDate IS NULL AND g.DeletedDate IS NULL
            AND g.GrantorID = 9
            AND @StartDate BETWEEN g.GrantStart AND g.GrantEnd AND g.GrantorID = 9
        UNION ALL
        -- Child victims provided emergency shelter in VOCA grant-related shelter (933)
        SELECT ByShelter = CAST(1 AS BIT), g.GrantID, g.ContractNumber, g.Prorate, GrantorServiceID = 933, c.ChildID + 1000000 AS ClientID,
            ClientType = 2, c.LastName + ', ' + c.FirstName + ' ' + c.MI AS ClientName, i.EntryDate AS EventDate, ci.ChildIntakeID AS EventID,
            ServiceUnits = CASE
                WHEN ci.EntryDate = ci.ExitDate THEN 1
                WHEN ci.EntryDate >= @StartDate AND ci.ExitDate <= @EndDate THEN DATEDIFF(day, ci.EntryDate, ci.ExitDate)
                WHEN ci.EntryDate < @StartDate AND ci.ExitDate <= @EndDate THEN DATEDIFF(day, @StartDate, ci.ExitDate)
                WHEN ci.EntryDate >= @StartDate AND (ci.ExitDate IS NULL OR ci.ExitDate > @EndDate) THEN DATEDIFF(day, ci.EntryDate, @EndDate) + 1
                WHEN ci.EntryDate < @StartDate AND (ci.ExitDate IS NULL OR ci.ExitDate > @EndDate) THEN DATEDIFF(day, @StartDate, @EndDate) + 1
                END
        FROM Children c INNER JOIN ChildIntakes ci ON c.ChildID = ci.ChildID
            INNER JOIN Intakes i ON ci.IntakeID = i.IntakeID
            INNER JOIN GrantTargets gt ON i.ShelterID = gt.TargetID AND gt.TargetTypeID = 1
            INNER JOIN Grants g ON gt.GrantID = g.GrantID
            LEFT OUTER JOIN IntakeVictimType ivt ON i.IntakeID = ivt.IntakeID
        WHERE ivt.VictimTypeID IS NOT NULL AND i.IntakeTypeID IN (1, 3, 5)
            AND i.EntryDate <= @EndDate AND (i.ExitDate IS NULL OR i.ExitDate >= @StartDate)
            AND c.DeletedDate IS NULL AND i.DeletedDate IS NULL AND g.DeletedDate IS NULL
            AND g.GrantorID = 9
            AND @StartDate BETWEEN g.GrantStart AND g.GrantEnd AND g.GrantorID = 9
        UNION ALL
        -- Child victims provided transitional shelter in VOCA grant-related shelter (934)
        SELECT ByShelter = CAST(1 AS BIT), g.GrantID, g.ContractNumber, g.Prorate, GrantorServiceID = 934, c.ChildID + 1000000 AS ClientID,
            ClientType = 2, c.LastName + ', ' + c.FirstName + ' ' + c.MI AS ClientName, i.EntryDate AS EventDate, ci.ChildIntakeID AS EventID,
            ServiceUnits = CASE
                WHEN ci.EntryDate = ci.ExitDate THEN 1
                WHEN ci.EntryDate >= @StartDate AND ci.ExitDate <= @EndDate THEN DATEDIFF(day, ci.EntryDate, ci.ExitDate)
                WHEN ci.EntryDate < @StartDate AND ci.ExitDate <= @EndDate THEN DATEDIFF(day, @StartDate, ci.ExitDate)
                WHEN ci.EntryDate >= @StartDate AND (ci.ExitDate IS NULL OR ci.ExitDate > @EndDate) THEN DATEDIFF(day, ci.EntryDate, @EndDate) + 1
                WHEN ci.EntryDate < @StartDate AND (ci.ExitDate IS NULL OR ci.ExitDate > @EndDate) THEN DATEDIFF(day, @StartDate, @EndDate) + 1
                END
        FROM Children c INNER JOIN ChildIntakes ci ON c.ChildID = ci.ChildID
            INNER JOIN Intakes i ON ci.IntakeID = i.IntakeID
            INNER JOIN GrantTargets gt ON i.ShelterID = gt.TargetID AND gt.TargetTypeID = 1
            INNER JOIN Grants g ON gt.GrantID = g.GrantID
            LEFT OUTER JOIN IntakeVictimType ivt ON i.IntakeID = ivt.IntakeID
        WHERE ivt.VictimTypeID IS NOT NULL AND i.IntakeTypeID = 2
            AND i.EntryDate <= @EndDate AND (i.ExitDate IS NULL OR i.ExitDate >= @StartDate)
            AND c.DeletedDate IS NULL AND i.DeletedDate IS NULL AND g.DeletedDate IS NULL
            AND g.GrantorID = 9
            AND @StartDate BETWEEN g.GrantStart AND g.GrantEnd AND g.GrantorID = 9
        UNION ALL
        -- Participant referrals to victim services programs by grant-funded staff (935)
        SELECT ByShelter = CAST(0 AS BIT), g.GrantID, g.ContractNumber, g.Prorate, GrantorServiceID = 935, p.ParticipantID AS ClientID,
            ClientType = 1, p.LastName + ', ' + p.FirstName + ' ' + p.MI as ClientName, r.ReferralDate AS EventDate, r.ReferralID AS EventID,
            ServiceUnits = r.NumberReferrals
        FROM Participants p INNER JOIN Referrals r ON p.ParticipantID =r.ClientID AND r.ClientTypeID = 1
            INNER JOIN Intakes i ON r.IntakeID = i.IntakeID
            INNER JOIN StaffReferrals sr ON r.ReferralID = sr.ReferralID
            INNER JOIN GrantTargets gt ON gt.TargetID = sr.StaffID AND gt.TargetTypeID = 4
            INNER JOIN Grants g ON g.GrantID = gt.GrantID
            LEFT OUTER JOIN IntakeVictimType ivt ON i.IntakeID = ivt.IntakeID
        WHERE ivt.VictimTypeID IS NOT NULL AND r.ReferralTypeID IN (9, 10, 11, 13, 17, 18, 19)
            AND r.ReferralDate BETWEEN @StartDate AND @EndDate
            AND r.DeletedDate IS NULL AND g.DeletedDate IS NULL AND i.DeletedDate IS NULL AND p.DeletedDate IS NULL
            AND @StartDate BETWEEN g.GrantStart AND g.GrantEnd AND g.GrantorID = 9
        UNION ALL
        -- Participant referrals to other services/suports/resources by grant-funded staff (936)
        SELECT ByShelter = CAST(0 AS BIT), g.GrantID, g.ContractNumber, g.Prorate, GrantorServiceID = 936, p.ParticipantID AS ClientID,
            ClientType = 1, p.LastName + ', ' + p.FirstName + ' ' + p.MI as ClientName, r.ReferralDate AS EventDate, r.ReferralID AS EventID,
            ServiceUnits = r.NumberReferrals
        FROM Participants p INNER JOIN Referrals r ON p.ParticipantID =r.ClientID AND r.ClientTypeID = 1
            INNER JOIN Intakes i ON r.IntakeID = i.IntakeID
            INNER JOIN StaffReferrals sr ON r.ReferralID = sr.ReferralID
            INNER JOIN GrantTargets gt ON gt.TargetID = sr.StaffID AND gt.TargetTypeID = 4
            INNER JOIN Grants g ON g.GrantID = gt.GrantID
            LEFT OUTER JOIN IntakeVictimType ivt ON i.IntakeID = ivt.IntakeID
        WHERE ivt.VictimTypeID IS NOT NULL AND r.ReferralTypeID NOT IN (9, 10, 11, 13, 17, 18, 19)
            AND r.ReferralDate BETWEEN @StartDate AND @EndDate
            AND r.DeletedDate IS NULL AND g.DeletedDate IS NULL AND i.DeletedDate IS NULL AND p.DeletedDate IS NULL
            AND @StartDate BETWEEN g.GrantStart AND g.GrantEnd AND g.GrantorID = 9
            UNION ALL
        -- Child referrals to victim service programs by grant-funded staff (935)
        SELECT ByShelter = CAST(0 AS BIT), g.GrantID, g.ContractNumber, g.Prorate, GrantorServiceID = 935, c.ChildID + 1000000 AS ClientID,
            ClientType = 1, c.LastName + ', ' + c.FirstName + ' ' + c.MI AS ClientName, r.ReferralDate AS EventDate, r.ReferralID AS EventID,
            ServiceUnits = r.NumberReferrals
        FROM Children c INNER JOIN ChildIntakes ci ON c.ChildID = ci.ChildID
            INNER JOIN Intakes i ON ci.IntakeID = i.IntakeID
            INNER JOIN Referrals r ON ci.ChildIntakeID = r.IntakeID AND r.ClientTypeID = 2
            INNER JOIN StaffReferrals sr ON r.ReferralID = sr.ReferralID
            INNER JOIN GrantTargets gt ON sr.StaffID = gt.TargetID AND gt.TargetTypeID = 4
            INNER JOIN Grants g ON gt.GrantID = g.GrantID
            LEFT OUTER JOIN IntakeVictimType ivt ON i.IntakeID = ivt.IntakeID
        WHERE ivt.VictimTypeID IS NOT NULL AND r.ReferralTypeID IN (9, 10, 11, 13, 17, 18, 19)
            AND r.ReferralDate BETWEEN @StartDate AND @EndDate
            AND r.DeletedDate IS NULL AND g.DeletedDate IS NULL AND i.DeletedDate IS NULL AND c.DeletedDate IS NULL
            AND @StartDate BETWEEN g.GrantStart AND g.GrantEnd AND g.GrantorID = 9
        UNION ALL
        -- Child referrals to other services/supports/resource (936)
        SELECT ByShelter = CAST(0 AS BIT), g.GrantID, g.ContractNumber, g.Prorate, GrantorServiceID = 936, c.ChildID + 1000000 AS ClientID,
            ClientType = 1, c.LastName + ', ' + c.FirstName + ' ' + c.MI AS ClientName, r.ReferralDate AS EventDate, r.ReferralID AS EventID,
            ServiceUnits = r.NumberReferrals
        FROM Children c INNER JOIN ChildIntakes ci ON c.ChildID = ci.ChildID
            INNER JOIN Intakes i ON ci.IntakeID = i.IntakeID
            INNER JOIN Referrals r ON ci.ChildIntakeID = r.IntakeID AND r.ClientTypeID = 2
            INNER JOIN StaffReferrals sr ON r.ReferralID = sr.ReferralID
            INNER JOIN GrantTargets gt ON sr.StaffID = gt.TargetID AND gt.TargetTypeID = 4
            INNER JOIN Grants g ON gt.GrantID = g.GrantID
            LEFT OUTER JOIN IntakeVictimType ivt ON i.IntakeID = ivt.IntakeID
        WHERE ivt.VictimTypeID IS NOT NULL AND r.ReferralTypeID NOT IN (9, 10, 11, 13, 17, 18, 19)
            AND r.ReferralDate BETWEEN @StartDate AND @EndDate
            AND r.DeletedDate IS NULL AND g.DeletedDate IS NULL AND i.DeletedDate IS NULL AND c.DeletedDate IS NULL
            AND @StartDate BETWEEN g.GrantStart AND g.GrantEnd AND g.GrantorID = 9
        UNION ALL
        -- Community Member referrals to victim service programs by grant-funded staff (935)
        SELECT ByShelter = CAST(0 AS BIT), g.GrantID, g.ContractNumber, g.Prorate, GrantorServiceID = 935, cm.MemberID + 3000000 AS ClientID,
            ClientType = 1, cm.LastName + ', ' + cm.FirstName + ' ' + cm.MI AS ClientName, r.ReferralDate AS EventDate, r.ReferralID AS EventID,
            ServiceUnits = r.NumberReferrals
        FROM CommunityMembers cm INNER JOIN Referrals r ON cm.MemberID = r.ClientID AND r.ClientTypeID = 4 
            INNER JOIN StaffReferrals sr ON r.ReferralID = sr.ReferralID
            INNER JOIN GrantTargets gt ON sr.StaffID = gt.TargetID AND gt.TargetTypeID = 4
            INNER JOIN Grants g ON gt.GrantID = g.GrantID
        WHERE cm.VictimTypeID BETWEEN 3 AND 99 AND r.ReferralTypeID IN (9, 10, 11, 13, 17, 18, 19)
            AND r.ReferralDate BETWEEN @StartDate AND @EndDate
            AND r.DeletedDate IS NULL AND g.DeletedDate IS NULL AND cm.DeletedDate IS NULL
            AND @StartDate BETWEEN g.GrantStart AND g.GrantEnd AND g.GrantorID = 9
        UNION ALL
        -- Community Member referrals to other services/supports/resource (936)
        SELECT ByShelter = CAST(0 AS BIT), g.GrantID, g.ContractNumber, g.Prorate, GrantorServiceID = 936, cm.MemberID + 3000000 AS ClientID,
            ClientType = 1, cm.LastName + ', ' + cm.FirstName + ' ' + cm.MI AS ClientName, r.ReferralDate AS EventDate, r.ReferralID AS EventID,
            ServiceUnits = r.NumberReferrals
        FROM CommunityMembers cm INNER JOIN Referrals r ON cm.MemberID = r.ClientID AND r.ClientTypeID = 4 
            INNER JOIN StaffReferrals sr ON r.ReferralID = sr.ReferralID
            INNER JOIN GrantTargets gt ON sr.StaffID = gt.TargetID AND gt.TargetTypeID = 4
            INNER JOIN Grants g ON gt.GrantID = g.GrantID
        WHERE cm.VictimTypeID BETWEEN 3 AND 99 AND r.ReferralTypeID NOT IN (9, 10, 11, 13, 17, 18, 19)
            AND r.ReferralDate BETWEEN @StartDate AND @EndDate
            AND r.DeletedDate IS NULL AND g.DeletedDate IS NULL AND cm.DeletedDate IS NULL
            AND @StartDate BETWEEN g.GrantStart AND g.GrantEnd AND g.GrantorID = 9
        ORDER BY g.GrantID, ClientID, EventID, ByShelter; 

    -- Unmet requests for services
    -- Evaluated over the entire grant period
        SELECT g.GrantID, g.ContractNumber, x.ReasonDenied, sr.ReasonDeniedID, COUNT(x.ReasonDeniedID) AS NumberDenied
        FROM Calls c INNER JOIN ShelterRequests sr ON c.CallID = sr.CallID
            INNER JOIN xReasonDenied x ON x.ReasonDeniedID = sr.ReasonDeniedID
            INNER JOIN Shelters s ON c.ShelterID = s.ShelterID
            INNER JOIN GrantTargets gt ON s.ShelterID = gt.TargetID AND gt.TargetTypeID = 1
            INNER JOIN Grants g ON g.GrantID = gt.GrantID
        WHERE sr.ReasonDeniedID > 0
            AND g.GrantorID = 9 AND c.CallDate BETWEEN g.GrantStart AND g.GrantEnd
            AND @StartDate BETWEEN g.GrantStart AND g.GrantEnd
            AND c.DeletedDate IS NULL AND g.DeletedDate IS NULL
        GROUP BY g.GrantID, g.ContractNumber, x.ReasonDenied, sr.ReasonDeniedID
        ORDER BY g.GrantID, x.ReasonDenied; 

    -- Number of surveys completed
        SELECT g.GrantID, g.ContractNumber, COUNT(q.QuestionnaireID) AS Questionnaires
        FROM Grants g INNER JOIN GrantSurveys gs ON g.GrantID = gs.GrantID
            INNER JOIN Questionnaires q ON gs.SurveyID = q.SurveyID
        WHERE g.GrantorID = 9 AND q.CompletedDate BETWEEN g.GrantStart AND g.GrantEnd
            AND @StartDate BETWEEN g.GrantStart AND g.GrantEnd
            AND q.DeletedDate IS NULL AND g.DeletedDate IS NULL
        GROUP BY g.GrantID, ContractNumber
        ORDER BY g.GrantID; 

    -- Narratives
        SELECT Item14 =ISNULL((SELECT TOP 1 Narrative FROM ReportNarratives WHERE ReportNumber = @ReportNumber AND ReportDate = @StartDate AND Item = '14'), ''),
            Item15 = ISNULL((SELECT TOP 1 Narrative FROM ReportNarratives WHERE ReportNumber = @ReportNumber AND ReportDate = @StartDate AND Item = '15'), '') ; 

    -- Service Map, used to show users in report how their services are mapped to VOCA service types (TableName:  ServiceMap)
        SELECT ISNULL(s.ServiceName, 'UNMAPPED') AS ServiceName, ISNULL(f.Focus, '<None>') AS Focus, gs.GrantorService
        FROM GrantorServices gs LEFT OUTER JOIN ServiceMap sm ON gs.GrantorServiceID = sm.GrantorServiceID
            LEFT OUTER JOIN ServiceFocus sf ON sm.ServiceFocusID = sf.ServiceFocusID
            LEFT OUTER JOIN Services s ON sf.ServiceID = s.ServiceID
            LEFT OUTER JOIN Focus f ON sf.FocusID = f.FocusID
        WHERE sm.GrantorID = 9 AND s.DiscontinuedDate IS NULL
        UNION ALL
        SELECT s.ServiceName, f.Focus, 'UNMAPPED' AS GrantorService
        FROM ServiceFocus sf INNER JOIN Services s ON sf.ServiceID = s.ServiceID
            INNER JOIN Focus f ON sf.FocusID = f.FocusID
        WHERE sf.ServiceFocusID NOT IN
            (SELECT ServiceFocusID FROM ServiceMap WHERE GrantorID = 9)
            AND s.DiscontinuedDate IS NULL
        ORDER BY ServiceName, Focus;
GO
UPDATE zsSettings SET Revision = 50