Tip #240: Once bitten, twice filtered

Anything that’s been done twice is usually better. That includes double cream and double barreled. Except that report pre-filtering does not work if you need a filtering entity to be included more than once in your SQL.

Consider this totally random piece of SQL:

select 
   a.revenue / b.revenue 
from 
   FilteredAccount a 
   inner join FilteredAccount b 
   on a.parentaccountid = b.accountid
where 
   b.revenue is not null

You cannot just apply pre-filtering because alias CRMAF_FilteredAccount cannot be used twice in the same statement. Not the most efficient but reasonable workaround includes temporary tables to get filtered records first and then use them as needed.

declare @accounts table(accountid uniqueidentifier)

insert @accounts select accountid 
from FilteredAccount CRMAF_FilteredAccount

select 
   a.revenue / b.revenue 
from 
   FilteredAccount a 
   -- apply filter to accounts
   inner join @accounts a1 
      on a.accountid = a1.accountid
   inner join FilteredAccount b 
      on a.parentaccountid = b.accountid
   -- apply filter to parent accounts
   inner join @accounts a2 
      on b.accountid = a2.accountid
where 
   b.revenue is not null

Time for double distilled.

Share on FacebookTweet about this on TwitterShare on Google+

Leave a Reply

Your email address will not be published. Required fields are marked *