Joining identical skeleton frameworks not returning values-CodePudding

I am needing to do some revenue analysis YoY at the daily level using MYSQL. I don't always have revenue every day, but I need to have a placeholder for everyday so I can do a comprehensive YoY analysis.

I have tried to create a "skeleton" with all possible days that I can join my revenue to. I needed to convert one revenue source (multiple sources) from a monthly to a daily level. In order to do this I employed the same "skeleton" method to get the revenue source to a daily level. However, when I try to join the (2) "skeleton" to the (1) "skeleton" the information doesn't come across. I have simplified the code to just be the "skeleton" trying to join.

(1) "skeleton"

SELECT 
    @rownum := @rownum   1,
    DATE_FORMAT(NOW(),'%Y-%m-01') - INTERVAL 24 MONTH   INTERVAL @rownum DAY AS revenueDay
    FROM
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8) tt1,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) tt2,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) tt3,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 4) tt4,
    (SELECT @rownum:=-1) tt0
    
    WHERE 
    DATE_FORMAT(NOW(),'%Y-%m-01') - INTERVAL 24 MONTH   INTERVAL @rownum DAY < (DATE_FORMAT(NOW(),'%Y-%m-%d') - INTERVAL 1 DAY)

(1) "skeleton" Results:

@rownum := @rownum 1	revenueDay
0	2020-11-01
1	2020-11-02
2	2020-11-03
3	2020-11-04

Attempt to join the (2) "skelton" with (1) "skeleton":

SELECT
skeleton.*,
gotcha.gotcha


FROM(
SELECT 
    @rownum := @rownum   1,
    DATE_FORMAT(NOW(),'%Y-%m-01') - INTERVAL 24 MONTH   INTERVAL @rownum DAY AS revenueDay
    FROM
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8) tt1,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) tt2,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) tt3,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 4) tt4,
    (SELECT @rownum:=-1) tt0
    
    WHERE 
    DATE_FORMAT(NOW(),'%Y-%m-01') - INTERVAL 24 MONTH   INTERVAL @rownum DAY < (DATE_FORMAT(NOW(),'%Y-%m-%d') - INTERVAL 1 DAY)
)
skeleton

LEFT JOIN(
SELECT 
    @rownum := @rownum   1,
    DATE_FORMAT(NOW(),'%Y-%m-01') - INTERVAL 24 MONTH   INTERVAL @rownum DAY AS revenueDay,
    'gotcha' AS gotcha
    
    FROM
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8) tt1,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) tt2,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) tt3,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 4) tt4,
    (SELECT @rownum:=-1) tt0
    
    WHERE 
    DATE_FORMAT(NOW(),'%Y-%m-01') - INTERVAL 24 MONTH   INTERVAL @rownum DAY < (DATE_FORMAT(NOW(),'%Y-%m-%d') - INTERVAL 1 DAY)
)
gotcha ON gotcha.revenueDay = skeleton.revenueDay

Attempt to join the (2) "skelton" with (1) "skeleton" Results:

@rownum := @rownum 1	revenueDay	gotcha
0	2020-11-01	NULL
1	2020-11-02	NULL
2	2020-11-03	NULL
3	2020-11-04	NULL

The "gotcha" column should have been populated the best I can tell, but it comes up NULL. I appreciate the help in advance!

CodePudding user response：

The answer is tricky but quite simple. You need to have a different variable for each query's @rowNum.

SELECT
skeleton.*,
gotcha.gotcha


FROM(
SELECT 
    @rownum := @rownum   1,
    DATE_FORMAT(NOW(),'%Y-%m-01') - INTERVAL 24 MONTH   INTERVAL @rownum DAY AS revenueDay
    FROM
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8) tt1,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) tt2,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) tt3,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 4) tt4,
    (SELECT @rownum:=-1) tt0
    
    WHERE 
    DATE_FORMAT(NOW(),'%Y-%m-01') - INTERVAL 24 MONTH   INTERVAL @rownum DAY < (DATE_FORMAT(NOW(),'%Y-%m-%d') - INTERVAL 1 DAY)
)
skeleton

LEFT JOIN(
SELECT 
    @rownum2 := @rownum2   1,
    DATE_FORMAT(NOW(),'%Y-%m-01') - INTERVAL 24 MONTH   INTERVAL @rownum2 DAY AS revenueDay,
    'gotcha' AS gotcha
    
    FROM
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8) tt1,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) tt2,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) tt3,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 4) tt4,
    (SELECT @rownum2:=-1) tt0
    
    WHERE 
    DATE_FORMAT(NOW(),'%Y-%m-01') - INTERVAL 24 MONTH   INTERVAL @rownum2 DAY < (DATE_FORMAT(NOW(),'%Y-%m-%d') - INTERVAL 1 DAY)
)
gotcha ON gotcha.revenueDay = skeleton.revenueDay