Iterate and assign Query help

Question

Iterate and assign Query help

Ten Centuries

Points: 1223

March 8, 2023 at 3:02 am

Table:SchoolTeacher

TeacherName   Zip    City   State
------------  ----  ------  ------
Sam           12345  NJ      NewYork
Pat           12345  NJ      NewYork
Peter         12588  Spring  MD




School:

SchoolLocationID    Zip    City   State
--------------  -------  -----  -----
105               12345   NV    NewYork
105               12345   NJ    NewYork
106               12345   NJ    NewYork
107               12588   Spring MD


BusinessRule:
1) Match should be done on Zipcodes if it matched then a single teacher assigned to only one schoollocation ID
 and the remaining teacher cannot be assigned to same school locationID.

2) If Zipcodes doesn't match then do City match and if it is matched then assigned teacher to location ID then exclude the matched locationID for next teacher assignment.

Expected output:

TeacherName   Zip    City   State     SchoolLocationID
------------  ----  ------  ------    -------------
Sam           12345  NJ      NewYork   105               
Pat           12345  NJ      NewYork   106
Peter         12588  Spring  MD        107

Thank you for your help in advance !!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Some observations:

You have written a load of text without asking for anything.
This looks like homework. For example, no one would ever key a table on 'first name' in a professional database application. Nor would address be held in such a de-normalised fashion. Why should we do your homework for you?
You have not included setup scripts for the test data (in the form of CREATE TABLE and INSERT statements)
ID stands for Identity Document (eg, a passport). What you want here is Id (Identifier)

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

I'm perfectly happy to help with homework.

Help.

So, where are you stuck? What have you tried that didn't work? Show me your work, and I'll be happy to toss in some pointers.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning