How to query only rows that reached record count

Question

I have an unique query requirement and I can't use stored procedures or UDF.

I need to run a query that will return date and record count for a given predicate (example: device_id) and stop as soon as it reaches cumulative record count/day to 1000)

See the example below (look at the second table, that is the expected result)

Sample data:

Device-ID	date	rec-count
123	01/01/2022	500
124	01/01/2022	500
123	01/02/2022	250
124	01/02/2022	350
124	01/03/2022	500
123	01/04/2022	280
124	01/04/2022	500
123	01/05/2022	300

Expected output is:

Device-ID	date	rec-count
123	01/01/2022	500
123	01/02/2022	250
123	01/04/2022	280
124	01/02/2022	350
124	01/01/2022	500
124	01/03/2022	500

Answer 1

You can use a cumulative sum using a window function:

select device_id, date, rec_count
from (
  select device_id, date, 
         sum(rec_count) over (partition by device_id order by date) as rec_count
  from the_table
  where device_id = 123
) t
where rec_count <= 1000;

Given the where clause of the inner query, the partition by device_id` isn't really needed, but that way the query can easily be extended to work for multiple IDs.

The above is pure standard ANSI SQL and should work on any modern DBMS.