根据开始和结束日期的年/月记录返回的金额 [英] return amount per year/month records based on start and enddate

问题描述

我有一个表，例如该数据：

I have a table with, for example this data:

ID |start_date  |end_date   |amount
---|------------|-----------|--------
1  |2019-03-21  |2019-05-09 |10000.00
2  |2019-04-02  |2019-04-10 |30000.00
3  |2018-11-01  |2019-01-08 |20000.00

我希望以年/月为基础，以正确的计算量取回拆分记录。

I would like te get the splitted records back with the correct calculated amount based on the year/month.

我希望结果如下：

ID |month |year   |amount
---|------|-------|--------
1  |3     | 2019  | 2200.00
1  |4     | 2019  | 6000.00
1  |5     | 2019  | 1800.00
2  |4     | 2019  |30000.00
3  |11    | 2018  | 8695.65
3  |12    | 2018  | 8985.51
3  |1     | 2019  | 2318.84

实现此目标的最佳方法是什么？我认为您必须使用DATEDIFF来获取start_date和end_date之间的天数，以计算每天的金额，但是我不确定如何将其作为每月/每年的记录返回。

What would be the best way to achieve this? I think you would have to use DATEDIFF to get the number of days between the start_date and end_date to calculate the amount per day, but I'm not sure how to return it as records per month/year.

提前Tnx！

推荐答案

这是一个主意。我使用Tally来创建每天与该 ID 相关的金额的一天。然后，我将 Amount 的值除以天数，然后将天数与月和年分组：

This is one idea. I use a Tally to create a day for every day the amount is relevant for for that ID. Then, I aggregate the value of the Amount divided by the numbers of days, which is grouped by Month and year:

CREATE TABLE dbo.YourTable(ID int,
                           StartDate date,
                           EndDate date,
                           Amount decimal(12,2));
GO
INSERT INTO dbo.YourTable (ID,
                           StartDate,
                           EndDate,
                           Amount)
VALUES(1,'2019-03-21','2019-05-09',10000.00),
      (2,'2019-04-02','2019-04-10',30000.00),
      (3,'2018-11-01','2019-01-08',20000.00);
GO
--Create a tally
WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT TOP (SELECT MAX(DATEDIFF(DAY, t.StartDate, t.EndDate)+1) FROM dbo.YourTable t) --Limits the rows, might be needed in a large dataset, might not be, remove as required
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 AS I
    FROM N N1, N N2, N N3), --1000 days, is that enough?
--Create the dates
Dates AS(
    SELECT YT.ID,
           DATEADD(DAY, T.I, YT.StartDate) AS [Date],
           YT.Amount,
           COUNT(T.I) OVER (PARTITION BY YT.ID) AS [Days]
    FROM Tally T
         JOIN dbo.YourTable YT ON T.I <= DATEDIFF(DAY, YT.StartDate, YT.EndDate))
--And now aggregate
SELECT D.ID,
       DATEPART(MONTH,D.[Date]) AS [Month],
       DATEPART(YEAR,D.[Date]) AS [Year],
       CONVERT(decimal(12,2),SUM(D.Amount / D.[Days])) AS Amount
FROM Dates D
GROUP BY D.ID,
         DATEPART(MONTH,D.[Date]),
         DATEPART(YEAR,D.[Date])
ORDER BY D.ID,
         [Year],
         [Month];

GO

DROP TABLE dbo.YourTable;
GO

数据库<>提琴

这篇关于根据开始和结束日期的年/月记录返回的金额的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！