Sum score between an effective time range in SQL Server database

Question

I have the following sample data in a SQL Server database.

UserID Score StartDate   EndDate
------------------------------------
1      10    2019-06-01  2019-07-15
1      20    2019-06-20  2019-07-01
1      30    2019-06-17  2019-07-25
2      10    2019-05-15  2019-06-10
2      20    2019-06-15  2019-07-01
2      30    2019-06-20  2019-07-15

And I need to achieve the following result.

UserID ScoreTotal StartDate   EndDate
----------------------------------------
1      10         2019-06-01  2019-06-17
1      40         2019-06-17  2019-06-20
1      60         2019-06-20  2019-07-01
1      40         2019-07-01  2019-07-15
1      30         2019-07-15  2019-07-25
2      10         2019-05-15  2019-06-10
2      20         2019-06-15  2019-06-20
2      50         2019-06-20  2019-07-01
2      30         2019-07-01  2019-07-15

It doesn't matter whether the score of the startdate or enddate is included in each calculation.

Any help would be the most appreciated.

Answer 1

Assuming your relation is A, the following query performs the operation you need:

WITH unwrapped AS (
    SELECT UserID, SUM(Score) AS Score, StartDate AS TempDate
    FROM A
    GROUP BY UserID, StartDate
    UNION ALL
    SELECT UserID, SUM(-1*Score) AS Score, EndDate AS TempDate
    FROM A
    GROUP BY UserID, EndDate
)
SELECT uw1.UserID, SUM(uw1.Score) AS ScoreTotal, MAX(uw1.TempDate) AS StartDate, uw2.TempDate AS EndDate
FROM unwrapped uw1 INNER JOIN unwrapped uw2
     ON uw1.UserID = uw2.UserID AND uw1.TempDate < uw2.TempDate
GROUP BY uw1.UserID, uw2.TempDate;