title: How to express a range over multiple columns with hierarchic relation?
body: <p>I'm porting an old accounting software to SQL. Here's a sample made up chart of accounts:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Account</th>
  <th>SubAcct</th>
  <th>SubSubAcct</th>
  <th>SubSubSubAcct</th>
  <th>AccountNumber</th>
  <th>Name</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1110</td>
  <td>0</td>
  <td>0</td>
  <td>0</td>
  <td>1110</td>
  <td>Banks</td>
  </tr>
  <tr>
  <td>1110</td>
  <td>1</td>
  <td>0</td>
  <td>0</td>
  <td>1110-1</td>
  <td>US Banks</td>
  </tr>
  <tr>
  <td>1110</td>
  <td>1</td>
  <td>1</td>
  <td>0</td>
  <td>1110-1-1</td>
  <td>Bank One</td>
  </tr>
  <tr>
  <td>1110</td>
  <td>1</td>
  <td>1</td>
  <td>1</td>
  <td>1110-1-1-1</td>
  <td>Bank One #123456</td>
  </tr>
  <tr>
  <td>1110</td>
  <td>1</td>
  <td>1</td>
  <td>2</td>
  <td>1110-1-1-2</td>
  <td>Bank One #234567</td>
  </tr>
  <tr>
  <td>1110</td>
  <td>1</td>
  <td>1</td>
  <td>11</td>
  <td>1110-1-1-11</td>
  <td>Bank One #11223344</td>
  </tr>
  <tr>
  <td>1110</td>
  <td>1</td>
  <td>2</td>
  <td>0</td>
  <td>1110-1-2-0</td>
  <td>Bank Two</td>
  </tr>
  <tr>
  <td>1110</td>
  <td>1</td>
  <td>2</td>
  <td>1</td>
  <td>1110-1-2-1</td>
  <td>Bank Two #876543</td>
  </tr>
  <tr>
  <td>1110</td>
  <td>2</td>
  <td>0</td>
  <td>0</td>
  <td>1110-2</td>
  <td>Foreign Banks</td>
  </tr>
  <tr>
  <td>1110</td>
  <td>2</td>
  <td>1</td>
  <td>0</td>
  <td>1110-2-1</td>
  <td>Japan One #556677</td>
  </tr>
  <tr>
  <td>1120</td>
  <td>0</td>
  <td>0</td>
  <td>0</td>
  <td>1120</td>
  <td>Receivables</td>
  </tr>
  <tr>
  <td>1120</td>
  <td>1</td>
  <td>0</td>
  <td>0</td>
  <td>1120-1</td>
  <td>US Receivables</td>
  </tr>
  <tr>
  <td>1120</td>
  <td>1</td>
  <td>1</td>
  <td>0</td>
  <td>1120-1-1</td>
  <td>Zone One</td>
  </tr>
  <tr>
  <td>1120</td>
  <td>1</td>
  <td>1</td>
  <td>1</td>
  <td>1120-1-1-1</td>
  <td>Customer AAA</td>
  </tr>
  <tr>
  <td>1120</td>
  <td>1</td>
  <td>1</td>
  <td>2</td>
  <td>1120-1-1-2</td>
  <td>Customer BBB</td>
  </tr>
  <tr>
  <td>1120</td>
  <td>1</td>
  <td>1</td>
  <td>3</td>
  <td>1120-1-1-3</td>
  <td>Customer CCC</td>
  </tr>
  <tr>
  <td>1120</td>
  <td>1</td>
  <td>2</td>
  <td>0</td>
  <td>1120-1-2-0</td>
  <td>Zone Two</td>
  </tr>
  <tr>
  <td>1120</td>
  <td>1</td>
  <td>2</td>
  <td>1</td>
  <td>1120-1-2-1</td>
  <td>Customer WWW</td>
  </tr>
  <tr>
  <td>1120</td>
  <td>1</td>
  <td>2</td>
  <td>2</td>
  <td>1120-1-2-2</td>
  <td>Customer YYY</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I need to query any range of accounts, for example, from account number 1110-1-1-2 to account number 1120-1-2.</p>
  <p>This works:</p>
  <pre><code>SELECT * FROM Accounts 
  WHERE FORMAT(Account,'D8')+'-'+
        FORMAT(SubAcct,'D8')+'-'+
        FORMAT(SubSubAcct,'D8')+'-'+
        FORMAT(SubSubSubAcct,'D8') 
     BETWEEN '00001110-00000001-00000001-00000002' 
     AND     '00001120-00000001-00000002-00000000'
  ORDER BY Account,SubAcct,SubSubAcct,SubSubSubAcct
  </code></pre>
  <p>But I don't think it's a good way to do it. Here's a <a href="http://sqlfiddle.com/#!18/51a6c/3" rel="nofollow noreferrer">SQLFiddle</a> with sample schema and data.</p>
  <p>I'll appreciate any ideas on how to express the query or for a better table definition.</p>