DenStudent DenStudent - 26 days ago 9
SQL Question

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

Answer

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;
Comments