How to get users who are not active for the past 6 months

Question

I have a field transaction_date which is varchar2, I want to make a search on that field and get user_id of that user who is not active for the past 6 months

I tried with below query but its not giving correct answer, my query is:

select distinct(user_id) 
from DAILYBOOKINGREPORT2 
where TRANSACTION_DATE  NOT BETWEEN to_date('31-MAY-2017') AND to_date('31-DEC-2016');

Possible duplicate of [How do you test if something is older than 3 months?](https://stackoverflow.com/questions/6931619/how-do-you-test-if-something-is-older-than-3-months) — Reece Young, Jun 21 '17 at 09:16
@ReeceYoung Not a duplicate - that question is for SQL Server and this is for Oracle. — MT0, Jun 21 '17 at 09:27

score 1 · Accepted Answer · answered Jun 21 '17 at 09:20

1

Use GROUP BY and HAVING to find those users where the maximum transaction date was more than 6 months ago:

SELECT   user_id
FROM     DAILYBOOKINGREPORT2
GROUP BY user_id
HAVING   MAX( TRANSACTION_DATE ) < ADD_MONTHS( SYSDATE, -6 );

answered Jun 21 '17 at 09:20

MT0

143,790
11
59
117

Reece Young · Answer 2 · 2017-06-21T09:17:00.773

-1

You want to make use of the DATEADD function

SELECT 
       DISTINCT(user_id) 
 FROM `DAILYBOOKINGREPORT2` 
WHERE `TRANSACTION_DATE` <= DATEADD(mm, -6, GETDATE())

More information: https://www.w3schools.com/sql/func_dateadd.asp

edited Jun 21 '17 at 09:17

answered Jun 21 '17 at 09:15

Reece Young

21
8

`DATEADD` is not an Oracle function and backticks are not valid Oracle syntax. – MT0 Jun 21 '17 at 09:17

How to get users who are not active for the past 6 months

2 Answers2