Yes/No Column based on multiple criterias

DaBears3579

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.

Screenshot.png

Frank Kulash

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

Frank Kulash

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

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

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

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

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>