locked
Conditional WHERE statements RRS feed

  • Question

  • Hi all, having a headache again learning SQL :)

    I have the following SQL statement 

    	SELECT t1.[agent-office], t1.agent, t2.[personally-introduced], t3.[company-introduced], t4.[managements], SUM([comm-intro-amount]) as [total-introduced]
    	FROM register AS t1 LEFT OUTER JOIN
       
       /* Personally Introduced */
       (SELECT agent, SUM([comm-intro-amount]) as [personally-introduced] 
    	FROM register 			
    	WHERE [sale-date] BETWEEN @param_StartDate AND @param_EndDate AND [agent-office] LIKE @param_Office
    	AND (([source-owner] = 'PL' AND role = 'LIST') 
    	OR   ([source-owner] = 'PL' AND role = 'MANAGE') 
    	OR   ([source-owner] = 'PL' AND role = 'SELL')			/* only if the agent listed the property else it sales of other agents property */)
    	GROUP BY agent) AS t2 ON t1.agent = t2.agent LEFT OUTER JOIN
    	
    	/* Company Introduced */
       (SELECT agent, SUM([comm-intro-amount]) as [company-introduced] 
    	FROM register 			
    	WHERE [sale-date] BETWEEN @param_StartDate AND @param_EndDate AND [agent-office] LIKE @param_Office AND 
    	(([source-owner] = 'CL' AND role = 'SELL'))
    	GROUP BY agent) AS t3 ON t1.agent = t3.agent LEFT OUTER JOIN
    	
    	/* Managements */
       (SELECT agent, SUM([comm-intro-amount]) as [managements] 
    	FROM register 			
    	WHERE [sale-date] BETWEEN @param_StartDate AND @param_EndDate AND [agent-office] LIKE @param_Office AND 
    	(([source-owner] = 'CL' AND role = 'MANAGE') OR ([source-owner] = 'CL' AND role = 'SELL'))
    	GROUP BY agent) AS t4 ON t1.agent = t4.agent 	
    
    	WHERE [sale-date] BETWEEN @param_StartDate AND @param_EndDate AND [agent-office] LIKE @param_Office
    	GROUP BY t1.[agent-office], t1.agent, t2.[personally-introduced] , t3.[company-introduced], t4.managements
    

    the part i can't figure out is how to add a conditional statement to the where clause under /* Personally Introduced */

    How do i make this part conditional 

    OR   ([source-owner] = 'PL' AND role = 'SELL')	
    

    only if there is a record in the table where ([source-owner] = 'PL' and role = 'LIST') for the same agent else it shouldn't SUM it.

    There is a row in the register table called [svoucherid-fk] that would be common to both records if it did exist.

     

    Sunday, December 11, 2011 7:31 PM

