StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POCreate a list of date ranges from a list of dates
text
Body
copied!<p>We currently store a schedule of payments as follows:</p> <pre><code>Item No | Due Date | Amount Due 108 | 2013-02-01 | 60.00 108 | 2013-02-26 | 60.00 108 | 2013-03-01 | 60.00 108 | 2013-03-15 | 60.00 </code></pre> <p><em>note: the difference between the dates is inconsistent, i.e. some items may be weeks, fortnights or months.</em></p> <p>What I ideally need to work out is how to re-query the above table into the following format:</p> <pre><code>Item No | Due Date | Date From | Date To | Amount Due 108 | 2013-02-01 | 2013-01-14 | 2013-02-25 | 60.00 108 | 2013-02-26 | 2013-02-26 | 2013-02-28 | 60.00 108 | 2013-03-01 | 2013-03-01 | 2013-03-14 | 60.00 108 | 2013-03-15 | 2013-03-15 | 2013-03-25 | 60.00 </code></pre> <p>The extra two dates to be fed in to allow this to be possible will be Start Date (2013-01-14) and today's date (2013-03-25).</p> <p>Each range should go from the Original Due Date to the day before the next Due Date.</p> <p>Any advise would be appreciated.</p> <hr> <p>UPDATE</p> <p>Here is what I have tried so far:</p> <pre><code>WITH CTE_Repayments(AgreementID, DueDate, AmountDue) AS ( -- Anchor Member Definition SELECT AgreementID, StartDate, CONVERT(DECIMAL(9,2),0.00) FROM Loans AS L WHERE L.AgreementID = 111 UNION ALL -- Recursive Member Definition SELECT RB.AgreementID, RB.DueDate, CONVERT(Decimal(9,2),RB.AmountDue) FROM ( SELECT * FROM RepaymentBreakdown WHERE AgreementID = 111 ) AS RB INNER JOIN CTE_Repayments AS R ON RB.AgreementID = R.AgreementID ) -- Statement that Executes CTE SELECT AgreementID, DueDate, AmountDue FROM CTE_Repayments </code></pre> <p>But this does not work.</p> <p>I am assuming I need to add a record with the start Date as an Anchor member.</p> <p>OK it now returns Data with the amended code above.</p> <p>Problem I have now is that the result set is not restricted to the AgreementID From the Anchor and I get an error:</p> <blockquote> <p>Msg 530, Level 16, State 1, Line 1 The statement terminated. The maximum recursion 100 has been exhausted before statement completion.</p> </blockquote>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload