title: SQL Find if a product has been ordered by a customer within the last 12 months
body: <p>I have been racking my brains out over the thanksgiving holiday an have had no luck in doing the following.</p>
  
  <p>I run the following SQL script to get the last twelve months of data from our sales database. While this has returned every sale as I had wanted for another report it has been asked of me to now search the results and find out if the product has been ordered before and if not to return the results to another report.</p>
  
  <p>Here is what I have got so far:</p>
  
  <pre><code>SELECT 
      cicmpy.cmp_name AS "Company Name", 
      humres.fullname AS "Salesperson", 
      oehdrhst_sql.ord_dt AS "Order Date", 
      oelinhst_sql.item_no AS "Item Number", 
      oelinhst_sql.item_desc_1 AS "Description",
      oelinhst_sql.qty_ordered AS "Qty Ordered", 
      oelinhst_sql.item_desc_2 AS "UPC CODE",   
      oehdrhst_sql.oe_po_no AS "Customer PO"
  FROM   
      (humres humres 
  INNER JOIN 
      (cicmpy cicmpy 
  INNER JOIN 
      oehdrhst_sql oehdrhst_sql ON cicmpy.cmp_code=oehdrhst_sql.cus_no) 
      ON humres.res_id=oehdrhst_sql.slspsn_no) 
  INNER JOIN 
      oelinhst_sql oelinhst_sql ON oehdrhst_sql.ord_no = oelinhst_sql.ord_no
  WHERE  
      oehdrhst_sql.ord_dt &gt; DATEADD(year, -1, GETDATE())
  ORDER BY 
      cicmpy.cmp_name, oelinhst_sql.item_no
  </code></pre>
  
  <p>The above returns my data in the following format</p>
  
  <pre><code>Company Name    Salesperson Order Date  Item Number Description Qty Ordered UPC CODE    Customer PO
  Company 1       John Doe    12/29/2014  1200052     Product 1            90 0000000001      292072
  Company 1       John Doe    4/7/2015    1200053     Product 2            90 0000000002      348615
  Company 1       John Doe    6/2/2015    1200052     Product 1            90 0000000001      382184
  Company 2       Jane Doe    12/29/2014  1200052     Product 1            90 0000000001      292072
  Company 2       Jane Doe    4/7/2015    1200053     Product 2            90 0000000002      348615
  Company 2       Jane Doe    6/2/2015    1200052     Product 1            90 0000000001      382184
  Company 3       Jane Doe    12/29/2014  1200052     Product 1            90 0000000001      292072
  Company 3       Jane Doe    4/7/2015    1200053     Product 2            90 0000000002      348615
  Company 3       John Smith  6/2/2015    1200053     Product 2            90 0000000002      382184
  Company 3       John Smith  12/29/2014  1200052     Product 1            90 0000000001      292072
  Company 3       John Smith  4/7/2015    1200053     Product 2            90 0000000002      348615
  Company 3       John Smith  6/2/2015    1200052     Product 1            90 0000000001      382184
  </code></pre>
  
  <p>I have thought of using COUNT but I can not figure how to limit it by customer name also I toyed around with using Distinct but I could not figure it out either. Any help would be appreciate as I can not continue dumping 20,000+ lines of sales to excel to filter them for this single report.</p>
  
  <p>it has been asked of me to search within the results (as in the example above) and find out if the product has been ordered before and if not to return the results to another report. effectively doing a query against these results ie. a new product is released with item number 1200053 when company 1 places an order for this item it is not in their previous purchases so the following needs returned </p>
  
  <pre><code>Company Name    Salesperson Order Date  Item Number Description Qty Ordered UPC CODE    Customer PO
  Company 1       John Doe    12/29/2015  1200053     Product 3            90 0000000001      292999
  </code></pre>