I have a SQL Server 2019 stored procedure which will allow a user to enter the number of minutes of calls they want to see. If he enters 20 minutes, then I want to show him the first x number of records where the of sum of call_length = 20
and < 21
(rounded to the nearest minute) of all of the records.
My code below just selects the records where call_length
is between 20 and 21. I need the sum of the call_length
of the records to be between 20 and 21. Thanks
DECLARE @Call_Mins int
SET @Call_Mins = 20
SELECT
reviewid, cumulativesum
FROM
(SELECT
reviewid,
SUM(CONVERT(float,REPLACE(Call_Length,':','.'))) OVER (PARTITION BY ReviewID ORDER BY ReviewID ASC) AS cumulativesum
FROM
LegacyReviews) t
WHERE
cumulativesum >= @Call_Mins
AND cumulativesum <= @Call_Mins + 1
Data looks like this:
This answer is assuming that your [Call_Length]
is stored as a Time datatype. (hh:mm:ss
)
DECLARE @Call_Mins AS INT
SET @Call_Mins = 20;
WITH cte as (
SELECT
[reviewid],
[Call_Length],
[RunningTotal] = SUM(DATEDIFF(SECOND, '00:00:00', [Call_Length])) OVER (ORDER BY [reviewid])
FROM [LegacyReviews])
SELECT
cte.[reviewid],
cte.[Call_Length]
FROM cte
WHERE [RunningTotal] < (@Call_Mins + 1) * 60
This query uses a Common Table Expression and a running total, that isn't returned per your question, that will not return anything past the first x number of rows that add up to whatever @Call_Mins
is set to (+ 1
). This would, in your example, return any values up to 20:59