Recursive SQL method to add to auto increment

I am a SQL starter. I have a problem which is quite complicated for me and the story is quite long.

• The first table is the users table that stores information about the user.
• The second table is the transactions table that stores the user's transaction.

create table nl_users
(
    userid      int not null primary key,
    datejoin    datetime not null,
    referrer    int not null,
    pointsbal   float not null default 0
)

create table nl_loyaltrans
(
    loyaltransid        varchar(15) not null primary key,
    userid              int not null,
    datetran            datetime not null,
    loyalprogid         varchar(20) not null,
    loyalruleid         varchar(20) not null,
    trantype            char(1) not null,
    trandescp           varchar(100) null,
    points              int not null,
)

insert into nl_users values (79, GETDATE(), 0, 0)
insert into nl_users values (80, GETDATE(), 77, 0)
insert into nl_users values (77, GETDATE(), 5, 0)
insert into nl_users values (5, GETDATE(), 0, 0)

insert into nl_loyaltrans values ('2017052300001', 79, GETDATE(), 'SOCINVEST', 'POSTMSG', 'E', 'Earned Points', 5)
insert into nl_loyaltrans values ('2017052300003', 80, GETDATE(), 'SOCINVEST', 'POSTMSG', 'E', 'Earned Points', 5)

      

        
        
        
      

    

The operation I am trying to achieve, I will give directions to the users who are "posting" and ALL ITS corresponding referrers . For each of the referrers, the result is less than the previous ones.

For example: --user 5> 77> 80

If user 80 earned 5 points,
user 77 earns 4 points and
user 5 earns 3 points

I have made a recursive output of my desired result, but I cannot tell that the referrer is associated with their child.

Points assigned to each TIER referrer, and the direct person is maintained by another table. In this table there is a key of the logotype, loyalty and rule, which should know which points should be assigned to the referrer for the points received by their child.

create table nl_loyalruledet
(
    loyalprogid     varchar(20) not null,
    loyalruleid     varchar(20) not null,
    rulelevel       int not null,
    methodtype      char(1) not null,
    flat            float not null default 0
)

      

        
        
        
      

    

Here's my request:

;WITH parents
AS (
    SELECT u.userid
        ,u.referrer
        ,lt.loyalprogid
        ,lt.loyalruleid
        ,cast(1 AS INT) AS rulelevel
    FROM nl_loyaltrans lt
    LEFT JOIN nl_users u ON lt.userid = u.userid

    UNION ALL

    SELECT c.userid
        ,c.referrer
        ,CAST(loyalprogid AS VARCHAR(20))
        ,CAST(loyalruleid AS VARCHAR(20))
        ,cast((
                row_number() OVER (
                    ORDER BY c.userid
                    ) + 1
                ) AS INT) AS rulelevel
    FROM nl_users c
    INNER JOIN parents p ON p.referrer = c.userid -- this is the recursion
    )
SELECT *
FROM parents

      

        
        
        
      

    

desired result ->

userid  referrer loyalprogid loyalruleid rulelevel  points
79      0        SOCINVEST   POSTMSG     1          5
80      77       SOCINVEST   POSTMSG     1          5
77      5        SOCINVEST   POSTMSG     2          4
5       0        SOCINVEST   POSTMSG     3          3

      

        
        
        
      

    

I can get points from joining nl_loyalruledet to the result set.

I cannot get the correct level of rules, because of its recursive method I guess.