CODEWITHSUNDEEP
sqlsql-servert-sqlssis
DenStudent
DenStudent

Reputation: 928

SSIS/SQL Server - Flag based on multiple condition columns

I have following table:

 ID | Number | Condition 1 | Condition 2 | Condition 3 | Condition 4 
 1  | 1      | 1           | 2           | 1           | 1
 1  | 2      | 2           | 1           | 2           | 2
 2  | 5      | 2           | 2           | 2           | 1
 2  | 6      | 2           | 2           | 2           | 2
 3  | 7      | 1           | 1           | 2           | 1
 3  | 8      | 2           | 1           | 1           | 2
 3  | 3      | 2           | 1           | 2           | 2
 4  | 9      | 2           | 1           | 1           | 1
 4  | 4      | 1           | 1           | 1           | 2
 5  | 10     | 2           | 1           | 2           | 1
 5  | 13     | 2           | 1           | 2           | 2

(Quite confusion, sorry about that)

As you can see, there are multiple IDs. I have to take one of each ID, based on the conditions. So, I want to check each condition one by one and add a column based on the result in my output. The logic behind this is

if (for each group of ID's) one row has condition = 1 then that's the correct one. If both rows are 1 or both are 2, then we check the following condition.

So, If we take only the records with ID 1, we can see the ID 1 - Number 1 is the correct one we need in our output.

But if we take ID 2, we see that both records have the same value, which doesn't give us an answer. In that case, we look at condition 2 and implement the same logic there.

(condition 4 will ALWAYS have one record with '1' for each ID)

So my output would be:

 ID | Number | Condition 1 | Condition 2 | Condition 3 | Condition 4 | IsOk
 1  | 1      | 1           | 2           | 1           | 1           | 1
 1  | 2      | 2           | 1           | 2           | 2           | 2
 2  | 5      | 2           | 2           | 2           | 1           | 1
 2  | 6      | 2           | 2           | 2           | 2           | 2
 3  | 7      | 1           | 1           | 2           | 1           | 1
 3  | 8      | 2           | 1           | 1           | 2           | 2
 3  | 3      | 2           | 1           | 2           | 2           | 2
 4  | 9      | 2           | 1           | 1           | 1           | 2
 4  | 4      | 1           | 1           | 1           | 2           | 1
 5  | 10     | 2           | 1           | 2           | 1           | 1
 5  | 13     | 2           | 1           | 2           | 2           | 2

Any advice/Suggestions?

I will be using this in SSIS, so solutions in SSIS or pure SQL code are all OK for me.

In case someone wonders: I tried it with Case

  ,CASE
   when [Condition1] = 1 then 1
    else CASE when [Condition2] = 1 then 1 
     else   CASE when [Condition3] = 1 then 1 
        else        CASE when [Condition4] = 1 then 1 END
        END
        END
   END AS IsOk

But that didn't give the requested result

Upvotes: 0

Views: 438

Answers (1)

iamdave
iamdave

Reputation: 12243

This can be achieved with a self referential derived table. Essentially, you check whether each row would be independently 'Ok' and then join that dataset to itself to check if a previous row was already deemed 'Ok', in which case mark it as 'Not Ok'. This works because your conditions are always 1 or 2 so when creating the row_number you can order in preference of Condition and which will guarantee that the 'first' row to be 'Ok' will have the preferential row_number ie: You won't get any cases of an 'Ok' row being returned with a higher row_number than a 'Not Ok' row for the same ID:

declare @t table(ID int
                ,Number int
                ,Condition1 int
                ,Condition2 int
                ,Condition3 int
                ,Condition4 int
                );
insert into @t values
 (1,1,1,2,1,1)
,(1,2,2,1,2,2)
,(2,5,2,2,2,1)
,(2,6,2,2,2,2)
,(3,7,1,1,2,1)
,(3,8,2,1,1,2)
,(3,3,2,1,2,2)
,(4,9,2,1,1,1)
,(4,4,1,1,1,2)
,(5,10,2,1,2,1)
,(5,13,2,1,2,2);


with cte as
(
    select row_number() over (order by ID
                                    ,Condition1
                                    ,Condition2
                                    ,Condition3
                                    ,Condition4
                            ) as rn
            ,ID
            ,Number
            ,Condition1
            ,Condition2
            ,Condition3
            ,Condition4
            ,case when Condition1 = 1 then 1
                else case when Condition2 = 1 then 1
                    else case when Condition3 = 1 then 1
                        else case when Condition4 = 1 then 1
                            else 2
                            end
                        end
                    end
                end as IsOk
    from @t
)
select c1.ID
        ,c1.Number
        ,c1.Condition1
        ,c1.Condition2
        ,c1.Condition3
        ,c1.Condition4
        ,case when isnull(c2.IsOk,0) = 1 then 2 else c1.IsOk end as IsOk
from cte c1
    left join cte c2
        on(c1.ID = c2.ID
            and c1.rn = c2.rn+1
            )
order by c1.rn;

Upvotes: 2

Related Questions