Case in Where Statement? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Case in Where Statement?

Author

Topic

dewacorp.alliances

452 Posts

Posted - 2009-05-07 : 06:26:01

Hi all

Im not sure whtehr this is possible or not? I have this query as follow:


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

I want to in the CASS statement in WHERE statement so IF let say the @ServiceID = 0 ... I don't want the "AND srv.ParentServiceID = @ServiceID" this line is running.

I can probably construc the sqlstatment and running using EXEC but if I can avoid that will be goood.

Thanks

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-05-07 : 06:39:30

try like this
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 (@ServiceID = 0 OR srv.ParentServiceID = @ServiceID)
AND cs.TenantID = @TenantID
AND ccp.TenantID = @TenantID
AND cl.TenantID = @TenantID
AND ct.TenantID = @TenantID

dewacorp.alliances

452 Posts

Posted - 2009-05-07 : 07:13:39

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

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-05-07 : 07:41:11

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)
            )
    )

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-05-07 : 08:00:38

quote:
Originally posted by dewacorp.alliances

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

Welcome

dewacorp.alliances

452 Posts

Posted - 2009-05-07 : 16:47:16

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)
            )
    )

Very interesting query. I might review this. Thanks for this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources