SQL recursive method to add in auto increment

Question

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

• The 1st table is the user's table which stores user info.
• The 2nd table is a transaction table which 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 trying to achieve is, I will give points to the users who "post msg" and ALL HIS respective referrers. For each and every of the referrer will get the points less than the previous one.

For example: --user 5 > 77 > 80

If user 80 earned 5 points,
user 77 will earn 4 points and
user 5 will earn 3 points

I did the recursive output of my desired result but I can't make it known that the referrer is linked to their child.

The points assign to each and every TIER's referrer and the immediate person is maintained by another table. That table has the key of loyalprogid, loyalruleid and rulelevel, which to know what points to assign to the referrer for points that earned 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 query:

;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

the desired output -->

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 actually get the points from joining the nl_loyalruledet to the result set.

I can't get the rulelevel right, because of its recursive method I think.

Answer 1

Try this below.. I have used dateran column for ordering.

For complexity, I have inserted one more record in nl_loyaltrans.

insert into nl_loyaltrans 
values ('2017052300004', 77, GETDATE(), 'SOCINVEST', 'POSTMSG', 'E', 'Earned Points', 6)

Now hit the following query

;WITH parents
AS (
    SELECT u.userid
        ,u.referrer
        ,lt.loyalprogid
        ,lt.loyalruleid
        ,cast(1 AS INT) AS rulelevel
        ,lt.datetran
        ,LT.points
    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))
        , P.rulelevel +1
        ,P.datetran
        ,p.points-1
    FROM nl_users c
    INNER JOIN parents p ON p.referrer = c.userid -- this is the recursion
    )
SELECT userid,referrer,loyalprogid,loyalruleid,rulelevel,points
FROM parents
ORDER BY datetran , rulelevel

Results:

+--------+----------+-------------+-------------+-----------+--------+
| 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 |
|     77 |        5 | SOCINVEST   | POSTMSG     |         1 |      6 |
|      5 |        0 | SOCINVEST   | POSTMSG     |         2 |      5 |
+--------+----------+-------------+-------------+-----------+--------+