Scripting an update based on a field looping

Question

question

Gene asked Feb 10, '20

Scripting an update based on a field looping

MS SQL 2017. I have been given a task to insert a step into an existing SQL job step that will update the value of a field based on a change in another datasbase and table.

The problem is that the second table involved has 1 to N records.

The field "All_docs_satisfied" is to be set to true when all the individual "papers" for the same studentid are marked as satisfied. The amount of papers in table B may vary as shown in the partial dataset provided

Main table AS A

Studentid, ........, All_docs_Received

Dataset of the second table.AS B

STUDENTID TRAKSET       PAPER              		FLAG	RECEIVED          SATISFIED
001439569	JAH     Second Letter of Recommendation	NULL 00
001560125	MSOM    College Transcript 1		NULL 00
001560125	MSOM    College Transcript 2		NULL 00
001560125	MSOM    College Transcript 3		NULL 00
001560125	MSOM    College Transcript 4		NULL 00
001560125	MSOM    College Transcript 5		NULL 00
001560125	MSOM    College Transcript 6		NULL 00
001560125	MSOM    College Transcript 7		NULL 00
001560125	MSOM    College Transcript 8		NULL 00
001560125	MSOM    Resume				NULL 00
001385279	FR      Official SAT or ACT Scores	NULL 00
001372645		Cover Letter                         2020-02-07 00:00:00.0001   1
001372645		Resume                               2020-02-07 00:00:00.0001   1
001372645	TR      Jacksonville Univ Transcript C  NULL 00
001372645	TR      Otterbein Coll Transcript :  C  NULL 00
001448667	JAH     Roommate Questionnaire          NULL 00


Select a.studentid, b.[SATISFIED]
from [dbo].[Feith_Emas_Master] as A, Emasrtl.[dbo].[EINDVTRK] AS B
where a.all_docs_recieved = '0'
and
while(a.studentid = b.studentid)
   Begin
       Update A
	   Set All_docs_Recieved = 'T'
	   IF (Select b.satisfied is Null)
	      Break
           IF (Select b.satisfied = 0)
	      Break
	   Else 
	      Update A
	      Set All_docs_Recieved = 'T'
	END

update looping

10 |1200

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

Answer 1 · 2020-02-10T08:14:07Z

Kev Riley answered Feb 10, '20

Not sure of your datatypes, but I've assumed that Satisifed is a bit - you may need to tweak the script if not.

So with some sample data

declare @YourTableB table (StudentID varchar(20), Satisfied bit)
	insert into @YourTableB (StudentID, Satisfied) select '001439569',null
	insert into @YourTableB (StudentID, Satisfied) select '001560125',null
	insert into @YourTableB (StudentID, Satisfied) select '001560125',null
	insert into @YourTableB (StudentID, Satisfied) select '001560125',null
	insert into @YourTableB (StudentID, Satisfied) select '001560125',null
	insert into @YourTableB (StudentID, Satisfied) select '001560125',null
	insert into @YourTableB (StudentID, Satisfied) select '001560125',null
	insert into @YourTableB (StudentID, Satisfied) select '001560125',null
	insert into @YourTableB (StudentID, Satisfied) select '001560125',null
	insert into @YourTableB (StudentID, Satisfied) select '001560125',null
	insert into @YourTableB (StudentID, Satisfied) select '001385279',null
	insert into @YourTableB (StudentID, Satisfied) select '001372645',1
	insert into @YourTableB (StudentID, Satisfied) select '001372645',1
	insert into @YourTableB (StudentID, Satisfied) select '001372645',null
	insert into @YourTableB (StudentID, Satisfied) select '001372645',null
	insert into @YourTableB (StudentID, Satisfied) select '001448667',null

you don't need to process the data in a loop, we can simply use windowing functions to determine the number of papers where Satisifed is '1', and the total number of papers, and then derive a value for 'All_docs_Recieved' by comparing the 2 values

select distinct
  StudentID,
  sum(cast(satisfied as tinyint))over(partition by StudentID order by (select null)) as NumberOfSatisfieds,
  sum(1)over(partition by StudentID order by (select null)) as NumberOfPapers,
  case when 
      sum(cast(satisfied as tinyint))over(partition by StudentID order by (select null)) 
      = 
      sum(1)over(partition by StudentID order by (select null)) 
    then 1 else 0 
  end as All_docs_Recieved 
from @YourTableB

gives the result

StudentID            NumberOfSatisfieds NumberOfPapers All_docs_Recieved
-------------------- ------------------ -------------- -----------------
001372645            2                  4              0
001385279            NULL               1              0
001439569            NULL               1              0
001448667            NULL               1              0
001560125            NULL               9              0

(5 rows affected)

10 |1200

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

question