Forum Stats

  • 3,838,564 Users
  • 2,262,383 Discussions
  • 7,900,687 Comments

Discussions

Yes/No Column based on multiple criterias

DaBears3579
DaBears3579 Member Posts: 6 Green Ribbon

Hello,

I'm looking to add a new column that will tell me whether a customer has been ticketed again after the initial ticket. So only the initial ticket should be marked with a "yes" and the most recent tickets afterwards should be marked as "no".

I've provided a screenshot example of the expected output based on fake data.


Tagged:

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,243 Red Diamond
    edited Aug 4, 2022 10:34PM Answer ✓

    Hi, @DaBears3579

    Originally you said:

    So only the initial ticket should be marked with a "yes" 

    Now do you mean "Only the initial ticket should be marked with a 'yes', and only if it thee are multiple tickets for that customer"? If so, you can add another condition, something like this:

    SELECT   ename, job, hiredate
    ,	 CASE
    	   WHEN MIN (hiredate) OVER (PARTITION BY job) = hiredate -- this is the initial row
    	   AND  COUNT (*)      OVER (PARTITION BY job) > 1	  -- there are multiple rows
    	   THEN 'Yes'
    	   ELSE 'No'
    	 END	AS first_row
    FROM	 scott_emp
    ORDER BY job, hiredate
    ;
    

    Except for 'PRESIDENT', the results are the same as before:

                                      FIRST
    ENAME      JOB       HIREDATE     _ROW
    ---------- --------- -----------  -----
    FORD       ANALYST   03-Dec-1981  Yes
    SCOTT      ANALYST   19-Apr-1987  No
    SMITH      CLERK     17-Dec-1980  Yes
    JAMES      CLERK     03-Dec-1981  No
    MILLER     CLERK     23-Jan-1982  No
    ADAMS      CLERK     23-May-1987  No
    JONES      MANAGER   02-Apr-1981  Yes
    BLAKE      MANAGER   01-May-1981  No
    CLARK      MANAGER   09-Jun-1981  No
    KING       PRESIDENT 17-Nov-1981  No
    ALLEN      SALESMAN  20-Feb-1981  Yes
    WARD       SALESMAN  22-Feb-1981  No
    TURNER     SALESMAN  08-Sep-1981  No
    MARTIN     SALESMAN  28-Sep-1981  No
    


Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,243 Red Diamond

    Hi, @DaBears3579

    Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements for all tables involved, relevant columns only) so the people who want to help you can re-create the problem and test their ideas. Also post the exact results you want from that data, and explain why you want those results from that data. Always post your complete Oracle version (e.g. 18.4.0.0.0).

    Don't use attachments. Not everyone can or will open attachments. Post everything right in this space.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,243 Red Diamond
    edited Aug 4, 2022 5:10PM

    You want to tell whether each ticket is the first ticket for a customer or not. That's essentially the same as telling whether each employee in the scott.emp table is the first employee for his job or not. Here's one way to do that:

    SELECT  ename, job, hiredate
    ,	 CASE
    	   WHEN hiredate = MIN (hiredate) OVER (PARTITION BY job)
    	   THEN 'Yes'
    	   ELSE 'No'
    	 END	AS first_row
    FROM	 scott.emp
    ORDER BY job, hiredate
    ;
    

    Output:

                                      FIRST
    ENAME      JOB       HIREDATE     _ROW
    ---------- --------- -----------  -----
    FORD       ANALYST   03-Dec-1981  Yes
    SCOTT      ANALYST   19-Apr-1987  No
    SMITH      CLERK     17-Dec-1980  Yes
    JAMES      CLERK     03-Dec-1981  No
    MILLER     CLERK     23-Jan-1982  No
    ADAMS      CLERK     23-May-1987  No
    JONES      MANAGER   02-Apr-1981  Yes
    BLAKE      MANAGER   01-May-1981  No
    CLARK      MANAGER   09-Jun-1981  No
    KING       PRESIDENT 17-Nov-1981  Yes
    ALLEN      SALESMAN  20-Feb-1981  Yes
    WARD       SALESMAN  22-Feb-1981  No
    TURNER     SALESMAN  08-Sep-1981  No
    MARTIN     SALESMAN  28-Sep-1981  No
    

    What do you want to do in case of a tie, where two or more rows for the same customer have the exact same earliest data?

  • DaBears3579
    DaBears3579 Member Posts: 6 Green Ribbon

    Thanks Frank! This is very close, however, when there's only 1 distinct entry (like "President" in your example) that should be labeled as "No" because they received the initial ticket, but didnt get another one.

    Thanks again

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,243 Red Diamond
    edited Aug 4, 2022 10:34PM Answer ✓

    Hi, @DaBears3579

    Originally you said:

    So only the initial ticket should be marked with a "yes" 

    Now do you mean "Only the initial ticket should be marked with a 'yes', and only if it thee are multiple tickets for that customer"? If so, you can add another condition, something like this:

    SELECT   ename, job, hiredate
    ,	 CASE
    	   WHEN MIN (hiredate) OVER (PARTITION BY job) = hiredate -- this is the initial row
    	   AND  COUNT (*)      OVER (PARTITION BY job) > 1	  -- there are multiple rows
    	   THEN 'Yes'
    	   ELSE 'No'
    	 END	AS first_row
    FROM	 scott_emp
    ORDER BY job, hiredate
    ;
    

    Except for 'PRESIDENT', the results are the same as before:

                                      FIRST
    ENAME      JOB       HIREDATE     _ROW
    ---------- --------- -----------  -----
    FORD       ANALYST   03-Dec-1981  Yes
    SCOTT      ANALYST   19-Apr-1987  No
    SMITH      CLERK     17-Dec-1980  Yes
    JAMES      CLERK     03-Dec-1981  No
    MILLER     CLERK     23-Jan-1982  No
    ADAMS      CLERK     23-May-1987  No
    JONES      MANAGER   02-Apr-1981  Yes
    BLAKE      MANAGER   01-May-1981  No
    CLARK      MANAGER   09-Jun-1981  No
    KING       PRESIDENT 17-Nov-1981  No
    ALLEN      SALESMAN  20-Feb-1981  Yes
    WARD       SALESMAN  22-Feb-1981  No
    TURNER     SALESMAN  08-Sep-1981  No
    MARTIN     SALESMAN  28-Sep-1981  No
    


  • Stax
    Stax Member Posts: 130 Bronze Badge
    SQL> ed
    Wrote file afiedt.buf
    
    
      1  with t(cl,dt) as (
      2  select 1,date '2022-07-11' from dual union all
      3  select 1,date '2022-07-15' from dual union all
      4  select 1,date '2022-08-05' from dual union all
      5  select 4,date '2022-08-01' from dual union all
      6  select 7,date '2022-08-11' from dual union all
      7  select 7,date '2022-02-11' from dual
      8  )
      9  select t.*,
     10   case
     11     count(*) over (partition by cl)
     12    when 1 then 'No'
     13    else lag('No',1,'Yes') over (partition by cl order by dt)
     14   end yn
     15  from t
     16* order by 1,2,3 desc
    SQL> /
    
    
            CL DT         YN
    ---------- ---------- ---
             1 11.07.2022 Yes
             1 15.07.2022 No
             1 05.08.2022 No
             4 01.08.2022 No
             7 11.02.2022 Yes
             7 11.08.2022 No
    
    
    6 rows selected.
    
    
    SQL>