1

I have two tables like this:

Runners:  
Name|Date    |FromDist|ToDist|RaceN  
Al  |1/1/2017|1       |9     |axe  
CC  |1/1/2017|11      |12    |axe   
Bm  |2/1/2017|6       |2     |Charity  



RaceStandards:  
RaceN  |Date    |FromDist|ToDist|Direction|Rank  
axe    |1/1/2017|1       |10    |+        |1
Charity|2/1/2017|10      |4     |-        |2

The race standards table gives the direction and distance ranges for each race. The runners table gives the distances ran by each runner.
Question/Issue/What I want to do:
1. Get the direction
2. If its +, range should be >=FromDist and <=ToDist; If its -, range should be <=FromDist and >=ToDist
3. Compare the distance range of Runners FromDist and ToDist based on steps 1 and 2. Assign the Rank number of that race if they fall in range, otherwise assign 0
4. If Runner is out of Range the value can be zero

So the end result would be something like this:

Runners:  
    Name|Date    |FromDist|ToDist|RaceN   |Rank  
    Al  |1/1/2017|1       |9     |axe     |1
    CC  |1/1/2017|11      |12    |axe     |0
    Bm  |2/1/2017|6       |2     |Charity |2  

There would be hundreds of rows in each table so I would want it dynamic. There could be overlaps and stuff but I just need a headstart for now, once I get a base code I can try to expand from there. I would appreciate any help. I tried to give as much detail, if more is required please ask.

1

If I understand correctly, this should give you what you're looking for.

SELECT R.*, COALESCE(RS.Rank, 0) AS Rank
FROM Runners AS R
  LEFT OUTER JOIN RaceStandards AS RS
    ON R.RaceN = RS.RaceN
    AND (
      (RS.Direction = '+' AND R.FromDist >= RS.FromDist AND R.ToDist <= RS.ToDist)
      OR (RS.Direction = '-' AND R.FromDist <= RS.FromDist AND R.ToDist >= RS.ToDist)
    )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.