title: Excluding results over a period
body: <p>I have an issue comparing data over time.
  I would like to exclude data that was existing in the last period but i am missing something.
  I want to retrieve only new ref which not existing during the previous period. (this query is part of bigger query thats why you will fond won / lost in my test query)</p>
  <p>An other thing i don't understand is why i got this error with production data ?</p>
  <blockquote>
  <p>SQL compilation error: Unsupported subquery type cannot be evaluated</p>
  </blockquote>
  <pre><code>     CREATE OR REPLACE TEMPORARY TABLE &quot;TMP_TEST&quot; (
      &quot;Period&quot;   TIMESTAMP,
      &quot;Country&quot;  VARCHAR,
      &quot;Ref&quot;      VARCHAR,
      &quot;Name&quot;     VARCHAR,
      &quot;Tag&quot;      VARCHAR
  );
  
  INSERT INTO &quot;TMP_TEST&quot; 
  VALUES 
  ('01/01/2020','US','WZ32  ','WKDM2     ','123'),
  ('01/01/2020','US','PZ56  ','2GFSDG37  ','456'),
  ('01/02/2020','US','OD59  ','ORD56     ','123'),
  ('01/03/2020','US','OD59  ','ORD56     ','123'),
  ('01/03/2020','US','OD59  ','ORD56     ','456'),
  ('01/03/2020','US','NULL  ','24GFDSGF2 ','123'),
  ('01/03/2020','US','RL04  ','24GSFD1   ','123'),
  ('01/04/2020','US','RL04  ','24GSFD1   ','123');
  
  SELECT * from &quot;TMP_TEST&quot;
  
  SELECT A.&quot;Ref&quot;,A.&quot;Period&quot;,A.&quot;Name&quot;,A.&quot;Country&quot;,A.&quot;Tag&quot;, 1 AS &quot;Won&quot;,0 AS &quot;Lost&quot;
  FROM &quot;TMP_TEST&quot; A
  WHERE A.&quot;Ref&quot; NOT IN (SELECT B.&quot;Ref&quot; FROM &quot;TMP_TEST&quot; B WHERE B.&quot;Period&quot; = DATEADD(MONTH, -1,A.&quot;Period&quot;))
  GROUP BY 1,2,3,4,5
  </code></pre>
  <p>Wanted result :</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Period</th>
  <th>Country</th>
  <th>Ref</th>
  <th>Name</th>
  <th>Tag</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>01/01/2020</td>
  <td>US</td>
  <td>WZ32</td>
  <td>WKDM2</td>
  <td>123</td>
  </tr>
  <tr>
  <td>01/01/2020</td>
  <td>US</td>
  <td>PZ56</td>
  <td>2GFSDG37</td>
  <td>456</td>
  </tr>
  <tr>
  <td>01/02/2020</td>
  <td>US</td>
  <td>OD59</td>
  <td>ORD56</td>
  <td>123</td>
  </tr>
  <tr>
  <td>01/03/2020</td>
  <td>US</td>
  <td>OD59</td>
  <td>ORD56</td>
  <td>456</td>
  </tr>
  <tr>
  <td>01/03/2020</td>
  <td>US</td>
  <td>NULL</td>
  <td>24GFDSGF2</td>
  <td>123</td>
  </tr>
  <tr>
  <td>01/03/2020</td>
  <td>US</td>
  <td>RL04</td>
  <td>24GSFD1</td>
  <td>123</td>
  </tr>
  </tbody>
  </table>
  </div>