question

SpineX avatar image
SpineX asked

SQL SP/Function Loop Waterfall

I need to write a dynamic code for the below logic which I am struggling to. Basically, I want to avoid redundancy and clutter and loop the logic below through a function or a stored procedure. insert into @Table (ID, Exclusion) select distinct ID, "CHECK1" from Student where CHECK1 = 1 insert into @Table (ID, Exclusion) select distinct ID, "CHECK2" from Student where CHECK2 = 1 and CHECK1 = 0 insert into @Table (ID, Exclusion) select distinct ID, "CHECK3" from Student where CHECK3 = 1 and CHECK1 = 0 and CHECK2 = 0 insert into @Table (ID, Exclusion) select distinct ID, "CHECK4" from Student where CHECK4 = 1 and CHECK1 = 0 and CHECK2 = 0 and CHECK3 = 0 I have to do this for close to 100 checks. Any insights will be appreciated.
functionsstored procedures
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Oleg avatar image
Oleg answered
It appears that you have a table named Student which has a rather big number of columns which names begin with the word Check. I am not questioning the design, it is what it is, I am just confirming. It also appears that you need to find the records which have only one of those CheckXX columns values set to 1, rest set to 0. If this is the case then there is no reason for any "Waterfall loop" here. If the columns are not nullable and can have value of one or zero then summing up all of them and requesting that such sum is equal to one is enough to select just the records which are needed. If the CheckXX columns are designed as BIT rather than INT then the + operator is not applicable to BIT data type, but you can achieve the same result with ***exclusive bitwise or*** (^). The latter will work with INT values as well considering that they can be only 1 or 0. Here is the script which will insert the records into the table variable at once. This script uses 10 columns but you can add as many as needed to the case statement and also to the where clause: insert into @Table (ID, Exclusion) select distinct /* not sure why distinct is needed, but it was in question definition */ ID, case when Check1 = 1 then 'Check1' when Check2 = 1 then 'Check2' when Check3 = 1 then 'Check3' when Check4 = 1 then 'Check4' when Check5 = 1 then 'Check5' when Check6 = 1 then 'Check6' when Check7 = 1 then 'Check7' when Check8 = 1 then 'Check8' when Check9 = 1 then 'Check9' when Check10 = 1 then 'Check10' else null -- should never happen but the default is always needed end Exclusion from Student where -- select only those records which have a value of just one check -- column value set to 1 and rest of check columns all set to 0 Check1 ^ Check2 ^ Check3 ^ Check4 ^ Check5 ^ Check6 ^ Check7 ^ Check8 ^ Check9 ^ Check10 = 1 If the CheckXX columns are INT data type, not BIT, you can replace the **^** with **+** if you want, but the **^** works for both. ***Edit based on the additional input from OP***: If there could be multiple CheckXX columns set to 1 and the task is to figure out first such column than this could be done by simply removing the WHERE clause, i.e. insert into @Table (ID, Exclusion) select ID, Exclusion from ( select distinct /* not sure why distinct is needed, but it was in question definition */ ID, case when Check1 = 1 then 'Check1' when Check2 = 1 then 'Check2' when Check3 = 1 then 'Check3' when Check4 = 1 then 'Check4' when Check5 = 1 then 'Check5' when Check6 = 1 then 'Check6' when Check7 = 1 then 'Check7' when Check8 = 1 then 'Check8' when Check9 = 1 then 'Check9' when Check10 = 1 then 'Check10' else null -- should never happen but the default is always needed end Exclusion from Student ) ex where Exclusion is not null; Optionally, the where clause may be retained, but modified to use + sign instead of ^ if the CheckXX columns are integers and requiring >= 1 condition instead of = 1, or using the Check1 = 1 or Check2 = 1 etc if the columns are bits. Removing the where clause is easier though, as all it takes is to filter out the rows with NULL for Exclusion afterwards. Hope this helps. Oleg
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hi Oleg, Many thanks for the response. But, one ID can have multiple checks equal to 1, so I need to set the very first check which it meets as 1. I need to select all columns, not only columns which have only one check as 1. I will give you a small example: ID CHECK1 CHECK2 CHECK3 CHECK4 CHECK5 a 1 0 0 1 0 b 1 1 1 1 0 c 0 0 0 1 1 d 0 0 0 0 1 e 1 1 0 1 0 Final output should be a CHECK1 b CHECK1 c CHECK4 d CHECK5 e CHECK1 I hope this example explains better.
0 Likes 0 ·
Hi Oleg, Many thanks for the response. But, one ID can have multiple checks equal to 1, so I need to set the very first check which it meets as 1. I need to select all columns, not only columns which have only one check as 1. I will give you a small example: ID CHECK1 CHECK2 CHECK3 CHECK4 CHECK5 a 1 0 0 1 0 b 1 1 1 1 0 c 0 0 0 1 1 d 0 0 0 0 1 e 1 1 0 1 0 Final output should be a CHECK1 b CHECK1 c CHECK4 d CHECK5 e CHECK1 I hope this example explains better.
0 Likes 0 ·
@SpineX This means that the problem is simpler than I thought. Just remove the ***where clause*** and it should do it, courtesy of the CASE statement which is evaluated left to right and top to bottom. Of course removing the where clause might introduce the rows with NULL for Exclusion column (for those students which have NONE of the CheckXX columns set to 1). To exclude those rows you can run select * from @Table where Exclusion is not null; You can optionally retain where clause and if the CheckXX columns are integers then change the ^ to + and equal sign to greater than or equal, i.e. where Check1 + Check2 + Check3 + Check4 + Check5 /* etc */ >= 1 If the CheckXX columns are not integers but are bits then the + is not suitable for them so you can restate the where clause to read: where Check1 = 1 or Check2 = 1 or Check3 = 1 or Check4 = 1 /* etc */ The CASE statement remains the same.
0 Likes 0 ·
Clever use of the case statement @Oleg. I didn't realize until you spelled it out that it was getting the condition right where all the lower numbered checks had to be 0. Much simpler than I first imagined, says the person in the audience :)
0 Likes 0 ·
@KenJ Thank you. Hats off to the developers of the CASE in T-SQL this way!. This design of the CASE allows sweeping down the harder-to-get-less-probable-to-occur conditions, if the order is not important, but performance is. For this question, without this top down feature of the case the solution would be more difficult for sure. Something like this: UNPIVOT the data, include column made up from CheckXX values cast as integer so they can be used for partitioned by ID numbers, and then, finally, pick the first occurrence per each ID: ;with records as ( select ID, Exclusion, row_number() over (partition by ID order by cast(replace(Exclusion, 'Check', '') as int)) N from Student unpivot (CheckValue for Exclusion in ( [Check1], [Check2], [Check3], [Check4], [Check5], [Check6], [Check7], [Check8], [Check9], [Check10]) ) upt where CheckValue = 1 ) insert into @Table (ID, Exclusion) select ID, Exclusion from records where N = 1; This works just fine but looks more complex than the simple CASE statement.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.