如何在SQL Server中基于日期区间与每日成本表计算项目阶段成本

Got it, let's work through this cost allocation problem step by step. The core goal is to split daily costs across project phases based on how much each cost period overlaps with the phase's start/end dates—including handling phases that don't have a finish date yet.

Full Working SQL Code #

DECLARE @Table1 TABLE ( [ForeignKeyID] INT ,[ProjectPhase] CHAR (100) ,[StartDate] DATETIME ,[FinishDate] DATETIME );
DECLARE @Table2 TABLE ( [ForeignKeyID] INT ,[StartDate] DATETIME ,[EndDate] DATETIME ,[DailyCost] INT );

INSERT INTO @Table1 ([ForeignKeyID], [ProjectPhase], [StartDate], [FinishDate])
VALUES (1, 'Phase 1', '20210101 00:00:00', '20210103 00:00:00')
,(1, 'Phase 2', '20210103 00:00:00', '20210105 06:00:00')
,(1, 'Phase 3', '20210105 06:00:00', NULL);

INSERT INTO @Table2 ([ForeignKeyID], [StartDate], [EndDate], [DailyCost])
VALUES (1, '20210101 00:00:00', '20210102 00:00:00', 1000)
,(1, '20210102 00:00:00', '20210103 00:00:00', 1000)
,(1, '20210103 00:00:00', '20210104 00:00:00', 1000)
,(1, '20210104 00:00:00', '20210105 00:00:00', 1000)
,(1, '20210105 00:00:00', '20210106 00:00:00', 1000);

SELECT
    t1.ForeignKeyID,
    t1.ProjectPhase,
    CONVERT(VARCHAR(20), t1.StartDate, 103) + ' ' + FORMAT(t1.StartDate, 'HH.mm') AS StartDate,
    CASE WHEN t1.FinishDate IS NOT NULL THEN CONVERT(VARCHAR(20), t1.FinishDate, 103) + ' ' + FORMAT(t1.FinishDate, 'HH.mm') ELSE NULL END AS FinishDate,
    CAST(
        SUM(
            CASE 
                WHEN MAX(t1.StartDate, t2.StartDate) < ISNULL(t1.FinishDate, t2.EndDate) 
                THEN t2.DailyCost * (DATEDIFF(HOUR, MAX(t1.StartDate, t2.StartDate), MIN(ISNULL(t1.FinishDate, t2.EndDate), t2.EndDate)) / 24.0)
                ELSE 0
            END
        ) AS INT
    ) AS ProjectPhaseCost
FROM @Table1 t1
LEFT JOIN @Table2 t2 ON t1.ForeignKeyID = t2.ForeignKeyID
GROUP BY t1.ForeignKeyID, t1.ProjectPhase, t1.StartDate, t1.FinishDate
ORDER BY t1.StartDate;

How It Works #

Let's break down the key logic:

1. Overlap Time Calculation:
   • MAX(t1.StartDate, t2.StartDate): Grabs the later start date of the phase or cost period—this is where the actual overlap begins.
   • MIN(ISNULL(t1.FinishDate, t2.EndDate), t2.EndDate): Gets the earlier end date. If the phase doesn't have a finish date (still active), we use the cost period's end date instead.
2. Prorated Cost Calculation:
   • We use DATEDIFF(HOUR, ...) to count total overlapping hours, then divide by 24 to get fractional days (since we need hour-level precision for partial days).
   • Multiply that fraction by the DailyCost to get the portion of the cost allocated to the phase.
3. Edge Case Handling: The CASE statement skips any cost periods that don't overlap with the phase (adding 0 instead of invalid values).

Expected Output #

This matches exactly the results you were looking for!

内容的提问来源于stack exchange，提问作者Harzio