--------------------------------------------------------------------
/*
دوره صوتی / ویدئویی تحلیل و طراحی بانک اطلاعاتی سیستم خرید و فروش
Site:        www.NikAmooz.com
Email:       Info@NikAmooz.com
Forum:       forum.NikAmooz.com
Created By:  Ahmad Ghaffari & Farid Taheri & Masoud Taheri 
*/
--------------------------------------------------------------------


create table #sample
(
    buy_date date,
    unit_price int,
    quantity int
)
go
 
insert into #sample (buy_date, quantity, unit_price) values 
('1392/01/15',     200,     300 ),
('1392/03/01',     250,     320 ),
('1392/06/01',     300,     330 ),
('1392/09/01',     250,     350);
 
with cte1 as
(
select *,
       sum(quantity) over(order by buy_date desc
                          rows between unbounded preceding 
                                  and current row) as running_total
from #sample
)
--SELECT * FROM cte1
, 
  cte2 as
(
select *,
       lag(running_total) over(order by buy_date desc) as lag
 
from cte1
)
--SELECT * FROM CTE2
,
 cte3 as
(
select *,row_number() over(order by buy_date desc) rnk
from cte2
where 
	running_total <= 500
	or 
	lag < 500
)
--SELECT * FROM CTE3
select sum(case 
             when running_total<500 then quantity * unit_price
             else (500-lag) * unit_price
           end) as value
from cte3;
drop table #sample
