How can I improve my SQL code for correct results?

vap0991 Published at Dev

vap0991

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

Serg

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

Collected from the Internet

Please contact [email protected] to delete if infringement.