Can SQL be used to build actual dates based on schedule information?

Question

If I have a table containing schedule information that implies specific dates, is there an SQL statement that can be written to convert this information into real rows using some kind of CROSS JOIN, maybe?

Consider a payment schedule table with these columns:

• StartDate - start date of the schedule (the first payment should be sent on this date)
• Duration - months length of the schedule
• Frequency - the number of months between repetitions
• PaymentAmt - payment amount: -)

SchedID StartDate Term Frequency PaymentAmt
-------------------------------------------------
1 05-Jan-2003 48 12 1000.00 
2 20-Dec-2008 42 6 25.00

Is there one SQL statement that allows me to move from the above to the next?

                              Running
SchedID Payment Due Expected
        Num date total
--------------------------------------
1 1 05-Jan-2003 1000.00
1 2 05-Jan-2004 2000.00
1 3 05-Jan-2005 3000.00
1 4 05-Jan-2006 4000.00
1 5 05-Jan-2007 5000.00
2 1 20-Dec-2008 25.00
2 2 20-Jun-2009 50.00
2 3 20-Dec-2009 75.00
2 4 20-Jun-2010 100.00
2 5 20-Dec-2010 125.00
2 6 20-Jun-2011 150.00
2 7 20-Dec-2011 175.00

MS SQL Server 2005 ( ), , while, , - CROSS JOIN , .

.

EDIT: SQL Server 2005, 2000. , . .

Answer 1

, , , - :

with q(SchedId, PaymentNum, DueDate, RunningExpectedTotal) as
    (select SchedId,
            1 as PaymentNum,
            StartDate as DueDate,
            PaymentAmt as RunningExpectedTotal
     from PaymentScheduleTable
     union all
     select q.SchedId,
             1 + q.PaymentNum as PaymentNum,
             DATEADD(month, s.Frequency, q.DueDate) as DueDate,
             q.RunningExpectedTotal + s.PaymentAmt as RunningExpectedTotal
     from q
          inner join PaymentScheduleTable s
                  on s.SchedId = q.SchedId
     where q.PaymentNum <= s.Term / s.Frequency)
select *
from q
order by SchedId, PaymentNum

Answer 2

. , , , , .

, :

/* assumes @startdate and @enddate schedule limits */

SELECT
   p.paymentid,
   ps.paymentnum,
   ps.duedate,
   ps.ret
FROM
   payment p,
   dbo.FUNC_get_payment_schedule(p.paymentid, @startdate, @enddate) ps
ORDER BY p.paymentid, ps.paymentnum

Answer 3

( : http://www.sql-server-helper.com/functions/integer-table.aspx) , e..g. start + int * freq

Answer 4

. , , :

CREATE TABLE Calendar
(
     calendar_date DATETIME NOT NULL,
     is_holiday BIT NOT NULL DEFAULT(0),
     CONSTRAINT PK_Calendar PRIMARY KEY CLUSTERED calendar_date
)

In addition to is_holiday, you can add other columns that are relevant to you. You can write a script to populate the table until the next 10 or 100 or 1000 years, and you must be tuned. It makes queries like the ones you are trying to make a lot easier and can provide you with additional features.