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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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?
 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

700 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  
 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.28 seconds. Powered By: Snitz Forums 2000