# Sum values if they are between date range sql

OK, judging by the provided information – and as far as I understood everything right – the following approach might solve your problem:

``````DECLARE @t TABLE(
CreationDate date,
EndDate date,
Value_An decimal(19,4)
)

INSERT INTO @t VALUES
('2019-03-01', '2019-03-01', 3.4)
,('2019-03-01', '2019-03-03', 3.5)
,('2019-05-01', '2019-05-01', 3.6)
,('2019-06-01', '2019-06-04', 3.7)

;WITH cteMultiRow AS(
SELECT CreationDate, COUNT(*) cntRows
FROM @t
GROUP BY CreationDate
HAVING COUNT(*) > 1
),
cte AS(
SELECT t.*
,ROW_NUMBER() OVER (PARTITION BY t.CreationDate ORDER BY t.EndDate) AS rn
,DATEDIFF(d, t.CreationDate, t.EndDate)+1 AS Difference
,CASE WHEN m.CreationDate IS NOT NULL THEN t.Value_An/(DATEDIFF(d, t.CreationDate, t.EndDate)+1) ELSE t.Value_An END AS ValuePerD
FROM @t t
LEFT JOIN cteMultiRow m ON t.CreationDate = m.CreationDate
),
cteSums AS(
SELECT c.CreationDate, SUM(c.ValuePerD) AS ValuePerD
FROM cte c
GROUP BY c.CreationDate
)
SELECT c.CreationDate, c.EndDate, c.Value_An, c.Difference, c.ValuePerD, ISNULL(s.ValuePerD, c.Value_An) AS ValueEnd
FROM cte c
LEFT JOIN cteSums s ON c.CreationDate = s.CreationDate AND c.rn = 1
``````

CLICK HERE to find out more related problems solutions.

Scroll to Top