SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Case in Where Statement?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dewacorp.alliances
Constraint Violating Yak Guru

Australia
452 Posts

Posted - 05/07/2009 :  06:26:01  Show Profile  Visit dewacorp.alliances's Homepage  Click to see dewacorp.alliances's MSN Messenger address  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
1693 Posts

Posted - 05/07/2009 :  06:39:30  Show Profile  Reply with Quote
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
Go to Top of Page

dewacorp.alliances
Constraint Violating Yak Guru

Australia
452 Posts

Posted - 05/07/2009 :  07:13:39  Show Profile  Visit dewacorp.alliances's Homepage  Click to see dewacorp.alliances's MSN Messenger address  Reply with Quote
Ah yess ... thank you. It's must be toward to Friday :)
Go to Top of Page

Ifor
Aged Yak Warrior

583 Posts

Posted - 05/07/2009 :  07:41:11  Show Profile  Reply with Quote
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)
            )
    )
Go to Top of Page

bklr
Flowing Fount of Yak Knowledge

India
1693 Posts

Posted - 05/07/2009 :  08:00:38  Show Profile  Reply with Quote
quote:
Originally posted by dewacorp.alliances

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



Welcome
Go to Top of Page

dewacorp.alliances
Constraint Violating Yak Guru

Australia
452 Posts

Posted - 05/07/2009 :  16:47:16  Show Profile  Visit dewacorp.alliances's Homepage  Click to see dewacorp.alliances's MSN Messenger address  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000