x

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.

more ▼

asked Sep 12 at 11:29 AM in Default

avatar image

SpineX
1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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

more ▼

answered Sep 14 at 01:26 PM

avatar image

Oleg
18.9k 3 7 28

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.

Sep 14 at 04:11 PM SpineX

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.

Sep 14 at 04:12 PM SpineX

@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.

Sep 14 at 05:38 PM Oleg

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 :)

Sep 14 at 10:30 PM KenJ

@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.

Sep 15 at 01:27 PM Oleg
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x74
x27

asked: Sep 12 at 11:29 AM

Seen: 43 times

Last Updated: Sep 15 at 01:27 PM

Copyright 2017 Redgate Software. Privacy Policy