title: Defining join criteria between targets and transactions as a set of values
body: <p>I have a problem I am currently solving with a very messy solution, I would like to know if there is an easier way to maintain this.</p>
  
  <p>The solution I have requires a new column to be added to two views and several reports every time what people are targeted on (often).</p>
  
  <p>I have a view returning sale transactions with several attributes (heavily cropped as view has 100+ columns):</p>
  
  <pre><code>SalesPerson   SaleType   StockType   Date         Value
  ------------------------------------------------------------
  James         Type A     Type B      2013-01-01   100
  James         Type B     Type C      2013-01-02   200
  James         Type C     Type C      2013-01-03   100
  James         Type A     Type A      2013-01-04   300
  James         Type B     Type B      2013-01-05   100
  James         Type C     Type B      2013-01-06  -100
  Harry         Type A     Type B      2013-01-01   200
  Harry         Type B     Type C      2013-01-02   200
  Harry         Type C     Type C      2013-01-03    50
  Harry         Type A     Type A      2013-01-04    50
  Harry         Type B     Type B      2013-01-05   100
  Harry         Type C     Type B      2013-01-06  -200
  </code></pre>
  
  <p>I then have a table with targets like:</p>
  
  <pre><code>SalesPerson   Year    Month   TargetType  Value
  ---------------------------------------------------
  James         2013    1       1           500
  James         2013    1       2           400
  Harry         2013    1       2           300
  Harry         2013    1       3           150
  </code></pre>
  
  <p>To connect the two I translate the sales table with a view that includes if the sale line is included in each type:</p>
  
  <pre><code>CREATE VIEW SalesWithTargets
  
  AS
  
  SELECT SalesPerson
       , Date
       , Value
       , CASE WHEN SaleType  = 'Type A'
              AND  Stocktype = 'Type B'
         THEN 1 ELSE 0 END AS [Type1]
  
       , CASE WHEN SaleType  = 'Type B'
              AND  Stocktype IN ('Type B', 'Type C')
         THEN 2 ELSE 0 END AS [Type2]
  
       , CASE WHEN SaleType  IN ('Type A', 'Type C')
              AND  Stocktype NOT IN ('Type B', 'Type C')
         THEN 3 ELSE 0 END AS [Type3]
  
  FROM SalesTransactions
  </code></pre>
  
  <p>Then to check against the targets I join the Target table to the new <code>SalesWithTargets</code> view as below:</p>
  
  <pre><code>SELECT SalesPerson
       , TargetType
       , Year
       , Month
       , Value AS Target 
       , SUM(Value) AS Actual
  
  FROM Targets
  LEFT JOIN SalesWithTargets ON Targets.TargetType = SalesWithTargets.[Type1]
                             OR Targets.TargetType = SalesWithTargets.[Type2]
                             OR Targets.TargetType = SalesWithTargets.[Type3]
  </code></pre>
  
  <p>I now have 23 separate types so have 23 case statements running for every row in the transactions table, performance is getting to be a problem.</p>
  
  <p>I have tried to create a stored procedure to run this using <code>COALESCE</code> or <code>ISNULL</code> but I can't find a way to allow a list of items.</p>
  
  <p>Is there a way this can be done to allow me to define target types outside a case statement? eg:</p>
  
  <pre><code>TType   SaleTypeCriteria  SaleTypeVal       StockTypeCriteria StockTypeVal
  ----------------------------------------------------------------------------
  1       Equal             Type A            Equal             Type B
  2       Equal             Type B            In                Type B, Type C
  3       In                Type A, Type C    Not In            Type A, Type C
  </code></pre>