July 3, 2008 at 9:12 am
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
July 3, 2008 at 10:56 am
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...
Maninder
www.dbanation.com
July 3, 2008 at 11:02 am
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?
😎
July 6, 2008 at 1:14 am
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.
July 6, 2008 at 12:34 pm
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
July 6, 2008 at 2:20 pm
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
July 7, 2008 at 3:11 pm
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