title: Is there a way to dense_rank() over every row within a date range?
body: <p>I'm trying to get a count of unique items in a column given an ID number and where the date is within the last 12 months. I need to iterate this over each row in my table.</p>
  
  <p>I am using a combination of dense_rank() and (Over partition by to calculate the count of unique items, but I haven't been able to add in the date filter successfully. The results I see so far are showing count of distinct Unique_Code for each row with the same ID regardless of the date.</p>
  
  <pre><code>select ID, 
         Unique_Code, 
         Transaction_Date,  
         DATEADD(Month, -12, Transaction_Date) as L12M,
         dense_rank() over (partition by ID order by Transaction_Date, Unique_Code) as [Unique_Count]
              from (select *, (case when datediff(day, lag(Transaction_Date,1,Transaction_Date) over (partition by Unique_Code order by ID), Transaction_Date)
               &lt;= 1 
                        then 1 else 2 
                   end) as grp
        from datatable1)
  </code></pre>
  
  <p>I expect the results to show a count of unique items from the unique_code column for the id in the row and where previous entries within the same ID are with the transaction date and the transaction date - 12 months. Right now I am seeing a count of unique items from the unique_code column from each entry with the same ID regardless of the date range.</p>
  
  <p><img src="https://i.stack.imgur.com/badIn.png" alt="Table of expected results"></p>