Sql

select t1.*

from test_table t1

where not exists ( select t2.t_id

                     from test_table t2

                     where t2.cust_id = t1.cust_id

                     and t2.t_id != t1.t_id

                     and extract(month from t2.trans_dt) = 10

                     )

and extract(month from t1.trans_dt) = 9

Try this (11.2.0.3) with extended test cases.. Just used your dataset.

   select
               cust_id
   from
               test_table
   having
               count (
                       case
                                    when to_char ( trans_dt, 'MM' ) = 9 then 1
                       end
                      ) = 1
   and         count (
                       case
                                    when to_char ( trans_dt, 'MM' ) = 10 then 1
                       end
                      ) = 0
   group by
               cust_id;

Hello Oracle_Monkey,

an "easy to understand" way: a) list September customers b) list October customers c) result is "a minus b"

WITH sep AS

( SELECT t1.cust_id

    FROM test_table t1

    WHERE EXISTS( SELECT NULL

                    FROM test_table t2

                    WHERE t2.trans_dt >= DATE '2017-09-01'

                      AND t2.trans_dt < DATE '2017-10-01'

                      AND t2.cust_id = t1.cust_id

                )

), oct AS

( SELECT t1.cust_id

    FROM test_table t1

    WHERE EXISTS( SELECT NULL

                    FROM test_table t2

                    WHERE t2.trans_dt >= DATE '2017-10-01'

                      AND t2.trans_dt < DATE '2017-11-01'

                      AND t2.cust_id = t1.cust_id

                )

)

SELECT sep.cust_id

  FROM sep

MINUS

SELECT oct.cust_id

  FROM oct

;

Best regards,

Bruno Vroman.

SQL> select * from test_table

match_recognize

(

  partition by cust_id

  order by trans_dt

  pattern( x+ y* $)

  define

    x as extract(month from trans_dt)  = 9,

    y as extract(month from trans_dt) != 10

);

   CUST_ID

----------

       200

       600

Hi ,

  select distinct cust_id from test_table where extract(month from trans_dt)  = 9

   and cust_id not in (select cust_id from test_table where extract(month from trans_dt)  = 10 );

Hi,

Oracle_Monkey wrote:

 

Hello ,

 

I need the customer ids who have done Transaction in September but not in October

 

...

You didn't say anything about the year.  Do you mean the customer must have at least 1 transaction in September of some year, and must not have any transactions in October of any year?  If so:

SELECT    cust_id
FROM      test_table
WHERE     TO_CHAR (trans_dt, 'MM')  IN ('09', '10')
GROUP BY  cust_id
HAVING    MAX (TO_CHAR (trans_dt, 'MM'))  = '09'
ORDER BY  cust_id;

If you want customers that had a transaction in September of 2017, but not in October of 2017, then:

SELECT    cust_id
FROM      test_table
WHERE     trans_dt  >= TO_DATE ('01-SEP-2017', 'DD-MON-YYYY')
AND       trans_dt  <  TO_DATE ('01-NOV-2017', 'DD-MON-YYYY')
GROUP BY  cust_id
HAVING    MAX (trans_dt)  < TO_DATE ('01-OCT-2017', 'DD-MON-YYYY')
ORDER BY  cust_id;

Always use 4 digits for the year.

If you need to find for any given year, some rows for SEPT and no rows for OCT, then partitioning by year will get you there.

SQL> with t (id, cust_id, trans_dt) as

(

  select 1,100,to_date('12-SEP-17','DD-MON-RR') from dual union all 

  select 2,100,to_date('12-OCT-17','DD-MON-RR') from dual union all

  select 3,200,to_date('12-SEP-17','DD-MON-RR') from dual union all

  select 10,200,to_date('12-OCT-18','DD-MON-RR') from dual union all

  select 11,200,to_date('12-SEP-18','DD-MON-RR') from dual union all 

  select 12,200,to_date('12-MAY-16','DD-MON-RR') from dual union all

  select 13,200,to_date('12-SEP-16','DD-MON-RR') from dual union all

  select 4,300,to_date('12-OCT-17','DD-MON-RR') from dual union all 

  select 5,400,to_date('12-JAN-17','DD-MON-RR') from dual union all 

  select 6,500,to_date('12-OCT-17','DD-MON-RR') from dual union all 

  select 7,500,to_date('12-MAR-17','DD-MON-RR') from dual union all

  select 8,600,to_date('12-SEP-17','DD-MON-RR') from dual union all 

  select 9,600,to_date('12-JUL-17','DD-MON-RR') from dual

)

select * from

(select t.*, extract(year from trans_dt) yr from t)

match_recognize

(

  partition by cust_id, yr

  order by trans_dt

  pattern( x+ y* $)

  define

    x as extract(month from trans_dt)  = 9,

    y as extract(month from trans_dt) != 10

);

   CUST_ID         YR

---------- ----------

       200       2016

       200       2017

       600       2017

if you need just the customer ids:

select distinct cust_id from

(select t.*, extract(year from trans_dt) yr from t)

match_recognize

(

  partition by cust_id, yr

  order by trans_dt

  pattern( x+ y* $)

  define

    x as extract(month from trans_dt)  = 9,

    y as extract(month from trans_dt) != 10

);

   CUST_ID

----------

       200

       600

Bruno,

Nice to see the MINUS, but the following seems likely to be more efficient:

SQL> l

  1  select cust_id from test_table where trunc(trans_dt,'MM') = to_date('01-Sep-2017')

  2  minus

  3  select cust_id from test_table where trunc(trans_dt,'MM') = to_date('01-Oct-2017')

  4*

SQL> /

   CUST_ID

----------

       200

       600

2 rows selected.

Regards

Jonathan Lewis

(Assuming the OP is thinking only of the most recent Sep/Oct pair of consecutive months - but it seems a little unlikely that the target is customer who have bought something in at least one september in the past but have never bought anything in an october).

Thank you Jonathan.

I'm aware that my way is not the more efficient (by far) but sometimes (often) I prefer a solution that is not the best but is easy to understand (and adapted to the level of the original question, after all the actual "customer").

For example the answer "4." of mNem with MATCH_RECOGNIZE is most probably more efficient, but I will spend more time trying to figure out the meaning of <<pattern( x+ y* $)>> than the overhead of CPU or IO that my version will put on database ;-)

Best regards,

Bruno

Bruno.Vroman wrote:

 

I'm aware that my way is not the more efficient (by far) but sometimes (often) I prefer a solution that is not the best but is easy to understand

 

 

For example the answer "4." of mNem with MATCH_RECOGNIZE is most probably more efficient, but I will spend more time trying to figure out the meaning of <<pattern( x+ y* $)>> than the overhead of CPU or IO that my version will put on database ;-)

 

Totally agree with the principle - human efficiency should always be part of the whole efficiency equation. Easy to understand, easy for the next person to modify (without introducing errors) is often worth a little extra CPU or I/O time.

I'm thinking about #4 as well - going to have to check the manuals for the interpretation rather than just making the "obvious" guess. It's clearly a winner from being a one-pass read of the table rather than two-pass, but it's not what Oracle would label as "DETERMINISTIC FINITE AUTO" so there may be excessive CPU costs to worry about depending on the data volume and pattern.  It's possible that a variation on Frank Kulash simple aggregation trick at #6 would be the most CPU efficient.

Regards

Jonathan Lewis

Hi Bruno,

Here is what I could come up with to explain the reply #4.

partition by cust_id order by trans_id:

for each logical group of cust_ids, ordered by trans_dt in ascending order,

x+

find one or more rows that satisfies the rule DEFINEd for x (month of trans_dt is 9)

immediately followed by

y*

find zero or more rows that satisfies the rule DEFINEd for y (month of trans_dt is NOT 10)

immediately followed by

$

anchor for end of rows for the partition

Please note that any row that may occur prior to matching the starting pattern variable x was ignored since our pattern is anchored to the end of rows (shown below for cust_id 700).

Here is a more verbose version that shows the matches relative to the rest of the pattern matches (including unmatched rows).

CLASSIFIER() is a built in function to show the matched pattern variable.

SQL> with t (id, cust_id, trans_dt) as

