如果我在begin_date中添加360个月，如何使用查找维度表准确获取end_date？

Question

EDIT:在任何表中，begin_date和end_date都是DATE类型的列。

我有下面的维度表，它提供了1980到2500年每个月的总天数：

CREATE TABLE total_days
    (
from_date     DATE,
to_date       DATE,
days_in_month SMALLINT
    );

from_date   to_date     days_in_month
1980-01-01  1980-01-31  31
1980-02-01  1980-02-29  29
...
2500-11-01  2500-11-30  30
2500-12-01  2500-12-31  31

如果我要将begin_date增加360个月，我应该如何构造SQL查询来获得准确的end_date？我需要以任何方式改变维度表来实现我的目标吗？

编辑:日期运算必须在不使用任何本机SQL日期运算函数的情况下执行。这必须通过在维度表中查找begin_date来完成。

Answer 1

我猜你有你的理由-这里是一个非常简单的技巧-假设事实表每个月都有一行-添加一个表示月份的新列，从1开始，并按时间顺序自动递增，从每年开始。

SELECT B.* 
FROM SO_total_days2 A
INNER JOIN SO_total_days2 B ON B.monthnumber = A.monthnumber + 360
WHERE A.from_date =  '2010-01-01'

from_date   to_date     days_in_month monthnumber
1980-01-01  1980-01-31  31    1
1980-02-01  1980-02-29  29    2
1980-03-03  1980-03-31  31    3
...
1981-01-01  1981-01-31  31    13
1981-12-01  1981-12-31  31    24
...
1985-01-01  1985-01-31  31    49
1985-12-01  1985-12-31  31    60

Answer 2

我想象你的“事实表”是这样的：

declare @dt datetime
    set @dt = '7-1-2012'

;
with date_table as  (
        select  @dt as [Start Date],
                dateadd(d,-1,dateadd(mm,1,@dt)) as [End Date],
                datepart(d,dateadd(d,-1,dateadd(mm,1,@dt))) as [Days]

        union ALL

        select  dateadd(mm, 1, [Start Date]) as  [Start Date],
                dateadd(d,-1,dateadd(mm,1,dateadd(mm, 1, [Start Date]))) as [End Date], 
                datepart(d,dateadd(d,-1,dateadd(mm,1,dateadd(mm, 1, [Start Date])))) as [Days]

        from    date_table
        where   dateadd(mm, 1, [Start Date]) <= dateadd(m,500,@dt))

select  [Start Date], [End Date], [Days]
into    #temp
from    date_table

option (MAXRECURSION 0)

这是在选择日期。(注意这些语句中没有包含DATEADD或DATEPART )

select  finish.[Start Date], finish.[End Date], finish.[Days]
from    (select rownum
         from   (select [Start Date], [End Date], [Days], row_number() over (order by [Start Date]) as rownum
                    from    #temp) as x
         where  x.[Start Date] = '2012-07-01 00:00:00.000' ) as start 

        join    (select [Start Date], [End Date], [Days], 
                        row_number() over (order by [Start Date]) as rownum
                 from   #temp) as finish
            on finish.rownum = start.rownum + 360

我在下面读了你的评论...如果你想总结这几天或者别的什么，你可以这样做：(所以从2012年7月1日开始，持续360个月……date_diff_days结果将是这360个月的总天数...使用我创建的#temp表...我假设它和你的事实表很相似。我有10957天)

select  sum(dayscount.[Days]) as date_diff_days
from    (select rownum
         from   (select [Start Date], [End Date], [Days], row_number() over (order by [Start Date]) as rownum
                    from    #temp) as x
         where  x.[Start Date] = '2012-07-01 00:00:00.000' ) as start 

        join    (select [Start Date], [End Date], [Days], 
                        row_number() over (order by [Start Date]) as rownum
                 from   #temp) as finish
            on finish.rownum = start.rownum + 360

        join (select    [Start Date], [End Date], [Days], 
                        row_number() over (order by [Start Date]) as rownum
                 from   #temp) as dayscount
            on dayscount.rownum >= start.rownum and 
                dayscount.rownum < finish.rownum 

Answer 3

如果我要以不可知的方式处理数据库，我会稍微修改一下事实表：

CREATE TABLE total_days
(
  year          INT,
  month         TINYINT,
  from_date     DATE,
  to_date       DATE,
  days_in_month SMALLINT
);

year  month  from_date   to_date     days_in_month
------------------------------------------------
1980    1    1980-01-01  1980-01-31   31
1980    2    1980-02-01  1980-02-29   29
...
2500   11    2500-11-01  2500-11-30   30
2500   12    2500-12-01  2500-12-31   31

然后你可以使用类似这样的东西：

SELECT td.* 
FROM 
      total_days AS td 
   CROSS JOIN
      ( SELECT year, month
        FROM total_days 
        WHERE from_date <= @StartingDate 
          AND @StartingDate <= to_date
      ) AS st
   CROSS JOIN
      ( SELECT 360 AS add_months ) AS param
WHERE td.year = st.year + ( st.month -1 + add_months ) / 12
  AND td.month = 1 + ( st.month - 1 + add_months ) % 12 )
;

或者更简单的(但为了提高效率更难优化：

WHERE 12 * td.year + td.month = 
      12 * st.year + st.month + add_months