[FIXED] How to calculate Project Phase cost based on interval date and daily cost table on SQL Server

Issue

I have two tables on SQL Server. The first table stores Project Phase data: ProjectPhase, StartDate, and FinishDate. And the second table stores Daily Cost data: StartDate, EndDate, and Daily Cost. Both table has a relationship using a foreign key column.

I want to calculate a project phase cost based on the start & finish date of the phase and the prorated daily cost. I have an example code as below:

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 
    xxx
FROM 
    @Table1 t1
    LEFT JOIN @Table2 t2 ON t1.ForeignKeyID = t2.ForeignKeyID

I am expecting a result as below:

ForeignKeyID	ProjectPhase	StartDate	FinishDate	ProjectPhaseCost
1	Phase 1	01/01/2021 00.00	03/01/2021 00.00	2000
1	Phase 2	03/01/2021 00.00	05/01/2021 06.00	2250
1	Phase 3	05/01/2021 06.00		750

Thanks very much for any help or idea.

Solution

First, you need to match the rows. This is a standard interval-matching condition: start1 < end2 and end1 > start2. You need to be careful with the logic around inclusive or exclusive dates.

Next, you need to calculate how many days. We need to do this in seconds otherwise we only get whole days. We take the later start and earlier end of the two tables.

Then just sum that multiplied by the cost.

SELECT 
    t1.ForeignKeyID,
    t1.ProjectPhase,
    t1.StartDate,
    t1.FinishDate,
    ProjectPhaseCost = SUM(
        t2.DailyCost *
        DATEDIFF(second,
            IIF(t1.StartDate > t2.StartDate, t1.StartDate, t2.StartDate),
            IIF(t1.FinishDate < t2.EndDate,   t1.FinishDate, t2.EndDate)
        ) / 86400.0)
FROM @Table1 t1
JOIN @Table2 t2
  ON t1.ForeignKeyID = t2.ForeignKeyID AND
     t1.StartDate < t2.EndDate AND t1.FinishDate > t2.StartDate
GROUP BY
    t1.ForeignKeyID,
    t1.ProjectPhase,
    t1.StartDate,
    t1.FinishDate;

Answered By - Charlieface

Answer Checked By - Senaida (PHPFixing Volunteer)