Better Way 1

[Signit]

While the below code works I cannot help but think there is a cleaner way of doing this. The below query would eventually result in @represented_org being set to 10800. As I said this code works for orgs 00001 through xxxxx, but I just feel like it could be tightened up. Any help would be greatly appreciated.

declare @rowcount        int,
        @count           int,
        @assigned_org    char(8),
        @represented_org char(8)

set @assigned_org = '108271'

select @count = len(@assigned_org)

while 1=1
  begin      
    select @rowcount = count(*)
      from csr
     where represented_org like substring(@assigned_org,1,@count)+'%'
    
    if @rowcount > 0
      break
    
    if @count > 1
      select @count = @count - 1
    else
       break
  end

select @represented_org = a.represented_org
  from csr a
 where a.represented_org like substring(@assigned_org,1,@count)+'%'

select @represented_org

 

[vongrunt]

assigned_org is left-aligned string and therefore sortable. Can you use something like (not checked):

select @represented_org = max(represented_org)
from csr
where represented_org < @assigned_org
-- or represented_org like @assigned_org + '%'

if left(@represented_org, 1) <> left(@assigned_org, 1) set @represented_org=''

?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Signit]

At first brush vongrunt your code worked flawlessly but as I began to test it I discovered some interesting ways in which people are storing data for represented_org. Someone assigned to org 04343 should have a represented_org of 04300, but because someone has assigned a represented_org of 04317 for a specific purppose your code returns 04317. My code returns the appropriate represented_org of 04300 for people in 04343. I am not quite sure how to modify your code to make it work to fix this instance.