How to express a range over multiple columns with hierarchic relation?

Question

I'm porting an old accounting software to SQL. Here's a sample made up chart of accounts:

Account	SubAcct	SubSubAcct	SubSubSubAcct	AccountNumber	Name
1110	0	0	0	1110	Banks
1110	1	0	0	1110-1	US Banks
1110	1	1	0	1110-1-1	Bank One
1110	1	1	1	1110-1-1-1	Bank One #123456
1110	1	1	2	1110-1-1-2	Bank One #234567
1110	1	1	11	1110-1-1-11	Bank One #11223344
1110	1	2	0	1110-1-2-0	Bank Two
1110	1	2	1	1110-1-2-1	Bank Two #876543
1110	2	0	0	1110-2	Foreign Banks
1110	2	1	0	1110-2-1	Japan One #556677
1120	0	0	0	1120	Receivables
1120	1	0	0	1120-1	US Receivables
1120	1	1	0	1120-1-1	Zone One
1120	1	1	1	1120-1-1-1	Customer AAA
1120	1	1	2	1120-1-1-2	Customer BBB
1120	1	1	3	1120-1-1-3	Customer CCC
1120	1	2	0	1120-1-2-0	Zone Two
1120	1	2	1	1120-1-2-1	Customer WWW
1120	1	2	2	1120-1-2-2	Customer YYY

I need to query any range of accounts, for example, from account number 1110-1-1-2 to account number 1120-1-2.

This works:

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

But I don't think it's a good way to do it. Here's a SQLFiddle with sample schema and data.

I'll appreciate any ideas on how to express the query or for a better table definition.

Answer 1

After looking at the structure of AccountNumber, it dawned on me that there is another fun option.

We can add a **persisted** column called HierID which converts your AccountNumber into a HierarchyID data type. Then we can take advantage of HierID.IsDescendantOf or even apply your range

You can alter your table as such or take a peek at the dbFiddle

Alter Table Accounts add [HierID] as convert(hierarchyid,'/'+replace(AccountNumber,'-','/')+'/')  PERSISTED;

Note: Creating an Index is optional, but highly suggested.

---

Now, let's say for example I wanted everything between 1110-1-1 Bank One and 1120 Receivables (including descendants) The query would look like this:

Declare @R1 varchar(50) = '1110-1-1'
Declare @R2 varchar(50) = '1120'

Select * 
  from Accounts
  Where HierID between convert(hierarchyid,'/'+replace(@R1,'-','/')+'/')
                   and convert(hierarchyid,'/'+replace(@R2+'-99999','-','/')+'/')

Results

Now, let's say I wanted the descendants of 1110-1 US Banks, the query would look like this:

 Declare @S varchar(50) = '1110-1'

 Select * 
  From Accounts
  Where HierID.IsDescendantOf( convert(hierarchyid,'/'+replace(@S,'-','/')+'/') ) = 1

Results

Answer 2

Just for completeness, here is one simple approach. The performace should be better than what you have now.

SELECT * 
FROM Accounts
WHERE 
(
  account > 1110 OR
  account = 1110 AND subacct > 1 OR
  account = 1110 AND subacct = 1 AND subsubacct > 1 OR
  account = 1110 AND subacct = 1 AND subsubacct = 1 AND subsubsubacct >= 2  
) AND (
  account < 1120 OR
  account = 1120 AND subacct < 1 OR
  account = 1120 AND subacct = 1 AND subsubacct < 2 OR
  account = 1120 AND subacct = 1 AND subsubacct = 2 AND subsubsubacct <= 0
)

You can add account BETWEEN 1110 AND 1120 to the condition if the optimizer fails to find appropriate range scan.