(

  select 1,100,to_date('12-SEP-17','DD-MON-RR') from dual union all

  select 2,100,to_date('12-OCT-17','DD-MON-RR') from dual union all

  select 3,200,to_date('12-SEP-17','DD-MON-RR') from dual union all

  select 4,300,to_date('12-OCT-17','DD-MON-RR') from dual union all

  select 5,400,to_date('12-JAN-17','DD-MON-RR') from dual union all

  select 6,500,to_date('12-OCT-17','DD-MON-RR') from dual union all

  select 7,500,to_date('12-MAR-17','DD-MON-RR') from dual union all

  select 8,600,to_date('12-SEP-17','DD-MON-RR') from dual union all

  select 9,600,to_date('12-JUL-17','DD-MON-RR') from dual union all

  select 14,700,to_date('12-JUN-17','DD-MON-RR') from dual union all

  select 15,700,to_date('12-MAY-17','DD-MON-RR') from dual union all

  select 10,700,to_date('12-SEP-17','DD-MON-RR') from dual union all

  select 11,700,to_date('12-NOV-17','DD-MON-RR') from dual union all

  select 12,700,to_date('10-DEC-17','DD-MON-RR') from dual union all

  select 13,700,to_date('12-DEC-17','DD-MON-RR') from dual

)

select * from t

match_recognize

(

  partition by cust_id

  order by trans_dt

  measures classifier() cl

  all rows per match with unmatched rows

  pattern( x+ y* $)

  define

    x as extract(month from trans_dt)  = 9,

    y as extract(month from trans_dt) != 10

);

   CUST_ID TRANS_DT        CL                 ID

---------- --------------- ---------- ----------

       100 12-Sep-2017                         1

       100 12-Oct-2017                         2

       200 12-Sep-2017     X                   3

       300 12-Oct-2017                         4

       400 12-Jan-2017                         5

       500 12-Mar-2017                         7

       500 12-Oct-2017                         6

       600 12-Jul-2017                         9

       600 12-Sep-2017     X                   8

       700 12-May-2017                        15

       700 12-Jun-2017                        14

       700 12-Sep-2017     X                  10

       700 12-Nov-2017     Y                  11

       700 10-Dec-2017     Y                  12

       700 12-Dec-2017     Y                  13

Hope it explains.

Dear mNem,

I hope you didn't feel offended by my remark, it was not at all "against you". Thank you for the explanations. My point was that all components of SQL are not equally simple... And when we read a question (this one or others), it can appear that the OP has not yet much experience, and if he/she is +/- a beginner, providing a complex answer will simply "fly over his/her head" even if it is "correct". For example if an OP shows that he has difficulties in understanding a simple JOIN between "emp" and "dept", the answer to the question should not use analytic functions.

Each of us has his/her own learning curve, and it is OK when we receive something that will push us up a little bit (like your explanations to me, thanks again), but the point is missed if we receive something that is too complex for us.

Hence when possible a simple answer can be good even if "gurus" can play together and propose many other "better" answers (sometimes we see this in the forums: a beginner asking a beginner's question followed by some "Ping-Pong" of gurus showing high level skills)

Have a nice day, as well as other readers (especially Oracle_Monkey, forgive us of having a bit "hijacked" your thread)

Bruno

Just aggregate them and apply filters on top of the aggregated values.

demo@ORA12C> select * from t;

      T_ID    CUST_ID TRANS_DT

---------- ---------- -----------

         1        100 12-SEP-2017

         2        100 12-OCT-2017

         3        200 12-SEP-2017

         4        300 12-OCT-2017

         5        400 12-JAN-2017

         6        500 12-OCT-2017

         7        500 12-MAR-2017

         8        600 12-SEP-2017

         9        600 12-JUL-2017

9 rows selected.

demo@ORA12C> select cust_id

  2  from t

  3  group by cust_id

  4  having count( case when trans_dt >= to_date('01-sep-2017')

  5                             and trans_dt < to_date('01-oct-2017')

  6                             then 1 end ) >0

  7     and count( case when trans_dt >= to_date('01-oct-2017')

  8                             and trans_dt < to_date('01-nov-2017')

  9                             then 1 end ) = 0

10  /

   CUST_ID

----------

       600

       200

demo@ORA12C>

So pretty much exactly what was suggested in reply 2 of this thread then?