1 2 Previous Next 17 Replies Latest reply on Mar 6, 2018 6:12 PM by mNem

    Sql

    Oracle_Monkey

      Hello ,

       

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

       

      Expected answer

      ===================

      200

      600

       

       

       CREATE TABLE TEST_TABLE 
         ( T_ID NUMBER, -- trans-id
      CUST_ID NUMBER, 
      TRANS_DT DATE
         ) ;
         
         
      Insert into TEST_TABLE (T_ID,CUST_ID,TRANS_DT) values (1,100,to_date('12-SEP-17','DD-MON-RR'));
      Insert into TEST_TABLE (T_ID,CUST_ID,TRANS_DT) values (2,100,to_date('12-OCT-17','DD-MON-RR'));
      Insert into TEST_TABLE (T_ID,CUST_ID,TRANS_DT) values (3,200,to_date('12-SEP-17','DD-MON-RR'));
      Insert into TEST_TABLE (T_ID,CUST_ID,TRANS_DT) values (4,300,to_date('12-OCT-17','DD-MON-RR'));
      Insert into TEST_TABLE (T_ID,CUST_ID,TRANS_DT) values (5,400,to_date('12-JAN-17','DD-MON-RR'));
      Insert into TEST_TABLE (T_ID,CUST_ID,TRANS_DT) values (6,500,to_date('12-OCT-17','DD-MON-RR'));
      Insert into TEST_TABLE (T_ID,CUST_ID,TRANS_DT) values (7,500,to_date('12-MAR-17','DD-MON-RR'));
      Insert into TEST_TABLE (T_ID,CUST_ID,TRANS_DT) values (8,600,to_date('12-SEP-17','DD-MON-RR'));
      Insert into TEST_TABLE (T_ID,CUST_ID,TRANS_DT) values (9,600,to_date('12-JUL-17','DD-MON-RR'));
      
      
      i wrote the below but i am not getting the correct value
      select *  from test_table T1 JOIN  test_table T2 
      ON  to_char( T1.TRANS_DT,'MON') ='SEP' AND   to_char( T2.TRANS_DT,'MON') !='OCT'
      AND T1.CUST_ID =T2.CUST_ID  AND T1.t_id  !=T2.t_id ;
      
        • 1. Re: Sql
          Hans Steijntjes

          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

          • 2. Re: Sql
            Manik

            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;
            
            • 3. Re: Sql
              BrunoVroman

              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.

              • 4. Re: Sql
                mNem

                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

                • 5. Re: Sql
                  3367698

                  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 );

                   

                   

                  CUST_ID
                  200
                  600
                  • 6. Re: Sql
                    Frank Kulash

                    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.

                    • 7. Re: Sql
                      mNem

                      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

                      • 8. Re: Sql
                        Jonathan Lewis

                        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).

                        • 9. Re: Sql
                          BrunoVroman

                          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

                          • 10. Re: Sql
                            Jonathan Lewis

                            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

                            • 11. Re: Sql
                              mNem

                              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.

                              • 12. Re: Sql
                                BrunoVroman

                                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

                                • 13. Re: Sql
                                  Rajeshwaran, Jeyabal

                                  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>

                                  • 14. Re: Sql
                                    John_K

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

                                    1 2 Previous Next