No question at this time
DBA Top 10
1 M. Cadot 9500
2 B. Vroman 7200
3 A. Kavsek 4600
4 P. Wisse 2000
5 J. Péran 1700
6 J. Schnackenberg 1200
7 T. Boles 800
8 D. Johnson 600
9 D. Dave 400
10 S. Spb 300
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48624
Total active users1393
Act. users last 24h1
Act. users last hour0
Registered user hits last week48
Registered user hits last month117
Go up

Comparison of rows
Next thread: db growth on daily basis
Prev thread: uploading files on my support

Message Score Author Date
Hi guys, I want a query which compare date wise...... Rahul Singh Jun 15, 2021, 12:50
Hello Rahul, my understanding: the "PID" is the...... Bruno Vroman Jun 15, 2021, 17:47
...continued<pre> PID DATETIME ...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Bruno Vroman Jun 15, 2021, 17:48
Thanks bruno for the quick solution, I will check ...... Rahul Singh Jun 16, 2021, 22:29
Thanks bruno it is working as expected.... Rahul Singh Jun 23, 2021, 07:50
Hi Bruno, Great explanation and great table ...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Joël Péran Jun 23, 2021, 08:07

Follow up by mail Click here


Subject: Comparison of rows
Author: Rahul Singh, India
Date: Jun 15, 2021, 12:50, 50 days ago
Os info: Windows
Oracle info: 11g
Message: Hi guys,

I want a query which compare date wise column values and output the process who does not follow cycle

Requirement :

There is a cycle to be followed by every process having validation 0 or 1 with timestamp.
a. First the process insert record in table having column of validation to 0
b. Second the process insert record in table having column of validation to 1

I want to fetch records which have not followed this cycle:

Below is my sample data ;

PID Datetime Validation
101 12-Jun-21 12:22 1
101 12-Jun-21 12:20 0
101 12-Jun-21 12:22 0
101 11-Jun-21 12:20 0
101 11-Jun-21 10:22 1
101 11-Jun-21 10:20 0
102 11-Jun-21 10:22 1
102 11-Jun-21 10:20 0
102 11-Jun-21 15:22 0
102 10-Jun-21 10:20 0

Output would be :

PID Datetime Validation

101 11-Jun-21 12:20 0
102 10-Jun-21 10:20 0
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Comparison of rows
Author: Bruno Vroman, Belgium
Date: Jun 15, 2021, 17:47, 50 days ago
Message: Hello Rahul,

my understanding: the "PID" is the base unit.
For one PID we have 1 to several datetime, and each time a "validation" equal to 0 or 1.
There might be many records, but if we order by datetime for a given pid the validations must follow 0,1,0,1,0,...
I assume that [PID, datetime] is unique...

Here is a way that detects the first problem for each PID (and for next rows it is not possible to know if they are wrong or right)
a) I sort rows per PID, DATETIME
b) I check if "VALIDATION" is equal to "MODULO( rownum + 1, 2 )"
c) if not, "problematic row".

(about the remark: "for next rows it is not possible to knwo if they are wrong or right": if you have for example
0 1 0 1 0 1 0 all is right, but
0 0 1 0 1 the second causes an issue, but maybe the real issue is that there was a missing row "1" before, or maybe all rows are wrong...
so we have simply to tell "this PID has an issue detected at position 2" and for the rest, use human judgment.
)
Here is code (with a "variation" about the Modulo thing, I leave its explanation as an exercise)
CREATE TABLE provide_it_next_time(

pid NUMBER(3)
, datetime DATE
, validation NUMBER(1) NOT NULL CHECK( validation IN ( 0, 1 ) )
);
ALTER TABLE provide_it_next_time ADD CONSTRAINT pk PRIMARY KEY( pid, datetime );
REM insert rows that are OK, with 1, 2, 3 or 4 datetimes per pid
INSERT INTO provide_it_next_time VALUES ( 100, sysdate-0, 0 );
INSERT INTO provide_it_next_time VALUES ( 101, sysdate-2, 0 );
INSERT INTO provide_it_next_time VALUES ( 101, sysdate-1, 1 );
INSERT INTO provide_it_next_time VALUES ( 102, sysdate-2, 0 );
INSERT INTO provide_it_next_time VALUES ( 102, sysdate-1, 1 );
INSERT INTO provide_it_next_time VALUES ( 102, sysdate-0, 0 );
INSERT INTO provide_it_next_time VALUES ( 103, sysdate-3, 0 );
INSERT INTO provide_it_next_time VALUES ( 103, sysdate-2, 1 );
INSERT INTO provide_it_next_time VALUES ( 103, sysdate-1, 0 );
INSERT INTO provide_it_next_time VALUES ( 103, sysdate-0, 1 );
REM insert rows that are KO, with 1, 2, 3 or 4 datetimes per pid
INSERT INTO provide_it_next_time VALUES ( 104, sysdate-0, 1 );
INSERT INTO provide_it_next_time VALUES ( 105, sysdate-2, 0 );
INSERT INTO provide_it_next_time VALUES ( 105, sysdate-1, 0 );
INSERT INTO provide_it_next_time VALUES ( 105, sysdate-0, 1 );
INSERT INTO provide_it_next_time VALUES ( 106, sysdate-4, 0 );
INSERT INTO provide_it_next_time VALUES ( 106, sysdate-3, 1 );
INSERT INTO provide_it_next_time VALUES ( 106, sysdate-2, 1 );
INSERT INTO provide_it_next_time VALUES ( 106, sysdate-1, 1 );
INSERT INTO provide_it_next_time VALUES ( 106, sysdate-0, 1 );
INSERT INTO provide_it_next_time VALUES ( 107, sysdate-5, 0 );
INSERT INTO provide_it_next_time VALUES ( 107, sysdate-4, 1 );
INSERT INTO provide_it_next_time VALUES ( 107, sysdate-3, 0 );
INSERT INTO provide_it_next_time VALUES ( 107, sysdate-2, 0 );
INSERT INTO provide_it_next_time VALUES ( 107, sysdate-1, 1 );
INSERT INTO provide_it_next_time VALUES ( 107, sysdate-0, 0 );
COMMIT;

