Oracle get the data that related to the previous day-CodePudding

Table PJASSIGN

| PPRJECT  | LONINUSER      |DATE         | 
| -------- | -------------- | --------    |
| MSFT     | Ken            |01/12/2022   | 
| MSFT     | Ken            |01/13/2022   |
| MSFT     | John           |01/20/2022   | 
| MSFT     | John           |01/25/2022   |
| GOOGLE   | Gary           |03/13/2022   | 
| GOOGLE   | Gary           |03/14/2022   | 
| GOOGLE   | Gary           |03/15/2022   | 
| GOOGLE   | May            |03/16/2022   | 
| TSLA     | Juno           |05/18/2022   | 
| TSLA     | Juno           |05/20/2022   | 
| TSLA     | Juno           |05/22/2022   | 
| TSLA     | Juno           |05/24/2022   |

TABLE USERINFO

| USER_PJ_ID  | LONINUSER   |DATE     | 
| -------- | -------------- | --------|
| 001      | Ken            |MSFT     | 
| 002      | Jay            |APPL     |
| 003      | John           |MSFT     | 
| 004      | Mary           |DELL     |
| 005      | Gary           |GOOGLE   | 
| 007      | Juno           |TSLA     | 
| 009      | May            |GOOGLE   |

Only extract the data(LOGINUSER) that assign to the a project and working on it continues with more than two days. And do not show the data(LOGINUSER) that haven't assign the related project in the PJASSIGN table. How can I get the expected result like below with the use of (sysday-1)?

Expected result

| LOGINUSER |
| -------- | 
| Ken    | 
| Gary   |

How can I develop it to fit in the requirement?

Select LOGINUSER
From PJASSIGN
where (sysdate- 1,'yyyy-mm-dd HH24:MI:SS' )

Thanks for your help.

CodePudding user response：

From Oracle 12, use MATCH_RECOGNIZE to perform row-by-row processing:

SELECT project,
       loninuser
FROM   PJAssign
MATCH_RECOGNIZE(
  PARTITION BY project, loninuser
  ORDER     BY "DATE"
  PATTERN (first_day consecutive_days{2,})
  DEFINE
    consecutive_days AS PREV("DATE")   INTERVAL '1' DAY = "DATE"
);

Which, for the sample data:

CREATE TABLE PJAssign (PROJECT, LONINUSER, "DATE") AS
SELECT 'MSFT',   'Ken',  DATE '2022-01-12' FROM DUAL UNION ALL 
SELECT 'MSFT',   'Ken',  DATE '2022-01-13' FROM DUAL UNION ALL 
SELECT 'MSFT',   'John', DATE '2022-01-20' FROM DUAL UNION ALL 
SELECT 'MSFT',   'John', DATE '2022-01-25' FROM DUAL UNION ALL 
SELECT 'GOOGLE', 'Gary', DATE '2022-03-13' FROM DUAL UNION ALL 
SELECT 'GOOGLE', 'Gary', DATE '2022-03-14' FROM DUAL UNION ALL 
SELECT 'GOOGLE', 'Gary', DATE '2022-03-15' FROM DUAL UNION ALL 
SELECT 'GOOGLE', 'May',  DATE '2022-03-16' FROM DUAL UNION ALL 
SELECT 'TSLA',   'Juno', DATE '2022-05-18' FROM DUAL UNION ALL 
SELECT 'TSLA',   'Juno', DATE '2022-05-20' FROM DUAL UNION ALL 
SELECT 'TSLA',   'Juno', DATE '2022-05-22' FROM DUAL UNION ALL 
SELECT 'TSLA',   'Juno', DATE '2022-05-24' FROM DUAL;

Outputs:

PROJECT LONINUSER

GOOGLE Gary

PROJECT	LONINUSER
GOOGLE	Gary

Note: Ken only worked on a project for exactly 2 days, not more than 2 days.

db<>fiddle here

CodePudding user response：

what about :

select distinct LOGINUSER from PJASSIGN where trunc(date) = trunc(sysdate -1 );

CodePudding user response：

I think you simply need a LAG function to get your desired result -

SELECT DISTINCT LOGINUSER
  FROM (SELECT LONINUSER, DATE - LAG(DATE) OVER(PARTITION BY LONINUSER ORDER BY DATE) prev_date
          FROM PJASSIGN)
 WHERE prev_date = 1;

And as MT0 suggests, this will give you users who works on the proects for 2 days or more. If you need users more than 2 days, You need another logic.