如何在SQL中生成收入预测?

您需要做的是首先为每个合同/产品生成一个日期序列,然后使用该序列进行分组。

生成序列的最方便的方法是使用记录较差的spt_值表,如下所示:

WITH Contracts_CTE (contract_id, product_id, contract_day, amount) AS
(
    SELECT 
        cl.contract_id,
        cl.product_id,
        DATEADD(DAY, v.number, cl.contract_line_start),
        cl.amount / DATEDIFF(DAY, cl.contract_line_start, cl.contract_line_end)
    FROM contracts_lines cl
    CROSS JOIN master.dbo.spt_values v
    WHERE v.type = 'P'
    AND DATEADD(DAY, v.number, cl.contract_line_start) < cl.contract_line_end
)
SELECT
    DATEPART(YEAR, c.contract_day) AS contract_year,
    DATEPART(MONTH, c.contract_day) AS contract_month,
    c.product_id,
    c.contract_id,
    SUM(Amount) AS contract_amount
FROM Contracts_CTE c
GROUP BY
    c.product_id,
    c.contract_id,
    DATEPART(YEAR, c.contract_day),
    DATEPART(MONTH, c.contract_day)

需要注意的是,spt_值中的数字仅从0到2047,因此,如果合同期限超过5年,则需要生成更长的序列。最简单的方法是将spt_值表交叉连接到自身,即:

SELECT (v1.number * 2048) + v2.number
FROM master.dbo.spt_values v1
CROSS JOIN master.dbo.spt_values v2
WHERE v1.type = 'P'
AND v2.type = 'P'
AND ((v1.number * 2048) + v2.number) < 100000

显然,您必须将其集成到上面的查询中,但是如果确实有必要,那么它应该不会太难。

这不是完整的解决方案,但您可能希望根据每月30天计算每个项目的每月收入。

大概是这样的:

select contract_id, product_id, 
       amount / (datediff(day, contract_line_start, contract_line_end)) * 30 as MontlyRevenue
from contracts_lines