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.

Leave a Comment

Your email address will not be published.

Scroll to Top