SQL iterate for each group
<p>i have this table:</p>

<pre><code>|ID| GruopID | Status | Date        |
| 2| 1       | S1     | 7/29/2011   |
| 3| 1       | S2     | 7/30/2011   |
| 9| 1       | S1     | 8/02/2011   |
| 7| 1       | S1     | 8/03/2011   |
| 8| 1       | S1     | 8/04/2011   |
| 1| 2       | S1     | 7/28/2011   |
| 4| 2       | S2     | 7/30/2011   |
| 5| 2       | S3     | 8/01/2011   |
| 6| 3       | S1     | 8/02/2011   |
</code></pre>

<p>and also a specific date from an outside source: 7/31/2011</p>

<p>I need a query which will give me <strong>for each groupID</strong> the <strong>closest upper and lower</strong> date, so the result will be:</p>

<pre><code>|ID| GruopID | Status | Date        |
| 3| 1       | S2     | 7/30/2011   |
| 9| 1       | S1     | 8/02/2011   |
| 4| 2       | S2     | 7/30/2011   |
| 5| 2       | S3     | 8/01/2011   |
| 6| 3       | S1     | 8/02/2011   |
</code></pre>

<p>Can someone please help me and show me the query?</p>