Answers

  • If I've read the problem correctly, the Agent should also be included to the EXISTS (...) subquery:

    ...
    AND EXISTS * only if the agent listed the property else it sales of other agents property *
    (
    SELECT 1
    FROM register RInner
    WHERE ROuter.agent = RInner.agent
    AND ROuter.svoucherid-fk = RInner.svoucherid-fk
    AND RInner.[source-owner] = 'PL'
    AND RInner.role = 'LIST'
    ) ...

    Also, it's not clear from the description of the problem if the three condition "@param_StartDate AND @param_EndDate AND [agent-office] LIKE @param_Office" should be added or not to the EXISTS(...) subquery.  Probably not but we never know.

    However, I see a more fundamental problem here: if we exclude the commission for a sale when the property has been introduced by another agent, then this selling commission should be added to the other agent; otherwise there would be something missing.  But at this stage, there is nothing to transfert the commission for the sale of one agent to another agent.

    Sunday, December 11, 2011 9:30 PM
  • See if the below one is what you expect. Query for "Personally Introduced" part is only copied below.

     

    (SELECT agent, SUM([comm-intro-amount]) as [personally-introduced] 
    	FROM register ROuter 			
    	WHERE [sale-date] BETWEEN @param_StartDate AND @param_EndDate AND [agent-office] LIKE @param_Office
    	AND (
    			(
    				[source-owner] = 'PL' AND role = 'LIST'
    			AND	EXISTS	/* only if the agent listed the property else it sales of other agents property */
    				(
    					SELECT	1
    					FROM	register RInner
    					WHERE	ROuter.svoucherid-fk = RInner.svoucherid-fk
    					AND		RInner.[source-owner] = 'PL' 
    					AND		RInner.role = 'SELL'	
    				)
    			) 
    		OR  ([source-owner] = 'PL' AND role = 'MANAGE') 
    		)
    	GROUP BY agent)
    

    Thanks!


    Or try the below.

    (SELECT agent, SUM([comm-intro-amount]) as [personally-introduced] 
    	FROM register ROuter 			
    	WHERE [sale-date] BETWEEN @param_StartDate AND @param_EndDate AND [agent-office] LIKE @param_Office
    	AND (
    			(
    				(	[source-owner] = 'PL' 
    				AND role = 'LIST'
    				)
    			OR  (
    					(
    						[source-owner] = 'PL' 
    					AND role = 'SELL' 
    					AND	EXISTS	/* only if the agent listed the property else it sales of other agents property */
    					(
    						SELECT	1
    						FROM	register RInner
    						WHERE	ROuter.svoucherid-fk = RInner.svoucherid-fk
    						AND		RInner.[source-owner] = 'PL' 
    						AND		RInner.role = 'LIST'	
    					)
    				) 
    			OR  (
    					[source-owner] = 'PL' 
    				AND role = 'MANAGE'
    				) 
    		)
    	GROUP BY agent)
    

    Thanks!

    Sunday, December 11, 2011 8:19 PM

All replies

  • See if the below one is what you expect. Query for "Personally Introduced" part is only copied below.

     

    (SELECT agent, SUM([comm-intro-amount]) as [personally-introduced] 
    	FROM register ROuter 			
    	WHERE [sale-date] BETWEEN @param_StartDate AND @param_EndDate AND [agent-office] LIKE @param_Office
    	AND (
    			(
    				[source-owner] = 'PL' AND role = 'LIST'
    			AND	EXISTS	/* only if the agent listed the property else it sales of other agents property */
    				(
    					SELECT	1
    					FROM	register RInner
    					WHERE	ROuter.svoucherid-fk = RInner.svoucherid-fk
    					AND		RInner.[source-owner] = 'PL' 
    					AND		RInner.role = 'SELL'	
    				)
    			) 
    		OR  ([source-owner] = 'PL' AND role = 'MANAGE') 
    		)
    	GROUP BY agent)
    

    Thanks!
    Sunday, December 11, 2011 7:48 PM
  • Try

     (SELECT agent, SUM([comm-intro-amount]) as [personally-introduced] 
    	FROM register R			
    	WHERE [sale-date] BETWEEN @param_StartDate AND @param_EndDate AND [agent-office] LIKE @param_Office
    	AND (([source-owner] = 'PL' AND role = 'LIST') 
    	OR   ([source-owner] = 'PL' AND role = 'MANAGE') 
    	OR   ([source-owner] = 'PL' AND role = 'SELL')	
    
    WHERE EXISTS (select 1 from Register R1
    WHERE R1.agent = R.agent
    AND R.[source-owner] = 'PL' AND R1.role = 'SELL')
    
    		/* only if the agent listed the property else it sales of other agents property */)
    	GROUP BY agent) 
    
    With where exists we check that this role and source_owner exists for the same agent.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Sunday, December 11, 2011 8:07 PM
  • See if the below one is what you expect. Query for "Personally Introduced" part is only copied below.

     

    (SELECT agent, SUM([comm-intro-amount]) as [personally-introduced] 
    	FROM register ROuter 			
    	WHERE [sale-date] BETWEEN @param_StartDate AND @param_EndDate AND [agent-office] LIKE @param_Office
    	AND (
    			(
    				[source-owner] = 'PL' AND role = 'LIST'
    			AND	EXISTS	/* only if the agent listed the property else it sales of other agents property */
    				(
    					SELECT	1
    					FROM	register RInner
    					WHERE	ROuter.svoucherid-fk = RInner.svoucherid-fk
    					AND		RInner.[source-owner] = 'PL' 
    					AND		RInner.role = 'SELL'	
    				)
    			) 
    		OR  ([source-owner] = 'PL' AND role = 'MANAGE') 
    		)
    	GROUP BY agent)
    

    Thanks!


    Or try the below.

    (SELECT agent, SUM([comm-intro-amount]) as [personally-introduced] 
    	FROM register ROuter 			
    	WHERE [sale-date] BETWEEN @param_StartDate AND @param_EndDate AND [agent-office] LIKE @param_Office
    	AND (
    			(
    				(	[source-owner] = 'PL' 
    				AND role = 'LIST'
    				)
    			OR  (
    					(
    						[source-owner] = 'PL' 
    					AND role = 'SELL' 
    					AND	EXISTS	/* only if the agent listed the property else it sales of other agents property */
    					(
    						SELECT	1
    						FROM	register RInner
    						WHERE	ROuter.svoucherid-fk = RInner.svoucherid-fk
    						AND		RInner.[source-owner] = 'PL' 
    						AND		RInner.role = 'LIST'	
    					)
    				) 
    			OR  (
    					[source-owner] = 'PL' 
    				AND role = 'MANAGE'
    				) 
    		)
    	GROUP BY agent)
    

    Thanks!

    Sunday, December 11, 2011 8:19 PM
  • If I've read the problem correctly, the Agent should also be included to the EXISTS (...) subquery:

    ...
    AND EXISTS * only if the agent listed the property else it sales of other agents property *
    (
    SELECT 1
    FROM register RInner
    WHERE ROuter.agent = RInner.agent
    AND ROuter.svoucherid-fk = RInner.svoucherid-fk
    AND RInner.[source-owner] = 'PL'
    AND RInner.role = 'LIST'
    ) ...

    Also, it's not clear from the description of the problem if the three condition "@param_StartDate AND @param_EndDate AND [agent-office] LIKE @param_Office" should be added or not to the EXISTS(...) subquery.  Probably not but we never know.

    However, I see a more fundamental problem here: if we exclude the commission for a sale when the property has been introduced by another agent, then this selling commission should be added to the other agent; otherwise there would be something missing.  But at this stage, there is nothing to transfert the commission for the sale of one agent to another agent.

    Sunday, December 11, 2011 9:30 PM
  • Hi Sylvain, your absolutely correct. There is no mechanism currently to transfer the sale of other agents properties at this stage, but this particular query just shows commission earned by the agent. It still picks up PL and List for the other agent because there is also a record for the other agent for the same property.
    Whilst I'm still learning, I was going to do a quick hack and create a 4th column and subtract the first 3 columns from the total commission value column which would give a value for sales of other agents property for that agent. If all goes well all the columns should equal the total column.
    Monday, December 12, 2011 3:20 AM