Hi All,
I have below 2 statements running inner join 2 times, the where condition is only different. is it possible to have 1 inner join statement with different where clause.
Bcoz of calling inner join 2 times the execution plan is highest cost.
 
select COUNT(*) 
			from Message m
			INNER JOIN BatchMessage bm ON bm.MessageID = m.MessageID AND bm.BatchID = b.BatchID
			WHERE MessageStateNo IN (14000, 50001, 50002)) as 'Sent',



select COUNT(*) 
			from Message m
			INNER JOIN BatchMessage bm ON bm.MessageID = m.MessageID AND bm.BatchID = b.BatchID
			WHERE MessageStateNo in (50001,50002)) as 'Bounced',
		

To add this is the complete code:

SELECT		
		dbo.fnSystemTimeToSiteTime(b.StartDate),
		b.Name as 'BroadcastName',
		cu.UserID as 'User',		
		
		(select COUNT(*) 
			from Message m
			INNER JOIN BatchMessage bm ON bm.MessageID = m.MessageID AND bm.BatchID = b.BatchID
			WHERE MessageStateNo IN (14000, 50001, 50002)) as 'Sent',
		
		(select COUNT(*) 
			from Message m
			INNER JOIN BatchMessage bm ON bm.MessageID = m.MessageID AND bm.BatchID = b.BatchID
			WHERE MessageStateNo in (50001,50002)) as 'Bounced',
		--('108' ) as 'Bounced',
								

		-- Opened
		(select COUNT(*) 
			from #events ev
				WHERE ev.BatchID=b.BatchID AND ev.EmailEventTypeID = @openedEventTypeID) as 'Opened',	
				
		ISNULL((select SUM(EventCount) 
		 from #events ev 		
			WHERE ev.BatchID=b.BatchID AND ev.EmailEventTypeID = @openedEventTypeID),0) as 'OpenedTotal',
		
		-- Clicks
		(select COUNT(*)
		 from (
			select ev.MessageID
			from #events ev
				WHERE ev.BatchID = b.BatchID and EmailEventTypeID = @clickThroughEventTypeID 
			group by ev.MessageID
		) x ) as 'Clicked',
		
		(select COUNT(*) 
			from Message m
			INNER JOIN BatchMessage bm ON bm.MessageID = m.MessageID AND bm.BatchID = b.BatchID
			WHERE MessageStateNo NOT IN (12000, 13000, 14000)) as 'Send Errors',					
			
		(SELECT COUNT(*) FROM BatchMember bm WHERE bm.BatchID = b.BatchID) as 'Total Customers'
		
	FROM batch b	
	LEFT JOIN ContactUser cu ON b.CreatedByContactUserID = cu.ContactUserID		
	WHERE StartDate BETWEEN @startInSysTime AND @endInSysTime	

Hello N_14,

     First of , you may check your execution plan to analyze which part is taking time. Then you can look at the index usages. If your business logic supports and you have relation specified, then probably you can club together few of your details. As I am not sure of your table structure and indexes, tried the below to show you some example. You may try to use the below:

create Table batch(batchid int, name varchar(100))
Insert into batch Values(1,'batch1');

create Table batchmessage(batchid int, MessageID int, messagestateno int)
Insert into batchmessage values(1,100, 50001)
Insert into batchmessage values(1,101,50002)
Insert into batchmessage values(1,102,14000)
Insert into batchmessage values(1,103,50008)

SELECT		
		b.Name as 'BroadcastName',
		(select COUNT(*) 
			from BatchMessage bm where  bm.BatchID = b.BatchID
			and MessageStateNo IN (14000, 50001, 50002)) as 'Sent',
		
		(select COUNT(*) 
			from BatchMessage bm where  bm.BatchID = b.BatchID
			and MessageStateNo in (50001,50002)) as 'Bounced',
		
		(SELECT COUNT(*) FROM BatchMessage bm WHERE bm.BatchID = b.BatchID) as 'Total Customers'
	FROM batch b	


SELECT		
		b.Name as 'BroadcastName',
		COUNT(case when MessageStateNo IN (14000, 50001, 50002) then 1 else null end)  as 'Sent',
		COUNT(case when MessageStateNo IN (50001, 50002) then 1 else null end)  as 'Bounced',
		COUNT(*)
	FROM batch b
	Left Join batchmessage bm on bm.BatchID = b.BatchID
Group by b.name

Drop table batch,batchmessage

You need to look into your business requirements first. Sometimes BRs are complex and cant be achieved in one code script. As Lateesh quoted check the execution plan, analyze which is the part of the code which is bothering you so much.

I don't know why Saied referred to my article on dynamic search conditions. It does not seem to be all applicable to your question.

Yes, you can make your query more efficient with a join as you suggest. However, it is somewhat easier to use the APPLY operator, as this permits you to retain the subquery in its current form. Here is a rewrite. You may be able to improve it further on your own.

DECLARE  @openedEventTypeID int,
         @clickThroughEventTypeID int,
         @startInSysTime  datetime2(3),
         @endInSysTime datetime2(3)
SELECT     
      dbo.fnSystemTimeToSiteTime(b.StartDate),
      b.Name as 'BroadcastName',
      cu.UserID as 'User',     
      isnull(stats.Sent, 0) AS Sent,
      isnull(stats.Bounced, 0) AS Bounced,
      -- Opened
      (select COUNT(*)
         from #events ev
            WHERE ev.BatchID=b.BatchID AND ev.EmailEventTypeID = @openedEventTypeID) as 'Opened',  

      ISNULL((select SUM(EventCount)
       from #events ev      
         WHERE ev.BatchID=b.BatchID AND ev.EmailEventTypeID = @openedEventTypeID),0) as 'OpenedTotal',

      -- Clicks
      (select COUNT(*)
       from (
         select ev.MessageID
         from #events ev
            WHERE ev.BatchID = b.BatchID and EmailEventTypeID = @clickThroughEventTypeID
         group by ev.MessageID
      ) x ) as 'Clicked',

      isnull(stats.SendErrors, 0) AS "Send errors",
      (SELECT COUNT(*) FROM BatchMember bm WHERE bm.BatchID = b.BatchID) as 'Total Customers'

   FROM batch b  
   LEFT JOIN ContactUser cu ON b.CreatedByContactUserID = cu.ContactUserID  
   OUTER APPLY (SELECT SUM(CASE WHEN MessageStateNo IN (14000, 50001, 50002)
                                THEN 1
                                ELSE 0
                           END) AS Sent,
                       SUM(CASE WHEN MessageStateNo IN (50001, 50002)
                                THEN 1
                                ELSE 0
                           END) AS Bounced,
                       SUM(CASE WHEN MessageStateNo IN (12000, 13000, 14000)
                                THEN 1
                                ELSE 0
                           END) AS SendErrors
               FROM Message m
               JOIN BatchMessage bm ON bm.MessageID = m.MessageID
                                   AND bm.BatchID = b.BatchID) AS stats
   WHERE StartDate BETWEEN @startInSysTime AND @endInSysTime