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

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.

Answer 2

One alternative is to enumerate the rows and then use that enumeration:

with enumerated as (
      select a.*,
             row_number() over (order by Account, SubAcct, SubSubAcct, SubSubSubAcct) as seqnum
      from accounts a
     )
select e.*
from (select e.*,
             max(case when account = 1110 and subacct = 1 and subsubacct = 1 and subsubsubacct = 2 then seqnum end) as seqnum_1,
             max(case when account = 1120 and subacct = 1 and subsubacct = 2 then seqnum end) as seqnum_2           
      from enumerated e
     ) e
where seqnum between seqnum_1 and seqnum_2;

If you have a "row number" column that is in the same ordering as seqnum, then you don't need the CTE.

EDIT:

You can easily tweak this by throwing in the accounts you are looking for. The following version also adds a flag indicating if the rows in enumerated are the lower boundary, the original data, or the upper boundary.

with enumerated as (
      select e.*,
             row_number() over (order by Account, SubAcct, SubSubAcct, SubSubSubAcct) as seqnum

      from ((select account, subacct, subsubacct, subsubsubacct, 0 as ord
             from accounts a
            ) union all
            select 1110, 1, 1, 2, -1
            union all
            select 1120, 1, 2, -1, 1
           ) e
     )
select e.*
from (select e.*,
             max(case when ord = -1 then seqnum end) as seqnum_1,
             max(case when ord = 1 then seqnum end) as seqnum_2           
      from enumerated e
     ) e
where seqnum between seqnum_1 and seqnum_2 and
      ord = 0;

This uses -1 for missing values, which I think is the intention (that no value for a component is "before" all other values).

Answer 3

The best solution is to write a User Defined Function that takes 12 parameters and returns TRUE or FALSE. That will make your application code significantly more readable, less brittle, will centralize application code, simplify queries, and even isolate the schema from your application code (specially tuple functions, which IMO are underutilized in the field).

You can write a UDF in almost any language, including SQL, but here is how it would be done in postgresql. Depending on your dbms you can name your parameters.

CREATE FUNCTION Between_Accounts(int, int, int, int, 
                                 int, int, int, int, 
                                 int, int, int, int) RETURNS bool LANGUAGE <whateverLang> $$
  . write your comparison function ... return true/false
$$

for instance, in the code above you could have basically the same logic you already have, or any one from a solution you have received. Or implement it in pl/SQL (or similar languages) and make it easier to read.

then can call the function in your where clause:

SELECT * FROM Accounts 
WHERE BetweenAccounts(Account,SubAcct, SubSubAcct, SubSubSubAcct,
            Acc1, SubAcc1, SubSubAcc1, SubSubSubAcc1,
            Acc2, SubAcc2, SubSubAcc2, SubSubSubAcc2)
ORDER BY Account,SubAcct,SubSubAcct,SubSubSubAcct

You can also write a function that returns a set of tuples. The application code does not even need to know the name of the table. For instance the following function only takes the between accounts:

CREATE FUNCTION Tuples_Between_Accounts( 
                                 int, int, int, int, 
                                 int, int, int, int) 
RETURNS Accounts --schema of the tuples returned
LANGUAGE sql
  $$
  -- write all your logic here and return the tuples ordered by... 
  -- you can reuse any of the SQL solutions given here...
  -- of course the strings below are hardcoded, they will
  -- need to be written in terms of parameters $1 to $8
    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

$$
    

Then all you have to do is:

SELECT * FROM
 Tuples_BetweenAccounts(
            Acc1, SubAcc1, SubSubAcc1, SubSubSubAcc1,
            Acc2, SubAcc2, SubSubAcc2, SubSubSubAcc2);

using UDFs will make your application code less brittle and more maintainable, since you only have the logic of how to find tuples between accounts inside the DBMS.

Answer 4

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 5

I would create a computed column and an index upon it.

A caveat, though: since FORMAT is non-deterministic, the computation shall not be FORMAT(..., 'D8')

-- FORMAT is non-deterministic, hence, not allowing INDEXes
-- Used RIGHT, which is deterministic

ALTER TABLE Accounts
ADD AccountNumberNormalized
AS
    RIGHT('00000000' + CONVERT(VARCHAR, Account),       8) + '-' +
    RIGHT('00000000' + CONVERT(VARCHAR, SubAcct),       8) + '-' +
    RIGHT('00000000' + CONVERT(VARCHAR, SubSubAcct),    8) + '-' +
    RIGHT('00000000' + CONVERT(VARCHAR, SubSubSubAcct), 8);

CREATE INDEX AK_Accounts_Normalized
ON Accounts(AccountNumberNormalized);

Then, the query would be as simple as

SELECT * FROM Accounts 
WHERE
   AccountNumberNormalized
       BETWEEN '00001110-00000001-00000001-00000002' 
       AND     '00001120-00000001-00000002-00000000'
ORDER BY Account,SubAcct,SubSubAcct,SubSubSubAcct

The resulting fiddle is here: http://sqlfiddle.com/#!18/bc2b3/1

Answer 6

The data you describe is very similar to that of PeopleSoft trees (https://docs.oracle.com/cd/E24150_01/pt851h2/eng/psbooks/ttrm/chapter.htm?File=ttrm/htm/ttrm03.htm) however your data is not stored in a truly hierarchical manner, which inhibits effective access. Your SubAcct values lose meaning, for example, because the same value in it spans multiple Account column values. That value should be concatenated with the parent. This is because it is in itself a node. Same with everything except for SubSubSubAcct for which you'll never have more than one within the same node, so the values there don't matter. In other words, you must have unique values for every node. Otherwise it breaks, and you are in the mess you are in now.

That being said, your means of access will also have to change. Presuming you never need to target certain but not all leaves in a node, you can change your where clause condition such that it focuses on the nodes (after you transform the data).

I question whether you really need to query for "any range of accounts" but instead some range of nodes. In other words, of banks 1 through 5, do you want all the accounts of banks 2 through 4, or only some of banks 2, and all of banks 3 and 4? I am not sure I understand how the order of the leaves could be important for such a partial grabbing of a node (your requirement suggests there might be, because you want accounts between). Some context there would be helpful.

In any case, I would transform this data before making any attempt to query it.

Answer 7

The key requirement: "I need to query any range of accounts" regardless of whether or not either "account number" in the range endpoint(s) actually exists. The first piece of code needed is a function to reliably parse the components of the endpoints of the range. In this case the function relies on an ordinal splitter called dbo.DelimitedSplit8K_LEAD (explained here)

DelimitedSplit8K_LEAD

CREATE FUNCTION [dbo].[DelimitedSplit8K_LEAD]
--===== Define I/O parameters
        (@pString VARCHAR(8000), @pDelimiter CHAR(1))
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
  WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (--==== This provides the "zero base" and limits the number of rows right up front
                     -- for both a performance gain and prevention of accidental "overruns"
                 SELECT 0 UNION ALL
                 SELECT TOP (DATALENGTH(ISNULL(@pString,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                 SELECT t.N+1
                   FROM cteTally t
                  WHERE (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0) 
                )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY s.N1),
        Item = SUBSTRING(@pString,s.N1,ISNULL(NULLIF((LEAD(s.N1,1,1) OVER (ORDER BY s.N1) - 1),0)-s.N1,8000))
   FROM cteStart s
;

Function to split endpoint "account number"

create function dbo.test_fnAccountParts(
    @acct            varchar(12))
returns table with schemabinding as return 
select max(case when dl.ItemNumber=1 then Item else 0 end) a,
       max(case when dl.ItemNumber=2 then Item else 0 end) sa,
       max(case when dl.ItemNumber=3 then Item else 0 end) ssa,
       max(case when dl.ItemNumber=4 then Item else 0 end) sssa
from dbo.DelimitedSplit8K_LEAD(@acct,'-') dl;

Query to locate rows based on sequence number

declare
  @acct1            varchar(12)='1110-1-1-2',
  @acct2            varchar(12)='1120-1-2';

with
rn_cte as (
      select a.*, row_number() over (order by Account, SubAcct, SubSubAcct, SubSubSubAcct) rn
      from #accounts a),
rn1_cte as (select max(rn) max_rn 
            from rn_cte r
                 cross apply dbo.test_fnAccountParts(@acct1) ap
            where r.Account<=ap.a
                  and r.SubAcct<=ap.sa
                  and r.SubSubAcct<=ap.ssa
                  and r.SubSubSubAcct<=ap.sssa),
rn2_cte as (select max(rn) max_rn 
            from rn_cte r
                 cross apply dbo.test_fnAccountParts(@acct2) ap
            where r.Account<=ap.a
                  and r.SubAcct<=ap.sa
                  and r.SubSubAcct<=ap.ssa
                  and r.SubSubSubAcct<=ap.sssa)
select rn.*
from rn_cte rn
     cross join rn1_cte r1
     cross join rn2_cte r2
where rn.rn between r1.max_rn
                and r2.max_rn;

Account SubAcct SubSubAcct  SubSubSubAcct   AccountNumber   Name                rn
1110    1       1           2               1110-1-1-2  Bank One #234567        5
1110    1       1           11              1110-1-1-11 Bank One #11223344      6
1110    1       2           0               1110-1-2-0  Bank Two                7
1110    1       2           1               1110-1-2-1-1    Bank Two #876543    8
1110    2       0           0               1110-2      Foreign Banks           9
1110    2       1           0               1110-2-1    Japan One #556677       10
1120    0       0           0               1120        Receivables             11
1120    1       0           0               1120-1      US Receivables          12
1120    1       1           0               1120-1-1    Zone One                13
1120    1       1           1               1120-1-1-1  Customer AAA            14
1120    1       1           2               1120-1-1-2  Customer BBB            15
1120    1       1           3               1120-1-1-3  Customer CCC            16
1120    1       2           0               1120-1-2-0  Zone Two                17

Suppose you were to add an indexed computed column (as suggested in Marcus Vinicius Pompeu's answer) called AccountNumberNormalized. It's a good suggestion. Then you would need a function to return the normalized account number of the end points. Something like this

drop function if exists dbo.test_fnAccountNumberNormalized;
go
create function dbo.test_fnAccountNumberNormalized(
    @acct            varchar(12))
returns table with schemabinding as return 
select concat_ws('-', RIGHT('00000000' + CONVERT(VARCHAR, (max(case when dl.ItemNumber=1 then Item else 0 end)) ), 8),
                      RIGHT('00000000' + CONVERT(VARCHAR, (max(case when dl.ItemNumber=2 then Item else 0 end)) ), 8),
                      RIGHT('00000000' + CONVERT(VARCHAR, (max(case when dl.ItemNumber=3 then Item else 0 end)) ), 8),
                      RIGHT('00000000' + CONVERT(VARCHAR, (max(case when dl.ItemNumber=4 then Item else 0 end)) ), 8)) 
                      AccountNumberNormalized
from dbo.DelimitedSplit8K_LEAD(@acct,'-') dl;

Then this query returns the same results (13 rows) as above

declare
  @acct1            varchar(12)='1110-1-1-2',
  @acct2            varchar(12)='1120-1-2';

SELECT a.* 
FROM #Accounts a
     cross apply dbo.test_fnAccountNumberNormalized(@acct1) fn1
     cross apply dbo.test_fnAccountNumberNormalized(@acct2) fn2
WHERE
   a.AccountNumberNormalized
       BETWEEN fn1.AccountNumberNormalized
       AND     fn2.AccountNumberNormalized
ORDER BY Account,SubAcct,SubSubAcct,SubSubSubAcct;

These are inline table valued functions. If you're using SQL 2019 (or compatability level 150) you maybe could change these into inline scalar functions.

[Edit] Here's a scalar function which returns CHAR(35). It definitely cleans up the code. Performance wise it would depend on specific circumstance and would need to be tested. This query returns the same results (13 rows) as above.

create function dbo.test_scalar_fnAccountNumberNormalized(
    @acct            varchar(12))
returns char(35) as 
begin
return (
select concat_ws('-', RIGHT('00000000' + CONVERT(VARCHAR, (max(case when dl.ItemNumber=1 then Item else 0 end)) ), 8),
                      RIGHT('00000000' + CONVERT(VARCHAR, (max(case when dl.ItemNumber=2 then Item else 0 end)) ), 8),
                      RIGHT('00000000' + CONVERT(VARCHAR, (max(case when dl.ItemNumber=3 then Item else 0 end)) ), 8),
                      RIGHT('00000000' + CONVERT(VARCHAR, (max(case when dl.ItemNumber=4 then Item else 0 end)) ), 8)) 
                      AccountNumberNormalized
from dbo.DelimitedSplit8K_LEAD(@acct,'-') dl);
end

declare
  @acct1            varchar(12)='1110-1-1-2',
  @acct2            varchar(12)='1120-1-2';

SELECT a.* 
FROM #Accounts a
WHERE
   a.AccountNumberNormalized
       BETWEEN dbo.test_scalar_fnAccountNumberNormalized(@acct1) 
       AND     dbo.test_scalar_fnAccountNumberNormalized(@acct2) 
ORDER BY Account,SubAcct,SubSubAcct,SubSubSubAcct;

Answer 8

Why don't you just transform it into a big number and compare it? Is way faster than any string calculation.

SELECT 
  (Account * 1000 + SubAcct *100 + SubSubAcct*10 + SubSubSubAcct) as full_Account
FROM Accounts 
WHERE (Account * 1000 + SubAcct *100 + SubSubAcct*10 + SubSubSubAcct) 
       between 1110112 and 1120120
ORDER BY Account,SubAcct,SubSubAcct,SubSubSubAcct

Answer 9

Ints speedier than char and there's tonnes of good functions for ints to expand as you get deeper.

Just make sure there's no collisions .. max length of account,subact etc would be the #0's you place behind the native key.

SQL only - no need to be fancy here. Fast, expandable, easy to document.

SELECT  
*  
FROM 
 Accounts 
WHERE
  SubSubSubAcct  +
  SubSubAcct*  100000000 +
  SubAcct   *  10000000000000000 +
  Account   *  1000000000000000000000000   
BETWEEN  
  1110 *1000000000000000000000000 + --Account
  1    *10000000000000000 +         --SubAcct
  1    *100000000 +                 --SubSubAcct
  2                                 --SubSubSubAcct
and 
  1120 *1000000000000000000000000 + --Account
  1    *10000000000000000 +         --SubAcct
  2    *100000000 +                 --SubSubAcct
  0                                 --SubSubSubAcct

SQL FIDDLE

Answer 10

Redundant data is a major problem for the old accounting table definition. for example, it has SubAcct, SubSubAcct, SubSubSubAcct, and maybe Sub...Acct columns. I believe this table doesn't observe the normalization rule.

If you want to create a better table definition, so I can suppose you define 3 columns instead of 6 columns, as you can manage more sub-accounts instead of 3 sub-accounts.

CREATE TABLE [dbo].[Accounts](
    [AccountID] [int] NOT NULL,
    [ParentAccountID] [int] NULL,
    [Name] [VARCHAR](100) NOT NULL,
 CONSTRAINT [PK_Accounts] PRIMARY KEY CLUSTERED 
(
    [AccountID] ASC
),
  CONSTRAINT FK_ParentAccount FOREIGN KEY (ParentAccountID)
    REFERENCES Accounts(AccountID)
);

I changed your structure and values for better maintenance with a recursive relationship.

INSERT INTO Accounts
    ([AccountID], [ParentAccountID], [Name])
VALUES
    (1110,null, 'Banks'),
    (11101,1110, 'US Banks'),
    (111011,11101, 'Bank One'),
    (1110111,111011, 'Bank One #123456'),
    (1110112,111011, 'Bank One #234567'),
    (11101111,1110111 , 'Bank One #11223344'),
    (1110120, 1110112, 'Bank Two'),
    (1110121, 1110112, 'Bank Two #876543'),
    (11101211, 1110121, 'Bank Two #876543')
;

and by this query, you can find 'Level', 'Path', 'Root'

also, you can filter it by 'between' syntax

WITH    CTE_TreeAccounts
              AS ( SELECT   ParentAccountID ,
                            Name ,
                            Name AS FullPathName ,
                            CAST(AccountID AS VARCHAR(100)) AS FullPathID ,
                            0 AS lvl ,
                            AccountID,
                            AccountID AS rootid
                   FROM     Accounts
                   WHERE    ParentAccountID IS NULL
                   UNION ALL
                   SELECT   ac.ParentAccountID  ,
                            ac.Name AS name ,
                            CAST(CONCAT(ISNULL(actree.FullPathName, ''), ' / ',
                                        ac.Name) AS VARCHAR(100)) AS name ,
                            CAST(CONCAT(ISNULL(actree.FullPathID, ''), '-',
                                        ac.AccountID) AS VARCHAR(100)) AS name ,
                            actree.lvl + 1 ,
                            ac.AccountID,
                            actree.rootid 
                   FROM     Accounts AS ac
                            INNER JOIN CTE_TreeAccounts actree ON actree.AccountID = ac.ParentAccountID
                 )
Select * from CTE_TreeAccounts

Here's a SQLFiddle with sample schema and data

Answer 11

I would create a new column named "AccountNumberRange" and fill it up exactly as you did with the FORMAT.

update Accounts set AccountNumberRange = FORMAT(Account,'D8')+'-'+
      FORMAT(SubAcct,'D8')+'-'+
      FORMAT(SubSubAcct,'D8')+'-'+
      FORMAT(SubSubSubAcct,'D8');

After that I would specify a default value for this column, to keep it updated.

Doing that, you would index this column and speed up your query.

Best, Júlio