I need some help with a complex SQL query. Here's my setup: I have two tables USER_TABLE and USER_VISIT_STAT. USER_TABLE have columns like ID,USER_NAME,UID,DEPARTMENT and USER_VISIT_STAT have columns like ID,USERID,LAST_VISIT_DATE. USERID field of USER_VISIT_STAT table holds the USER_TABLE.ID.
When a user logs in to the application his current date is saved in USER_VISIT_STAT table (refer the screenshot). So I need to get the user details who are not connected to the application since last 2 years. But we have to take care of the UID. (means you can see in USER_TABLE there are two entries with same UID but case is different. In USER_VISIT_STAT u can see 101 LAST_VISIT_DATE is 31-08-18 and 103 LAST_VISIT_DATE is 20-01-16. So you have to ignore both the ids because user is same and he is active). In response table I need user name, user uid, user department and user max(last active date).
+--------------------------------------+
| USER_TABLE |
+--------------------------------------+
| ID | NAME | UID | DEPARTMENT |
+--------------------------------------+
| 101 | PRAKASH | US45 | ENGG |
| 102 | RAJESH | US22 | ENGG |
| 103 | PRAKASH | us45 | HR |
| 104 | HARI | US9 | ENGG |
| 105 | MAYANK | US90 | HR |
+--------------------------------------+
+--------------------------------+
| USER_VISIT_STAT |
+--------------------------------+
| ID | USID | MAX(LAST_VISIT_DATE)|
+--------------------------------+
| 1 | 101 | 31-08-18 |
| 2 | 102 | 30-08-18 |
| 3 | 101 | 30-08-18 |
| 4 | 103 | 20-01-16 |
| 5 | 104 | 29-08-16 |
| 6 | 105 | 19-07-16 |
| 7 | 101 | 12-06-16 |
| 8 | 102 | 12-06-16 |
| 9 | 104 | 13-04-16 |
+--------------------------------+
+-------------------------------------------------+
| RESPONSE |
+-------------------------------------------------+
| NAME | UID | DEPARTMENT | LAST_VISIT_DATE |
+-------------------------------------------------+
| HARI | US9 | ENGG | 29-08-16 |
| MAYANK | US09 | HR | 19-07-16 |
+-------------------------------------------------+
Using an analytic form of the MAX() function, partitioning that by the upper case of the UID will solve for this. Available as a demo here SQL Fiddle
Query 1:
SELECT
M.LAST_VISIT_DATE, U.*
FROM (
SELECT
U."UID"
, V.LAST_VISIT_DATE
, MAX(V.LAST_VISIT_DATE) OVER(PARTITION BY UPPER(U."UID")) MAX_VISIT
FROM USER_TABLE U
INNER JOIN USER_VISIT_STAT V ON U.ID = V.USID
) M
INNER JOIN USER_TABLE U ON M."UID" = U."UID"
WHERE M.MAX_VISIT < ADD_MONTHS(SYSDATE,-24)
AND M.LAST_VISIT_DATE = M.MAX_VISIT
| LAST_VISIT_DATE | ID | NAME | UID | DEPARTMENT |
|-----------------------|-----|--------|------|------------|
| 2016-08-29 00:00:00.0 | 104 | HARI | US9 | ENGG |
| 2016-07-19 00:00:00.0 | 105 | MAYANK | US90 | HR |
Oracle 11g R2 Schema Setup:
CREATE TABLE USER_TABLE
("ID" int, "NAME" varchar2(7), "UID" varchar2(4), "DEPARTMENT" varchar2(4))
;
INSERT ALL
INTO USER_TABLE ("ID", "NAME", "UID", "DEPARTMENT")
VALUES (101, 'PRAKASH', 'US45', 'ENGG')
INTO USER_TABLE ("ID", "NAME", "UID", "DEPARTMENT")
VALUES (102, 'RAJESH', 'US22', 'ENGG')
INTO USER_TABLE ("ID", "NAME", "UID", "DEPARTMENT")
VALUES (103, 'PRAKASH', 'us45', 'HR')
INTO USER_TABLE ("ID", "NAME", "UID", "DEPARTMENT")
VALUES (104, 'HARI', 'US9', 'ENGG')
INTO USER_TABLE ("ID", "NAME", "UID", "DEPARTMENT")
VALUES (105, 'MAYANK', 'US90', 'HR')
SELECT * FROM dual
;
CREATE TABLE USER_VISIT_STAT
("ID" int, "USID" int, "LAST_VISIT_DATE" timestamp)
;
INSERT ALL
INTO USER_VISIT_STAT ("ID", "USID", "LAST_VISIT_DATE")
VALUES (1, 101, '31-Aug-2018 12:00:00 AM')
INTO USER_VISIT_STAT ("ID", "USID", "LAST_VISIT_DATE")
VALUES (2, 102, '30-Aug-2018 12:00:00 AM')
INTO USER_VISIT_STAT ("ID", "USID", "LAST_VISIT_DATE")
VALUES (3, 101, '30-Aug-2018 12:00:00 AM')
INTO USER_VISIT_STAT ("ID", "USID", "LAST_VISIT_DATE")
VALUES (4, 103, '20-Feb-2016 12:00:00 AM')
INTO USER_VISIT_STAT ("ID", "USID", "LAST_VISIT_DATE")
VALUES (5, 104, '29-Aug-2016 12:00:00 AM')
INTO USER_VISIT_STAT ("ID", "USID", "LAST_VISIT_DATE")
VALUES (6, 105, '19-Jul-2016 12:00:00 AM')
INTO USER_VISIT_STAT ("ID", "USID", "LAST_VISIT_DATE")
VALUES (7, 101, '12-Jun-2016 12:00:00 AM')
INTO USER_VISIT_STAT ("ID", "USID", "LAST_VISIT_DATE")
VALUES (8, 102, '12-Jun-2016 12:00:00 AM')
INTO USER_VISIT_STAT ("ID", "USID", "LAST_VISIT_DATE")
VALUES (9, 104, '13-Apr-2016 12:00:00 AM')
SELECT * FROM dual
;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments