我可以使用 SQL 根据时间表信息绘制实际日期吗? [英] Can I use SQL to plot actual dates based on schedule information?

问题描述

如果我有一个包含暗示特定日期的计划信息的表，是否可以编写一个 SQL 语句来将该信息转换为实际行，也许使用某种 CROSS JOIN?

考虑一个包含这些列的付款计划表:

• StartDate - 计划开始的日期(第一次付款应在此日期到期)
• Term - 时间表的长度(以月为单位)
• 频率 - 两次重复之间的月数
• PaymentAmt - 付款金额 :-)

<前>SchedID 开始日期 期限频率 PaymentAmt-------------------------------------------------1 05-Jan-2003 48 12 1000.002 2008 年 12 月 20 日 42 6 25.00

是否有一条 SQL 语句可以让我从上面转到下面?

<前>跑步SchedID 预期付款到期Num 日期总计-------------------------------1 1 05-Jan-2003 1000.001 2 05-Jan-2004 2000.001 3 05-Jan-2005 3000.001 4 2006 年 1 月 5 日 4000.001 5 2007 年 1 月 5 日 5000.002 1 2008 年 12 月 20 日 25.002 2 20-Jun-2009 50.002 3 2009 年 12 月 20 日 75.002 4 20-Jun-2010 100.002 5 2010 年 12 月 20 日 125.002 6 20-Jun-2011 150.002 7 2011 年 12 月 20 日 175.00

我正在使用 MS SQL Server 2005(不希望很快升级)并且我已经可以使用表变量和 while 循环来执行此操作，但似乎某种 CROSS JOIN 将适用，但我不知道这将如何运作.

感谢您的想法.

我实际上使用的是 SQL Server 2005，尽管我最初说的是 2000.我们并不像我想象的那么落后.对不起.

解决方案

我现在无法测试代码，所以请稍加保留，但我认为看起来或多或少类似于以下内容的内容应该可以回答这个问题:

 with q(SchedId, PaymentNum, DueDate, RunningExpectedTotal) 作为(选择 SchedId，1 作为 PaymentNum，开始日期作为截止日期，PaymentAmt 作为 RunningExpectedTotal来自 PaymentScheduleTable联合所有选择 q.SchedId，1 + q.PaymentNum 作为 PaymentNum，DATEADD(month, s.Frequency, q.DueDate) as DueDate,q.RunningExpectedTotal + s.PaymentAmt 作为 RunningExpectedTotal来自 q内连接 PaymentScheduleTable s在 s.SchedId = q.SchedId其中 q.PaymentNum <= s.Term/s.Frequency)选择 *来自 q按 SchedId、PaymentNum 排序

If I have a table containing schedule information that implies particular dates, is there a SQL statement that can be written to convert that information into actual rows, using some sort of CROSS JOIN, perhaps?

Consider a payment schedule table with these columns:

• StartDate - the date the schedule begins (1st payment is due on this date)
• Term - the length in months of the schedule
• Frequency - the number of months between recurrences
• PaymentAmt - the 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 a single SQL statement to allow me to go from the above to the following?

                              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

I'm using MS SQL Server 2005 (no hope for an upgrade soon) and I can already do this using a table variable and while loop, but it seemed like some sort of CROSS JOIN would apply but I don't know how that might work.

Your thoughts are appreciated.

EDIT: I'm actually using SQL Server 2005 though I initially said 2000. We aren't quite as backwards as I thought. Sorry.

解决方案

I cannot test the code right now, so take it with a pinch of salt, but I think that something looking more or less like the following should answer the question:

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

这篇关于我可以使用 SQL 根据时间表信息绘制实际日期吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！