WITH theory AS
( SELECT p.pid, p.datetime, p.validation
, ROW_NUMBER() OVER ( PARTITION BY p.pid
ORDER BY p.datetime ) n
FROM provide_it_next_time p
)
, issues AS
( SELECT t.pid, t.datetime, t.validation, t.n
FROM theory t
WHERE MOD( t.n + t.validation, 2 ) != 1
)
, first_issue_per_pid AS
( SELECT i.pid, MIN( i.n ) n
FROM issues i
GROUP BY i.pid
)
SELECT i.pid, i.datetime, i.validation wrong, i.n nth
FROM issues i
INNER JOIN first_issue_per_pid f
ON i.pid = f.pid
AND i.n = f.n
ORDER BY i.pid
;
continued...
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Comparison of rows
Author: Bruno Vroman, Belgium
Date: Jun 15, 2021, 17:48, 50 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: ...continued
           PID DATETIME                  WRONG        NTH

---------- -------------------- ---------- ----------
104 15-JUN-2021 17:37:25 1 1
105 14-JUN-2021 17:37:25 0 2
106 13-JUN-2021 17:37:25 1 3
107 13-JUN-2021 17:37:25 0 4
(for example the last line tells that for PID 107 the fourth validation is zero although it is expected to be one).

With your data:
TRUNCATE TABLE provide_it_next_time;

INSERT INTO provide_it_next_time VALUES (101, to_date('12-Jun-21 12:22', 'DD-Mon-YY HH24:MI'), 1);
INSERT INTO provide_it_next_time VALUES (101, to_date('12-Jun-21 12:20', 'DD-Mon-YY HH24:MI'), 0);
INSERT INTO provide_it_next_time VALUES (101, to_date('12-Jun-21 12:22', 'DD-Mon-YY HH24:MI'), 0);
INSERT INTO provide_it_next_time VALUES (101, to_date('11-Jun-21 12:20', 'DD-Mon-YY HH24:MI'), 0);
INSERT INTO provide_it_next_time VALUES (101, to_date('11-Jun-21 10:22', 'DD-Mon-YY HH24:MI'), 1);
INSERT INTO provide_it_next_time VALUES (101, to_date('11-Jun-21 10:20', 'DD-Mon-YY HH24:MI'), 0);
INSERT INTO provide_it_next_time VALUES (102, to_date('11-Jun-21 10:22', 'DD-Mon-YY HH24:MI'), 1);
INSERT INTO provide_it_next_time VALUES (102, to_date('11-Jun-21 10:20', 'DD-Mon-YY HH24:MI'), 0);
INSERT INTO provide_it_next_time VALUES (102, to_date('11-Jun-21 15:22', 'DD-Mon-YY HH24:MI'), 0);
INSERT INTO provide_it_next_time VALUES (102, to_date('10-Jun-21 10:20', 'DD-Mon-YY HH24:MI'), 0);
output would be
       PID DATETIME                  WRONG        NTH

---------- -------------------- ---------- ----------
101 12-JUN-2021 12:20:00 0 4
102 11-JUN-2021 10:20:00 0 2
HTH,

Bruno Vroman.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Comparison of rows
Author: Rahul Singh, India
Date: Jun 16, 2021, 22:29, 49 days ago
Message: Thanks bruno for the quick solution, I will check and let you know in case of any query pertaining to the resolution you have given.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Comparison of rows
Author: Rahul Singh, India
Date: Jun 23, 2021, 07:50, 43 days ago
Message: Thanks bruno it is working as expected.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Comparison of rows
Author: Joël Péran, France
Date: Jun 23, 2021, 08:07, 43 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Hi Bruno,

Great explanation and great table name :)
I'm not sure you were understood on that particular thing :)
Regars,
Joel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here