MsSQL how to get nearest future value and sum of value from given date and warehouse number in one query

Question

I have data as follow

I need to select sum of future values by warehouse and nearest future value by warehouse.

Eg. result should be like (if getdate() = 16-09-2020):

Is it possible to do it with one query?

Answer 1

You could use window functions only:

select *
from (
    select 
        warehouse, 
        sum(value) over(partition by warehouse) sum_future_value
        value nearest_future_value,
        date  nearest_future_date,
        row_number() over(partition by warehouse order by date) rn
    from mytable
    where date > getdate()
) t
where rn = 1

Within the subquery, we filter on future dates, compute the sum of upcoming values by warehouse, and rank records. All that is left to do is filter on the earliers record per group.