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.
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.