title: Creating custom week number in sql
body: <p>I have a table with ID and Date columns.</p>
  <p><strong>Table A:</strong></p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;">ID</th>
  <th style="text-align: center;">Date</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;">24</td>
  <td style="text-align: center;">2019-10-29</td>
  </tr>
  <tr>
  <td style="text-align: left;">24</td>
  <td style="text-align: center;">2019-10-30</td>
  </tr>
  <tr>
  <td style="text-align: left;">24</td>
  <td style="text-align: center;">2019-10-31</td>
  </tr>
  <tr>
  <td style="text-align: left;">.....</td>
  <td style="text-align: center;">....</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I need to add a column called &quot;Week_Num&quot; such that:</p>
  <ol>
  <li>If there is a new &quot;ID&quot; and the first date begins on a Tuesday (eg: 29th Oct,2019 in ID 24 is Tuesday) then the week will start with 1</li>
  <li>Week will always end on Saturday irrespective of whether 7 days are over or not with only one exception (see point 3)</li>
  <li>If there is a new &quot;ID&quot; and the first date begins before Tuesday (eg: 25th Oct,2020 in ID 25 is Sunday) then the week will start with 0 and once it reaches Tuesday the week_num will become 1.</li>
  </ol>
  <p><strong>Expected Output:</strong></p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;">ID</th>
  <th style="text-align: center;">Date</th>
  <th style="text-align: right;">Day_Of_Week</th>
  <th style="text-align: right;">Week_Num</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;">24</td>
  <td style="text-align: center;">2019-10-29</td>
  <td style="text-align: right;">Tue</td>
  <td style="text-align: right;">1</td>
  </tr>
  <tr>
  <td style="text-align: left;">24</td>
  <td style="text-align: center;">2019-10-30</td>
  <td style="text-align: right;">Wed</td>
  <td style="text-align: right;">1</td>
  </tr>
  <tr>
  <td style="text-align: left;">24</td>
  <td style="text-align: center;">2019-10-31</td>
  <td style="text-align: right;">Thur</td>
  <td style="text-align: right;">1</td>
  </tr>
  <tr>
  <td style="text-align: left;">24</td>
  <td style="text-align: center;">2019-11-01</td>
  <td style="text-align: right;">Fri</td>
  <td style="text-align: right;">1</td>
  </tr>
  <tr>
  <td style="text-align: left;">24</td>
  <td style="text-align: center;">2019-11-02</td>
  <td style="text-align: right;">Sat</td>
  <td style="text-align: right;">1</td>
  </tr>
  <tr>
  <td style="text-align: left;">24</td>
  <td style="text-align: center;">2019-11-03</td>
  <td style="text-align: right;">Sun</td>
  <td style="text-align: right;">2</td>
  </tr>
  <tr>
  <td style="text-align: left;">24</td>
  <td style="text-align: center;">2019-11-04</td>
  <td style="text-align: right;">Mon</td>
  <td style="text-align: right;">2</td>
  </tr>
  <tr>
  <td style="text-align: left;">24</td>
  <td style="text-align: center;">2019-11-05</td>
  <td style="text-align: right;">Tues</td>
  <td style="text-align: right;">2</td>
  </tr>
  <tr>
  <td style="text-align: left;">24</td>
  <td style="text-align: center;">2019-11-06</td>
  <td style="text-align: right;">Wed</td>
  <td style="text-align: right;">2</td>
  </tr>
  <tr>
  <td style="text-align: left;">24</td>
  <td style="text-align: center;">2019-11-07</td>
  <td style="text-align: right;">Thurs</td>
  <td style="text-align: right;">2</td>
  </tr>
  <tr>
  <td style="text-align: left;">24</td>
  <td style="text-align: center;">2019-11-08</td>
  <td style="text-align: right;">Fri</td>
  <td style="text-align: right;">2</td>
  </tr>
  <tr>
  <td style="text-align: left;">24</td>
  <td style="text-align: center;">2019-11-09</td>
  <td style="text-align: right;">Sat</td>
  <td style="text-align: right;">2</td>
  </tr>
  <tr>
  <td style="text-align: left;">24</td>
  <td style="text-align: center;">..........</td>
  <td style="text-align: right;">.</td>
  <td style="text-align: right;">..</td>
  </tr>
  <tr>
  <td style="text-align: left;">24</td>
  <td style="text-align: center;">2020-03-14</td>
  <td style="text-align: right;">.</td>
  <td style="text-align: right;">..</td>
  </tr>
  <tr>
  <td style="text-align: left;">25</td>
  <td style="text-align: center;">2020-10-25</td>
  <td style="text-align: right;">Sun</td>
  <td style="text-align: right;">0</td>
  </tr>
  <tr>
  <td style="text-align: left;">25</td>
  <td style="text-align: center;">2020-10-26</td>
  <td style="text-align: right;">Mon</td>
  <td style="text-align: right;">0</td>
  </tr>
  <tr>
  <td style="text-align: left;">25</td>
  <td style="text-align: center;">2020-10-27</td>
  <td style="text-align: right;">Tue</td>
  <td style="text-align: right;">1</td>
  </tr>
  <tr>
  <td style="text-align: left;">25</td>
  <td style="text-align: center;">2020-10-28</td>
  <td style="text-align: right;">Wed</td>
  <td style="text-align: right;">1</td>
  </tr>
  <tr>
  <td style="text-align: left;">25</td>
  <td style="text-align: center;">2020-10-29</td>
  <td style="text-align: right;">Thur</td>
  <td style="text-align: right;">1</td>
  </tr>
  <tr>
  <td style="text-align: left;">25</td>
  <td style="text-align: center;">2020-10-30</td>
  <td style="text-align: right;">Fri</td>
  <td style="text-align: right;">1</td>
  </tr>
  <tr>
  <td style="text-align: left;">25</td>
  <td style="text-align: center;">2020-10-31</td>
  <td style="text-align: right;">Sat</td>
  <td style="text-align: right;">1</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>What I have so far:</p>
  <pre><code>select 
  distinct ID,min(Date) over (partition by ID order by date) as firstTuesdayOfSeason
  from
  TableA
  group by ID,Date
  having datepart(weekday,Date)=3
  </code></pre>
  <p>which gets the first Tuesday for every new ID and gives the following output:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;">ID</th>
  <th style="text-align: left;">firstTuesdayOfSeason</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;">24</td>
  <td style="text-align: left;">2019-10-29</td>
  </tr>
  <tr>
  <td style="text-align: left;">25</td>
  <td style="text-align: left;">2020-10-27</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I was thinking of joining this table with the Table A (the one with ID, Date as columns) but I don't know how to implement the weird Saturday logic.</p>