user2123852 user2123852 - 3 months ago 17
SQL Question

Convert SQL Server 2012 CTE to SQL Server 2008

I have the below query which works fine in 2012 as it is using the 2012 features. Can anyone make the exact same query work in 2008 with same output? I am looking to get Bucket filling data from Filler table to Bucket table. I get error when executing it in SQL 2008.

The Parallel Data Warehouse (PDW) features are not enabled


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,
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
from fillerCte as f
cross join BucketCte as b
order by f.ID, b.ID


Output table should have below showing filling process.

ID | FullCapacity | CurrentAmount
---+--------------+--------------
B1 | 100 | 90
B2 | 50 | 0
B3 | 70 | 0
---+--------------+--------------
B1 | 100 | 100
B2 | 50 | 50
B3 | 70 | 10
---+--------------+--------------
B1 | 100 | 100
B2 | 50 | 50
B3 | 70 | 50
---+--------------+--------------
B1 | 100 | 100
B2 | 50 | 50
B3 | 70 | 70

Answer

To do the same query in SQL Server 2008, you have to calculate the running total in two steps, the first step is to rank the rows using the ROW_NUMBER() OVER(ORDER BY ID), then use the row number to calculate the running total:

;WITH fillerCte as
(
  SELECT 
   ID,
   Filler,
   ROW_NUMBER() OVER(ORDER BY Id) AS RN
   FROM @Filler
), FillerRT
AS
(
  SELECT 
    f1.ID,
    f1.Filler,
    SUM(f2.Filler) AS TotalFill
  FROM fillerCte AS f1
  INNER JOIN fillerCte AS f2 ON f2.rn <= f1.rn
  GROUP BY f1.ID, f1.Filler
), BucketCte as
(
  SELECT
    ID,
    FullCapacity,
    ROW_NUMBER() OVER(ORDER BY ID) AS RN
  FROM @Buckets
),BucketRN
AS
(
  SELECT 
    b1.ID,
    b1.FullCapacity,
    SUM(b2.FullCapacity) - b1.FullCapacity AS RunningTotalCapacity
  FROM BucketCte AS b1 INNER JOIN BucketCte AS b2 ON b2.RN <= b1.RN
  GROUP BY b1.ID, b1.FullCapacity
)
select 
b.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
from FillerRT as f
cross join BucketRN as b
order by f.ID, b.ID;

This will give you the same exact output of your query.