CODEWITHSUNDEEP
sqlsql-server
vap0991
vap0991

Reputation: 113

How can I improve my SQL code for correct results?

Well, I am not sure what is the best way to this problem. But let me give you an example. What I am trying to achieve here is get the P2 value which is equal to carry when trying to sum P2 values in the descending order.. 10 to 1.

I have a huge table:

Category_Id Brand_Id Carry P2_0  P2_1  P2_3 ... P2_10
9           54       59    12    3     17       .
7           6        102   4     0     3        .
9           71       54    20    1     0        .
9           75       98    34    4     0        .
7           10       140   59    5     4        .

This is main logic of my code:

SELECT CategoryCode,Brand_Id, (CASE
    WHEN P2_10 > =  Carry Then 'Error' 
    WHEN P2_10 + P2_9 > =  Carry Then '10' 
    WHEN P2_10 + P2_9 + P2_8  > =  Carry Then '9' 
    WHEN P2_10 + P2_9 + P2_8 + P2_7  >=  Carry Then '8' 
    WHEN P2_10 + P2_9 + P2_8 + P2_7 + P2_6  >=  Carry Then '7'
    WHEN P2_10 + P2_9 + P2_8 + P2_7 + P2_6 + P2_5 > =  Carry Then '6' 
    WHEN P2_10 + P2_9 + P2_8 + P2_7 + P2_6 + P2_5 + P2_4 > =  Carry Then '5'
    WHEN P2_10 + P2_9 + P2_8 + P2_7 + P2_6 + P2_5 + P2_4 + P2_3 > =  Carry Then '4' 
    WHEN P2_10 + P2_9 + P2_8 + P2_7 + P2_6 + P2_5 + P2_4 + P2_3 + P2_2 > =  Carry Then '3'
    WHEN P2_10 + P2_9 + P2_8 + P2_7 + P2_6 + P2_5 + P2_4 + P2_3 + P2_2 + P2_1 > =  Carry Then '2'
    WHEN P2_10 + P2_9 + P2_8 + P2_7 + P2_6 + P2_5 + P2_4 + P2_3 + P2_2 + P2_1 + P2_0 > =  Carry Then '1'
    ELSE NULL END) As Threshold  from BQ_15

Now the issue here is say for a brand_id 6 if the carry is 106 then 

 P2_10(50) + P2_9(50) + P2_8(3) + P2_7(3) = Carry (106) gives the right result 

 but if  P2_10 + P2_9 + P2_8 + P2_7 > Carry it has to go back to previous result, if in the previous result the new P2 was '0' it has to back further. 

 so if P2_10(50) + P2_9(50) + P2_8(2) + P2_7(0) + P2_6(30) > Carry (106) then it should skip P2_7 (because it is zero) and go to P2_8 (desired result) but for my code it goes to P2_7.

I know I haven't included anything that skips '0' that is where my entire issue lies is how do I iterate in SQL that my code will work for both the cases and get the desired result.

Thanks in advance

Upvotes: 2

Views: 123

Answers (2)

shawnt00
shawnt00

Reputation: 17915

I doubt you'll have any issues with the previous answer using cross appy. If you do have complications or if you wanted to stick with your original case expression it isn't too difficult to find an expression that works--it's just a little messy. Here's what one of the cases could look like:

WHEN P2_10 + P2_9 + P2_8 + P2_7 >= Carry
THEN coalesce(
    nullif(sgn(P2_8)  *  8, 0),
    nullif(sgn(P2_9)  *  9, 0),
    nullif(sgn(P2_10) * 10, 0),
    -1
)

And actually that's unnecessarily complicated when you really just need to nest case expressions. Perhaps that's something you didn't realize was possible:

WHEN P2_10 + P2_9 + P2_8 + P2_7 >= Carry
THEN case when P2_8 > 0 then 8 when P2_9 > 0 then 9 when P2_10 > 0 then 10 else -1 end

Do you ever have sequences that start out with all zeroes that then jump over the carry threshhold: (0, 0, 0, 200)? And what about sums that never exceed the carry? I don't believe the other answer covers that scenario.

Upvotes: 1

Serg
Serg

Reputation: 22811

Use cross apply to make row scope calculations. I took 4 p2_xx columns, extend it as needed.

from (
     -- sample data
     values (9,54,106,  50,50,2,0,30)     
     ) hugeTable (Category_Id, Brand_Id, Carry, P2_10, P2_9, P2_8, P2_7, P2_6) 
cross apply (
    select Threshold = min(p2n)
    from (
        select p2n,
             s = sum(p2val) over(order by p2n desc)
        from (
             values
             (10, P2_10), (9, P2_9), (8, P2_8), (7, P2_7), (6, P2_6) 
        ) t(p2n, p2val)
        where p2val>0
    ) t
    where s <= Carry
) t

I use sum() over(), if you are on 2008 or earlier version then

from (
     -- sample data
     values (9,54,106,  50,50,2,0,30)     
     ) hugeTable (Category_Id, Brand_Id, Carry, P2_10, P2_9, P2_8, P2_7, P2_6) 
cross apply (
    select Threshold = min(p2n)
    from (
        select p2n,
             s = (select sum(p2val)
                 from (
                      values
                      (10, P2_10), (9, P2_9), (8, P2_8), (7, P2_7), (6, P2_6)  
                 ) t2(p2n, p2val)
                 where t2.p2n>=t.p2n )
        from (
             values
             (10, P2_10), (9, P2_9), (8, P2_8), (7, P2_7), (6, P2_6)  
        ) t(p2n, p2val)
        where p2val>0
    ) t
    where s <= Carry
) t

Upvotes: 2

Related Questions