CODEWITHSUNDEEP
sqloracle-database
coder
coder

Reputation: 29

Oracle get the data that related to the previous day

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

Upvotes: 0

Views: 55

Answers (3)

MT0
MT0

Reputation: 167877

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

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

db<>fiddle here

Upvotes: 4

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

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.

Upvotes: 0

user3863616
user3863616

Reputation: 195

what about :

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

Upvotes: 0

Related Questions