How to select records in date order that total to an arbitrary amount?

Question

I have a table of fuel deliveries as follows:

Date        Time        Qty
20160101    0800       4500
20160203    0900       6000
20160301    0810       3400
20160328    1710       5300
20160402    1201       6000

I know that on April 1st I had 10,000 litres in the tank so now I want to select just the deliveries that make up the total. This means I want the records for 20160328,20160301 and 20160203. I am using Postgres and I want to know how to structure a select statement that would accomplish this task. I understand how to use the where clause to filter records whose date is less than on equal April 1st but I do not know how to instruct Postgres to select the records in reverse date order until the quantity selected is greater than or equal to 10,000.

Answer 1

with d as (
    select *, sum(qty) over (order by date desc, time desc) as total
    from delivery
    where date between '20160101' and '20160401'
)
select *
from d
where total < 10000
union
(
    select *
    from d
    where total >= 10000
    order by date desc, time desc
    limit 1
)
order by date desc, time desc
;
    date    |   time   | qty  | total 
------------+----------+------+-------
 2016-03-28 | 17:10:00 | 5300 |  5300
 2016-03-01 | 08:10:00 | 3400 |  8700
 2016-02-03 | 09:00:00 | 6000 | 14700

The data:

create table delivery (date date, time time, qty int);
insert into delivery (date, time, qty) values
('20160101','0800',4500),
('20160203','0900',6000),
('20160301','0810',3400),
('20160328','1710',5300),
('20160402','1201',6000);