title: SQL for total sales for each customer for each year
body: <p>Using Advantage Database Server 11
  I'm trying to find each customers total purchase from two types for comparison year by year, but I get an error:</p>
  
  <p>[iAnywhere Solutions][Advantage SQL Engine]Expected 
  lexical element not found: THEN</p>
  
  <p>Here are the table.</p>
  
  <pre><code>customernr | Date_in | Status | InvType | Qty | Total
  1111          9/1/2018            D         5    25.00
  1111         12/1/2018   V        D         3    15.00
  1111         5/12/2019            L         1     2.00
  1111         7/11/2019            D         5    35.00
  1112         6/21/2018            L         7    18.00
  1112         9/14/2019            L         3     7.00
  
  
  Select 
  customernr,
  SUM(CASE WHEN date_in &gt;= '2018-01-01' and date_in &lt;= '2018-12-31' total 
  and InvType='D' ELSE 0 END) LastYD,
  SUM(CASE WHEN date_in &gt;= '2018-01-01' and date_in &lt;= '2018-12-31' total 
  and InvType='L' ELSE 0 END) LastYL,
  SUM(CASE WHEN date_in &gt;= '2019-01-01' and date_in &lt;= '2019-12-31' total 
  and InvType='D' ELSE 0 END) ThisYD,
  SUM(CASE WHEN date_in &gt;= '2019-01-01' and date_in &lt;= '2019-12-31' total 
  and InvType='L' ELSE 0 END) ThisYL
  
  from invoice
  where (Status &lt;&gt; 'V' or Status IS NULL)
  
  Group by Customernr
  </code></pre>
  
  <p>Thanks for any help,</p>
  
  <p>KHJ</p>