Calculating Billing Weeks based on billing week date
<p>Given the following dates:
Start Date: 09/17/2015
End Date 04/01/2016</p>

<p>I am attempting to calculate the total number of weeks and the billing weeks per month, billing weeks to date, and forecasted billing weeks.</p>

<p>Since our billing is only done by week to avoid prorating, this is a little difficult.</p>

<p>I.e,</p>

<p>The total number of weeks would be 29 using the following: </p>

<pre><code>(select ceiling(convert(float,DATEDIFF(day,'09/17/2015','04/01/2016')+1)/7))
</code></pre>

<p>More over if I were to run this report on 8th of Jan 2016,</p>

<p>It should give me 1 week as the billing weeks billed to date, with the remaining 4 weeks as a forecast. </p>

<p>I was thinking of creating a couple of fields:
WeekStart and WeekEnd as dates fields, Month and Year field. And then to filter by dates where end date &lt; today's date. Then aggregate that by the current month.</p>

<p>Example:</p>

<p>Weekstart: 09/17/2015 
Weekend: 09/23/2015
Month: September
Year: 2015</p>

<p>Weekstart: 09/24/2015
Weekend: 09/30/2015
Month: September 
Year: 2015</p>

<p>......
......
......</p>

<p>Weekstart: 03/31/2016
Weekend: 04/06/2016
Year: 2016
Month: March </p>

<p>Does this sound logical?</p>

<p>How can I go about creating this stored procedure. Any  starting point would be very helpful.</p>

<p>Here is the quick sample Stored Proc I have created:</p>

<p>Here is the following sample I created, this should would I believe: </p>

<pre><code>DECLARE @startdate datetime = '09/17/2015'
DECLARE @enddate datetime = '04/01/2016'

WHILE (@startdate &lt;= @enddate)
BEGIN
    Print CONVERT(VARCHAR, @startdate, 120) + '-----'+ CONVERT(VARCHAR, DateAdd(DD,+6,@startdate), 120) 
    set @startdate = DateAdd(DD,+6,@startdate)+1 ; 
END
</code></pre>