CODEWITHSUNDEEP
joinleft-joinpartitionrow-number
user27265009
user27265009

Reputation: 1

LEFT JOIN on ROW_NUMBER OVER PARTITION

I have two tables : 1.Actions : Contains multiple actions performed on orderIDs on specific dates. For each orderID, there are multiple rows, each representing a different action performed on a particular date. 2. Logs : Logs orderIDs as "in stock" with specific dates.

Here is the structure of my actions table :

orderID1 | 2024-09-06 | action3

orderID1 | 2024-09-06 | action2.5

orderID1 | 2024-09-05 | action2

orderID1 | 2024-09-05 | action1.5

orderID1 | 2024-09-04 | action1

orderID2 | 2024-09-08 | action23

orderID2 | 2024-09-08 | action22

orderID2 | 2024-09-07 | action21

And here is the structure of my logs table :

orderID1 | 2024-09-06 | ...

orderID1 | 2024-09-05 | ...

orderID1 | 2024-09-04 | ...

orderID2 | 2024-09-08 | ...

orderID2 | 2024-09-07 | ...

I would like to have - for each orderID and date of the Logs table - the most recent action performed on the same orderID on or before that date.

So to say I would like to get the following result

orderID1 | 2024-09-06 | ... | action3

orderID1 | 2024-09-05 | ... | action2

orderID1 | 2024-09-04 | ... | action1

orderID2 | 2024-09-08 | ... | action23

orderID2 | 2024-09-07 | ... | action21

I used the SQL script below with a WINDOW function but it doesn't work. The query fetches the latest action overall for each orderID, instead of fetching the most recent action up to the specific date for each row in the Logs table

WITH latest_actions AS (
    SELECT 
        act.orderID,
        lg.logdate,
        act.actiondate AS last_action_date,
        act.actionname AS action_performed,
        ROW_NUMBER() OVER (PARTITION BY act.orderID, lg.logdate 
                           ORDER BY act.actiondate DESC) AS ranked
    FROM 
        Actions act
    LEFT JOIN 
        Logs lg
    ON 
        CAST(lg.logdate AS INTEGER) = act.orderID
    WHERE 
        act.actiondate <= lg.logdate
)
SELECT 
    lg.orderID,
    lg.logdate,
    lg.orderdate,
    lg.perimeter,
    lg.ordertype,
    la.last_action_date,
    la.action_performed
FROM 
    Logs lg
LEFT JOIN (
    SELECT 
        orderID, 
        logdate, 
        last_action_date, 
        action_performed
    FROM latest_actions
    WHERE ranked = 1
) la
ON 
    CAST(lg.orderID AS INTEGER) = la.orderID
ORDER BY 
    lg.orderID, lg.logdate

Upvotes: 0

Views: 33

Answers (1)

sam
sam

Reputation: 1985

Can you try this:

DROP TABLE IF EXISTS #actions
CREATE TABLE #actions (OrderId varchar(20) NOT NULL, OrderDate Date, ActionName varchar(100))
INSERT INTO #actions (OrderId, OrderDate, ActionName)
    VALUES 
        ('orderID1', '2024-09-06', 'action3')
        , ('orderID1', '2024-09-06', 'action2.5')
        , ('orderID1', '2024-09-05', 'action2')
        , ('orderID1', '2024-09-05', 'action1.5')
        , ('orderID1', '2024-09-04', 'action1')
        , ('orderID2', '2024-09-08', 'action23')
        , ('orderID2', '2024-09-08', 'action22')
        , ('orderID2', '2024-09-07', 'action21')

DROP TABLE IF EXISTS #logs
CREATE TABLE #logs (OrderId varchar(20) NOT NULL, OrderDate Date)
INSERT INTO #logs (OrderId, OrderDate)
    VALUES
         ('orderID1', '2024-09-06')
        , ('orderID1', '2024-09-05')
        , ('orderID1', '2024-09-04')
        , ('orderID2', '2024-09-08')
        , ('orderID2', '2024-09-07')

select * from #actions
select * from #logs

select *
from
    ( select ROW_NUMBER() over (partition by a.orderdate, a.orderid order by a.orderdate, a.orderid) as rownm,  a.* 
    from #logs l
    left join #actions a  on l.OrderId = a.OrderId and l.OrderDate = a.OrderDate ) tbl
where tbl.rownm = 1

Upvotes: 0

Related Questions