I have three tables with detail structure and data like this :
Table TBL_REFERRAL
ID CREATED_DATE USER_NAME REFERRAL_CODE --------------------------------------------------------- 1 28-08-2020 12:15:40 DINA EM0001 2 28-08-2020 12:19:42 DINA EM0002 3 28-08-2020 12:19:22 LISA EM0003 4 28-08-2020 20:00:09 LISA EM0004 5 29-08-2020 12:00:05 RYAN EM0004
Table TBL_USER
ID USER_NAME UNIQUE_CODE ---------------------------------- 1 DINA UNI0001 3 LISA UNI0002 5 RYAN UNI0003
Table TBL_CHECKOUT
TRANS_ID UNIQUE_CODE CREATED_DATE ITEM TOTAL ------------------------------------------------------------------------ 1112 UNI0001 28-08-2020 12:20:40 Milk 5000 1113 UNI0002 28-08-2020 12:22:22 Shampoo 12000 1114 UNI0002 28-08-2020 20:11:09 Biscuit 5000 1115 UNI0003 29-08-2020 12:02:05 Soap 2000
Because of some conditions, in the process I can’t connect between checkout table and referral. But I want to have a view table that connecting the checkout with referral but I am map that with logic based on created date time between referral and check out. Is it possible to me have the view table like this ? :
REFERRAL_CODE TRANS_ID USER_NAME UNIQUE_CODE REFERRAL_DATE CHECKOUT_DATE ------------------------------------------------------------------------------------------ EM0002 1112 DINA UNI0001 28-08-2020 12:19:42 28-08-2020 12:20:40 EM0003 1113 LISA UNI0002 28-08-2020 12:19:22 28-08-2020 12:22:22 EM0004 1114 LISA UNI0002 28-08-2020 20:00:09 28-08-2020 20:11:09 EM0004 1115 RYAN UNI0003 29-08-2020 12:00:05 29-08-2020 12:02:05
Updated -> I have been try the query like this :
SELECT
a.REFERRAL_CODE, a.USER_NAME, b.UNIQUE_CODE, c.TRANS_ID,
a.CREATED_DATE AS "REFERRAL_DATE", c.CREATED_DATE AS "CHECKOUT_DATE"
FROM
TBL_CHECKOUT c
LEFT JOIN TBL_USER b
ON c.UNIQUE_CODE = b.UNIQUE_CODE
LEFT JOIN TBL_REFERRAL a
ON a.USER_NAME = b.USER_NAME
WHERE
c.CREATED_DATE > a.CREATED_DATE;
The result come is :
REFERRAL_CODE TRANS_ID USER_NAME UNIQUE_CODE REFERRAL_DATE CHECKOUT_DATE ------------------------------------------------------------------------------------------ EM0001 1112 DINA UNI0001 28-08-2020 12:15:40 28-08-2020 12:20:40 EM0002 1112 DINA UNI0001 28-08-2020 12:19:42 28-08-2020 12:20:40 EM0003 1113 LISA UNI0002 28-08-2020 12:19:22 28-08-2020 12:22:22 EM0004 1114 LISA UNI0002 28-08-2020 20:00:09 28-08-2020 20:11:09 EM0004 1115 RYAN UNI0003 29-08-2020 12:00:05 29-08-2020 12:02:05
So, based on my result and expectation there is still missing 1 filtering. In this condition c.CREATED_DATE > a.CREATED_DATE, if a.CREATED_DATE that smaller then c.CREATED_DATE is more than 1. the data show 2 row, it should be just one, get latest a.CREATED_DATE. If i using ROW_NUMBER in logic it will be disturb row EM0003 & 1113 and EM0004 & 1114.
based on my current result, i want to grouping the transaction_id get based on latest REFERRAL_DATE, is it possible ?
Thank you so much for help I really appreciate it
Advertisement
Answer
You can use LEFT JOIN among those three tables, and ROW_NUMBER() analytic function is need to filter out the records among latest CHECKOUT_DATE values :
WITH t AS
(
SELECT r.REFERRAL_CODE, r.USER_NAME, u.UNIQUE_CODE, c.TRANS_ID,
r.CREATED_DATE AS "REFERRAL_DATE", c.CREATED_DATE AS "CHECKOUT_DATE",
ROW_NUMBER() OVER (PARTITION BY r.USER_NAME ORDER BY c.CREATED_DATE DESC) AS rn
FROM TBL_CHECKOUT c
LEFT JOIN TBL_USER u
ON u.UNIQUE_CODE = c.UNIQUE_CODE
LEFT JOIN TBL_REFERRAL r
ON r.USER_NAME = u.USER_NAME
WHERE r.CREATED_DATE < c.CREATED_DATE
)
SELECT REFERRAL_CODE, USER_NAME, UNIQUE_CODE,TRANS_ID,REFERRAL_DATE,CHECKOUT_DATE
FROM t
WHERE rn = 1