Need help with an optimized query for the following situation

  • I have a table with the following format:

    Sr1Col1 Col2Col3

    1BU123456 80440

    2BU123456 0KPMGF1

    3BU123456 8044KPMGF1

    4FC123456 80440

    5FC123456 0KPMGF1

    6FC123456 8044KPMGF1

    7BU123456 00

    8FC123456 00

    Need help with an optimized query for.

    The following conditions:

    If we have an exact match of col2 and col3 for BU values then that row gets selected (row 3).

    Else if there is an exact match for col2 and col3 for FC values then that row gets selected (row 6) this is if row 3 is not present in the above table.

    Else if we have col2 matching and Col3 is 0 for BU values then it gets selected (row 1) in the above table if row 3 and 6 are not there.

    Else if we have a col2 matching and col3 is 0 for FC values then it gets selected (row 4) in the above table if row 3, 6 and 1 are not there.

    Else if we have a col3 matching and col2 is 0 for BU values then it gets selected (row 2) in the above table if row 3, 6, 1 and 4 are not there.

    Else if we have a col3 matching and col2 is 0 for FC values then it gets selected (row 5) in the above table if row 3, 6, 1, 4 and 2 are not there.

    Else I get no values selected

  • Use can use a cursor(nested) to perform the Following operation.

    I did not understand one thing: how can column2=column3 as the values do not match, unless you have a Criteria set for 1000=KCFV and 2000=KCFF something like this.. so lot of CASE Statements as well..

    do you already have a Query in similar ASPECTS...

  • aayub (7/3/2008)


    I have a table with the following format:

    Sr1Col1 Col2Col3

    1BU123456 80440

    2BU123456 0KPMGF1

    3BU123456 8044KPMGF1

    4FC123456 80440

    5FC123456 0KPMGF1

    6FC123456 8044KPMGF1

    7BU123456 00

    8FC123456 00

    Need help with an optimized query for.

    The following conditions:

    If we have an exact match of col2 and col3 for BU values then that row gets selected (row 3).

    Else if there is an exact match for col2 and col3 for FC values then that row gets selected (row 6) this is if row 3 is not present in the above table.

    Else if we have col2 matching and Col3 is 0 for BU values then it gets selected (row 1) in the above table if row 3 and 6 are not there.

    Else if we have a col2 matching and col3 is 0 for FC values then it gets selected (row 4) in the above table if row 3, 6 and 1 are not there.

    Else if we have a col3 matching and col2 is 0 for BU values then it gets selected (row 2) in the above table if row 3, 6, 1 and 4 are not there.

    Else if we have a col3 matching and col2 is 0 for FC values then it gets selected (row 5) in the above table if row 3, 6, 1, 4 and 2 are not there.

    Else I get no values selected

    What is your current query (post the code) and based on the data above, what is the expected output from the query?

    😎

  • I am not sure if this is the best way to do it.

    if exists( Select 1 from @TempBalBU where ELcol1 like @EL4 And col2 like @EL5 And balCode like 'BU123456')

    Select @TotalAmount = @totalAmount + IsNull(sum(Amount),0) from @TempBalBU where ELcol1 like @EL4 And col2 like @EL5 And balCode like 'BU123456'

    Else

    if exists( Select 1 from @TempBalBU where ELcol1 like @EL4 And col2 like @EL5 And balCode like 'FC123456')

    Select @TotalAmount = @totalAmount + IsNull(sum(Amount),0) from @TempBalBU where ELcol1 like @EL4 And col2 like @EL5 And balCode like 'FC123456'

    Else

    If @flg_ChkPref = 1 -- where we have preferences in Col1 and Col2

    Begin

    if exists( Select 1 from @TempBalBU where Col1 like @EL4 And Col2 like '0' And balCode like 'BU123456' )

    Select @TotalAmount = @totalAmount + IsNull(sum(Amount),0) from @TempBalBU where where Col1 like @EL4 And Col2 like '0' And balCode like 'BU123456'

    Else

    if exists( Select 1 from @TempBalBU where Col1 like @EL4 And Col2 like '0' And balCode like 'FC123456'

    Select @TotalAmount = @totalAmount + IsNull(sum(Amount),0) from @TempBalBU where Col1 like @EL4 And Col2 like '0' And balCode like 'FC123456'

    Else

    if exists( Select 1 from @TempBalBU where Col1 like '0' And Col2 like @EL5 And balCode like 'BU123456' )

    Select @TotalAmount = @totalAmount + IsNull(sum(Amount),0) from @TempBalBU where Col1 like '0' And Col2 like @EL5 And balCode like 'BU123456'

    Else

    if exists( Select 1 from @TempBalBU where Col1 like '0' And Col2 like @EL5 And balCode like 'FC123456')

    Select @TotalAmount = @totalAmount + IsNull(sum(Amount),0) from @TempBalBU where Col1 like '0' And Col2 like @EL5 And balCode like 'FC123456'

    End

    This is the querry that I am using at the moment.

    The problem that I see is that the code is repeating again and again.


    Regards,

    Muhammad Arsalan Ayub
    groups.yahoo.com

    Blog

  • What are the expected results you are looking for?

    Please read the following article:

    Best Practices: http://www.sqlservercentral.com/articles/Best+Practices/61537/

    If you provide the create and insert statement and expected results, it will be a lot easier for someone to help you out.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Maybe you can adapt the following to work for you.

    If object_id('tempdb..#test') Is Not Null

    Begin;

    Drop Table #test;

    End;

    Go

    Create Table #test (Sr1 int, Col1 char(8), Col2 int, Col3 char(6));

    Go

    Insert Into #test (Sr1, Col1, Col2, Col3) Values (1, 'BU123456', 8044, '0');

    Insert Into #test (Sr1, Col1, Col2, Col3) Values (2, 'BU123456', 0, 'KPMGF1');

    Insert Into #test (Sr1, Col1, Col2, Col3) Values (3, 'BU123456', 8044, 'KPMGF1');

    Insert Into #test (Sr1, Col1, Col2, Col3) Values (4, 'FC123456', 8044, '0');

    Insert Into #test (Sr1, Col1, Col2, Col3) Values (5, 'FC123456', 0, 'KPMGF1');

    Insert Into #test (Sr1, Col1, Col2, Col3) Values (6, 'FC123456', 8044, 'KPMGF1');

    Insert Into #test (Sr1, Col1, Col2, Col3) Values (7, 'BU123456', 0, '0');

    Insert Into #test (Sr1, Col1, Col2, Col3) Values (8, 'FC123456', 0, '0');

    Go

    Declare @el4 int

    ,@el5 char(6);

    Set @el4 = 8044;

    Set @el5 = 'KPMGF1';

    With cte (Sr1, Col1, Col2, Col3, rnk)

    As (Select Sr1

    ,Col1

    ,Col2

    ,Col3

    ,rank() Over(

    Order By Case

    When col2 = @el4 Then -1

    Else col2

    End

    ,Case

    When col3 = @el5 Then '-1'

    Else col3

    End

    ,Case col1

    When 'BU123456' Then 0

    Else 1

    End)

    From #test

    )

    Select *

    From cte

    -- Where rnk = 1;

    From what I can tell - it looks like what you want to be able to do is rank the selection criteria. Once the ranking is correct - all you need to do at that point is select the ranking that you want.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • I think i would use the Case-function to determine a 'priority' for each row,

    en then select the record with the highest priority.

    Like this:

    First, the determination of priority:

    SELECT *,

    CASE WHEN ( ( UPPER(LEFT(Col1,2)) = 'BU' ) AND ( Col2 <> 0 ) AND ( Col3 <> '0' ) ) THEN 1

    WHEN ( ( UPPER(LEFT(Col1,2)) = 'FC' ) AND ( Col2 <> 0 ) AND ( Col3 <> '0' ) ) THEN 2

    WHEN ( ( UPPER(LEFT(Col1,2)) = 'BU' ) AND ( Col2 <> 0 ) AND ( Col3 = '0' ) ) THEN 3

    WHEN ( ( UPPER(LEFT(Col1,2)) = 'FC' ) AND ( Col2 <> 0 ) AND ( Col3 = '0' ) ) THEN 4

    WHEN ( ( UPPER(LEFT(Col1,2)) = 'BU' ) AND ( Col2 = 0 ) AND ( Col3 <> '0' ) ) THEN 5

    WHEN ( ( UPPER(LEFT(Col1,2)) = 'FC' ) AND ( Col2 = 0 ) AND ( Col3 <> '0' ) ) THEN 6

    ELSE 0

    END AS Ranking

    FROM TempBalUI

    Then, retrieving the first row with the lowest ranking (not 0),

    since a 'low' ranking means a high priority:

    WITH ListValues

    AS ( SELECT *,

    CASE WHEN ( ( UPPER(LEFT(Col1,2)) = 'BU' ) AND ( Col2 <> 0 ) AND ( Col3 <> '0' ) ) THEN 1

    WHEN ( ( UPPER(LEFT(Col1,2)) = 'FC' ) AND ( Col2 <> 0 ) AND ( Col3 <> '0' ) ) THEN 2

    WHEN ( ( UPPER(LEFT(Col1,2)) = 'BU' ) AND ( Col2 <> 0 ) AND ( Col3 = '0' ) ) THEN 3

    WHEN ( ( UPPER(LEFT(Col1,2)) = 'FC' ) AND ( Col2 <> 0 ) AND ( Col3 = '0' ) ) THEN 4

    WHEN ( ( UPPER(LEFT(Col1,2)) = 'BU' ) AND ( Col2 = 0 ) AND ( Col3 <> '0' ) ) THEN 5

    WHEN ( ( UPPER(LEFT(Col1,2)) = 'FC' ) AND ( Col2 = 0 ) AND ( Col3 <> '0' ) ) THEN 6

    ELSE 0

    END AS Ranking

    FROM TempBalUI

    )

    SELECT TOP 1 Sr1, Col1, Col2, Col3

    FROM ListValues

    WHERE ( Ranking <> 0 )

    ORDER BY Ranking ASC

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply