I found this :
Quote:
This is a parser bug that exists only in SQL Server 2008. Non-PDW versions of SQL Server before 2012 do not support the ORDER BY clause with aggregate functions like MIN
more details
You don't have a SQL 2008 here, but considering the above mentioned, i think you can try this:
declare @Buckets table (ID char(2), FullCapacity int)
declare @Filler table (ID char(2), Filler int)
insert into @Buckets values
('B1', 100),
('B2', 50),
('B3', 70)
insert into @Filler values
('F1', 90),
('F2', 70),
('F3', 40),
('F4', 20)
;with fillerCte as
(
select
ID,
Filler,
sum(Filler) over (order by ID) as TotalFill
from @Filler
),
BucketCte as
(
select
ID,
FullCapacity,
sum(FullCapacity) over (order by ID) - FullCapacity as RunningTotalCapacity
from @Buckets
)
select
b.ID as b_ID,
f.ID as f_ID,
b.FullCapacity,
case
when f.TotalFill < b.RunningTotalCapacity then 0
when f.TotalFill > b.RunningTotalCapacity + b.FullCapacity then b.FullCapacity
else f.TotalFill - b.RunningTotalCapacity
end as CurrentAmount
into #tmp
from fillerCte as f
cross join BucketCte as b
SELECT f_ID as ID, FullCapacity,CurrentAmount
FROM
#tmp
ORDER BY f_ID, b_ID