SELECT DISTINCT 'EXISTING_CASE' AS CaseType,  cs.CaseID
				FROM cms_Cases cs INNER JOIN cms_CaseClientProfiles ccp ON cs.CaseID = ccp.CaseID 
				INNER JOIN cms_ClientProfiles cp ON ccp.ClientProfileID = cp.ClientProfileID 
				INNER JOIN cms_Clients cl ON cp.ClientID = cl.ClientID 
				INNER JOIN cms_ClientTypes ct ON ccp.ClientTypeID = ct.ClientTypeID 
				INNER JOIN cms_CaseServices css ON cs.CaseID = css.CaseID
				INNER JOIN cms_Services srv ON css.ServiceID = srv.ServiceID
				WHERE ct.ClientTypeCode = 'CLT-0-PRIMARY'
						AND cs.AssignmentUTCDate < @UTCStartPeriod AND (cs.ClosedCaseUTCDate >= @UTCStartPeriod OR cs.ClosedCaseUTCDate IS NULL)
						AND srv.ServiceCode = 'SRV-1-FND-RGF'
						AND srv.ParentServiceID = @ServiceID
						AND cs.TenantID = @TenantID 
						AND ccp.TenantID = @TenantID 
						AND cl.TenantID = @TenantID
						AND ct.TenantID = @TenantID

SELECT 'EXISTING_CASE' AS CaseType,  cs.CaseID
FROM cms_Cases cs
WHERE cs.AssignmentUTCDate < @UTCStartPeriod
    AND (cs.ClosedCaseUTCDate >= @UTCStartPeriod OR cs.ClosedCaseUTCDate IS NULL)
    AND cs.TenantID = @TenantID
    AND EXISTS
    (
        SELECT *
        FROM cms_CaseClientProfiles ccp
        WHERE cs.CaseID = ccp.CaseID
            AND ccp.TenantID = @TenantID
            AND EXISTS
            (
                SELECT *
                FROM cms_ClientProfiles cp
                WHERE ccp.ClientProfileID = cp.ClientProfileID
                    AND EXISTS
                    (
                        SELECT *
                        FROM cms_Clients cl
                        WHERE cp.ClientID = cl.ClientID
                            AND cl.TenantID = @TenantID
                    )
            )
            AND EXISTS
            (
                SELECT *
                FROM cms_ClientTypes ct
                WHERE ccp.ClientTypeID = ct.ClientTypeID
                    AND ct.ClientTypeCode = 'CLT-0-PRIMARY'
                    AND ct.TenantID = @TenantID
            )
    )
    AND EXISTS
    (
        SELECT *
        FROM cms_CaseServices css
        WHERE cs.CaseID = css.CaseID
            AND EXISTS
            (
                SELECT *
                FROM cms_Services srv
                WHERE css.ServiceID = srv.ServiceID
                    AND srv.ServiceCode = 'SRV-1-FND-RGF'
                    AND (srv.ParentServiceID = @ServiceID OR @ServiceID = 0)
            )
    )

quote:

---

Originally posted by dewacorp.alliances

Ah yess ... thank you. It's must be toward to Friday :)

---

quote:

---

Originally posted by Ifor

Getting rid of the JOINs and DISTINCT should make the query quicker:

SELECT 'EXISTING_CASE' AS CaseType,  cs.CaseID
FROM cms_Cases cs
WHERE cs.AssignmentUTCDate < @UTCStartPeriod
    AND (cs.ClosedCaseUTCDate >= @UTCStartPeriod OR cs.ClosedCaseUTCDate IS NULL)
    AND cs.TenantID = @TenantID
    AND EXISTS
    (
        SELECT *
        FROM cms_CaseClientProfiles ccp
        WHERE cs.CaseID = ccp.CaseID
            AND ccp.TenantID = @TenantID
            AND EXISTS
            (
                SELECT *
                FROM cms_ClientProfiles cp
                WHERE ccp.ClientProfileID = cp.ClientProfileID
                    AND EXISTS
                    (
                        SELECT *
                        FROM cms_Clients cl
                        WHERE cp.ClientID = cl.ClientID
                            AND cl.TenantID = @TenantID
                    )
            )
            AND EXISTS
            (
                SELECT *
                FROM cms_ClientTypes ct
                WHERE ccp.ClientTypeID = ct.ClientTypeID
                    AND ct.ClientTypeCode = 'CLT-0-PRIMARY'
                    AND ct.TenantID = @TenantID
            )
    )
    AND EXISTS
    (
        SELECT *
        FROM cms_CaseServices css
        WHERE cs.CaseID = css.CaseID
            AND EXISTS
            (
                SELECT *
                FROM cms_Services srv
                WHERE css.ServiceID = srv.ServiceID
                    AND srv.ServiceCode = 'SRV-1-FND-RGF'
                    AND (srv.ParentServiceID = @ServiceID OR @ServiceID = 0)
            )
    )

---