How to select only these ID which logged only one time during last 3 months based on Timestamp colum-CodePudding

I have table in Oracle SQL like below:

ID   date
------------
11  | 2021-07-01 12:55:13.278
11  | 2021-06-01 12:55:13.278
22  | 2021-06-01 12:55:13.278
33  | 2021-05-01 12:55:13.278

And I need to select only these clients who logged only ONE time during last 3 months from current date (2021-09-28).

So as a result I need

ID   
----
11

Because only client with ID = 11 logged only ONE time (2021-07-01 12:55:13.278) during last 3 months.

I think that maybe sample of table is not large, but describions is clear. Be aware that format of column with data is probably timestamp with date and time and it needs special selection probably?

CodePudding user response：

select ID from tbl_name
WHERE 
DATE > add_months( sysdate, -3 )
group by ID having count(*) = 1

NOTE: reserved words like date souldnt be used as column name

CodePudding user response：

I'd filter those who logged last three months and count them afterwards

select id
  from test_data
 where date_col > add_months(to_date('2021-09-28', 'yyyy-mm-dd'), -3)
 group by id
 having count(1) = 1

CodePudding user response：

The DATA column looks like VARCHAR, just compare directly

select t1.id from tab1 t1 
 group by t1.id 
having count(case when t1.data > to_char(add_months(to_date('2021-09-28', 'yyyy-mm-dd'), -3), 'yyyy-mm-dd')
                  then 1 else null end) = 1

CodePudding user response：

Another option

with x ( id , date_logon ) 
as 
(
select 11 , to_timestamp('2021-07-01 12:55:13.278','YYYY-MM-DD HH24:MI:SS.FF3') from dual union all
select 11 , to_timestamp('2021-06-01 12:55:13.278','YYYY-MM-DD HH24:MI:SS.FF3') from dual union all
select 22 , to_timestamp('2021-06-01 12:55:13.278','YYYY-MM-DD HH24:MI:SS.FF3') from dual union all
select 33 , to_timestamp('2021-05-01 12:55:13.278','YYYY-MM-DD HH24:MI:SS.FF3') from dual
), 
z as 
(
select id , min(date_logon) min_val, max(date_logon) max_val
from x group by id 
) 
select * from z where add_months(sysdate,-3) between min_val and max_val;

       ID MIN_VAL                                                                     MAX_VAL
---------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------
        11 01-JUN-21 12.55.13.278000000 PM                                             01-JUL-21 12.55.13.278000000 PM