主页 / dba / 问题 / 161749
Accepted
Tufan Chand
Asked: 2017-01-21 05:25:14 +0800 CST

在sqlserver中按月计算日期差异

  • 772

我正在使用 SQL 服务器。我想按月计算确切的日期差异。我知道为此有一个内置函数DATEDIFF，但我想要一些自定义小数差异，例如：

sDate(yyyy-MM-dd)  eDate          output              formula
---------------------------------------------------------------------------------------------------------------
2016-03-03        2016-03-05      0.0967741           3.00/31.00 (daycount/lastday of the month)
2016-03-05        2016-06-05      3.0376343          (27.00/31.00)+(30.00/30.00)+(31.00/31.00)+(5.00/30.00)
2016-02-26        2016-04-03      1.2379310          (4.00/29.00)+(31.00/31.00)+(3.00/30.00)

那么这将如何计算呢？我可以通过使用函数来应用此逻辑，但我不想这样做。如何在单个查询中应用此逻辑？

您的宝贵建议将不胜感激。

sql-server functions

3 个回答

  1. 在迷你日历表的帮助下，这可以很容易地处理：

    rextester 设置：http ://rextester.com/ZHTTZ86931

    测试数据：

    create table tmp (fromdate date,thrudate date,monthdiff decimal(19,7),formula varchar(64))
insert into tmp (fromdate, thrudate, monthdiff, formula) values
 ('2016-02-26','2016-04-03',1.2379310,'(4.00/29.00)+(31.00/31.00)+(3.00/30.00)')
,('2016-03-03','2016-03-05',0.0967741,'3.00/31.00 (daycount/lastday of the month)')
,('2016-03-05','2016-06-05',3.0376343
                         ,'(27.00/31.00)+(30.00/30.00)+(31.00/31.00)+(5.00/30.00)')
,('2016-01-01','2016-12-31',12,'');

    创建一个迷你日历表来跟踪每个日期及其月份的百分比。

      /* creating a DayPercentOfMonth table */
declare @FromDate date = '20100101';
declare @ThruDate date = '20301231';
create table dbo.DayPercentOfMonth (
    [Date] date not null primary key
  , DayPercentOfMonth decimal(9,8) not null
);
with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
, cal as (
  select 
    Date =convert(date,dateadd(day, row_number() over (order by (select 1)) -1, @fromdate))
    , [DayPercentOfMonth] = convert(decimal(6,4),1)
                           /convert(decimal(6,4)
           ,datepart(day,dateadd(day,-1,dateadd(Month,datediff(Month,0
           ,row_number() over (order by (select 1)) -1)+1
           ,0))))
    from         n as deka
      cross join n as hecto
      cross join n as kilo     /* 2.73 years */
      cross join n as [tenK]    /* 27.3 years */
      --cross join n as [hundredk] /* 273  years */
    )
insert into dbo.DayPercentOfMonth ([Date],[DayPercentOfMonth])
select [Date],DayPercentOfMonth
from cal;

    如果您真的不想创建辅助表，则可以使用递归 cte 的替代版本：

    /* set from & thru dates for calendar cte */
declare @FromDate date = '20160101';
declare @ThruDate date = '20161231';

with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
, cal as (
  select 
    Date =convert(date,dateadd(day, row_number() over (order by (select 1)) -1, @fromdate))
    , [DayPercentOfMonth] = convert(decimal(6,4),1)
                           /convert(decimal(6,4)
           ,datepart(day,dateadd(day,-1,dateadd(Month,datediff(Month,0
           ,row_number() over (order by (select 1)) -1)+1
           ,0))))
    from         n as deka
      cross join n as hecto
      cross join n as kilo     /* 2.73 years */
      cross join n as [tenK]    /* 27.3 years */
      --cross join n as [hundredk] /* 273  years */
    )

    然后查询只是加入日历日期介于起始日期和截止日期之间的日历，并对DayPercentOfMonth列求和。

    select 
    fromdate=convert(varchar(10),t.fromdate,120)
  , thrudate=convert(varchar(10),t.thrudate,120)
  , monthdiff=format(t.monthdiff ,'f8','en-US')
  , SumDayPercentOfMonth=format(convert(decimal(19,8),sum(DayPercentOfMonth)),'f8','en-US')
  --select fromdate, thrudate, monthdiff, SumDayPercentOfMonth=sum(DayPercentOfMonth)
  from tmp as t 
    inner join cal c on c.date >= t.fromdate 
                    and c.date <= t.thrudate
  group by t.fromdate, t.thrudate, t.monthdiff, t.formula
  order by fromdate

results in:

+------------+------------+-------------+----------------------+
|  fromdate  |  thrudate  |  monthdiff  | SumDayPercentOfMonth |
+------------+------------+-------------+----------------------+
| 2016-01-01 | 2016-12-31 | 12.0000000  | 12.00000000          |
| 2016-02-26 | 2016-04-03 | 1.2379310   | 1.23793103           |
| 2016-03-03 | 2016-03-05 | 0.0967741   | 0.09677419           |
| 2016-03-05 | 2016-06-05 | 3.0376343   | 3.03763441           |
+------------+------------+-------------+----------------------+

    注意：输出的示例数据值并不像它们看起来那样精确。

    (27.00/31.00)+(30.00/30.00)+(31.00/31.00)+(5.00/30.00) = 3.037634408602151 != 3.0376343
(3.00/31.00) = 0.0967741935483871 != 0.0967741 -- truncated
    • 2

  2. 由于您已经知道要应用于特定日期差异的公式，为什么不使用简单的CASE表达式并应用您的特定公式。

    • 1
  3. Best Answer

    最后我找到了我的问题的解决方案并在这里发布。我想在一个查询中使用该解决方案，所以下面是解决方案：

    首先，我计算不包括两个给定日期的开始月份和结束月份的月份数。然后是从开始日期开始的小数月数和从结束日期开始的相同月数。

    SAMPLE
---------------------------------------------------------------------------------
declare @startdate date set @startdate='2016-10-12'
declare @enddate date set @enddate='2019-08-12'

select 
cast(round(  convert(decimal(9,4),datediff(mm, dateadd(mm,1,@startdate), dateadd(mm,-1,@enddate))+1) -- middle months count
+ convert(decimal(9,4),((DAY(EOMONTH(@startdate))-DAY(@startdate))+1))/convert(decimal(9,4),(DAY(EOMONTH(@startdate))))--start date month count
+ convert(decimal(9,4),DAY(@enddate))/convert(decimal(9,4),(DAY(EOMONTH(@enddate))))--end date month count
,4) as numeric(9,4))
    • 0

相关问题