 <p>guest.TABLE2.keyfield belongs to the updated table which does not exists in this query</p> <hr> <p>This is a query with the same logic as the update.<br> For each record of T2 you are going to get the value for update based on T1.</p> <pre><code>select guest.TABLE2.* ,(SELECT count(T1.key_field) FROM ThisDB..TABLE1 T1 WHERE T1.key_field = guest.TABLE2.keyfield AND T1.date_field between (DATEADD(DAY, -7, guest.TABLE2.other_date)) and guest.TABLE2.other_date) from guest.TABLE2 </code></pre>