Convert SQL server 2012 query to SQL 2008

Question

0.00/5 (No votes)

See more:

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 out put?

SQL

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

What I have tried:

SQL

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

Posted 7-Sep-16 22:58pm

ashishkumarrai

Updated 8-Sep-16 2:58am

Herman<T>.Instance

v2

Add a Solution

Comments

Herman<T>.Instance 8-Sep-16 5:42am

What is the exact problem?

ashishkumarrai 8-Sep-16 5:47am

I get error when executing it in SQL 2008.The Parallel Data Warehouse (PDW) features are not enabled

ashishkumarrai 8-Sep-16 5:47am

output I am after.
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

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Alex Banu · Answer 1 · 2016-09-08T02:58:00

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:

SQL

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