Home » SQL & PL/SQL » SQL & PL/SQL » Need age difference between warning and critical (Oracle)
Need age difference between warning and critical [message #680106] Wed, 22 April 2020 01:22 Go to next message
soubalaji
Messages: 5
Registered: April 2020
Junior Member
Hi Team,

I have a requirement to find the age for the warning to critical and criticl to warning.

I have a table like below

Task->Date->status
A => 20-March-2020 -> clear
A => 19-March-2020 -> Warning
A => 18-March-2020 -> Critical
A => 17-March-2020 -> Warning
A => 16-March-2020 -> Warning
A => 15-March-2020 -> Clear
A => 20-Feb-2020 -> clear
A => 19-Feb-2020 -> Warning
A => 18-Feb-2020 -> Critical
A => 17-Feb-2020 -> Warning
A => 16-Feb-2020 -> Warning
A => 15-Feb-2020 -> Clear

Need to find recent age differene between critical to warning and warning to critical as

for critical to warning:
A => 19-March-2020 -> Warning
A => 18-March-2020 -> Critical

and for warning to critical:
A => 18-March-2020 -> Critical
A => 16-March-2020 -> Warning

and i want the final output like

Task=>Warning_critical_Age->Critical_Warning_Age
A=>1=>2

will you please help to solve this ?
Re: Need age difference between warning and critical [message #680107 is a reply to message #680106] Wed, 22 April 2020 01:24 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

The test case must be representative of your data. If you have several task then post a test case with several tasks...
Take care care that we have neither the same date format nor the same date language, use TO_DATE.

Have a look at LAG/LEAD functions.

[Updated on: Wed, 22 April 2020 01:28]

Report message to a moderator

Re: Need age difference between warning and critical [message #680116 is a reply to message #680107] Wed, 22 April 2020 08:18 Go to previous messageGo to next message
soubalaji
Messages: 5
Registered: April 2020
Junior Member
i have tried this below query,but getting multiple value instead of matching the first one

select distinct mel.task,mel.date,mel2.date
from
"tablea" "mel",
"tablea" "me2"
where
mel.task = mel2.task
and mel.severity='Warning'
and mel2.severity='Critical'
and mel.date <> mel2.date

any suggestion on this ?
Re: Need age difference between warning and critical [message #680118 is a reply to message #680116] Wed, 22 April 2020 08:24 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
Rows in a table are like balls in a basket. Which ball is the first ball.
WHERE clause has NO criteria for obtaining ONLY "first" row how ever you define first to be.
Re: Need age difference between warning and critical [message #680119 is a reply to message #680118] Wed, 22 April 2020 08:26 Go to previous messageGo to next message
soubalaji
Messages: 5
Registered: April 2020
Junior Member
Recent date is the one we need to fetch.
Re: Need age difference between warning and critical [message #680120 is a reply to message #680119] Wed, 22 April 2020 08:32 Go to previous messageGo to next message
soubalaji
Messages: 5
Registered: April 2020
Junior Member
i have tried with fetch first 1 rows only, while running with live table having multiple taks also it's fetching only 1 rows.
actually i need first row for all the mel.task.
Re: Need age difference between warning and critical [message #680121 is a reply to message #680120] Wed, 22 April 2020 08:42 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
soubalaji wrote on Wed, 22 April 2020 06:32
i have tried with fetch first 1 rows only, while running with live table having multiple taks also it's fetching only 1 rows.
actually i need first row for all the mel.task.

We don't have your table.
We don't have your data.
Therefore we can't write SQL to meet your requirements.

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: Need age difference between warning and critical [message #680122 is a reply to message #680121] Wed, 22 April 2020 08:52 Go to previous messageGo to next message
soubalaji
Messages: 5
Registered: April 2020
Junior Member
i am really sorry for this.I am not asking you to write sql query on behalf of me.
I am excepting your guidance on this.

How to fetch first 1 row for all the matched column. ?

Re: Need age difference between warning and critical [message #680123 is a reply to message #680122] Wed, 22 April 2020 09:15 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2985
Registered: January 2010
Location: Connecticut, USA
Senior Member
This is typical start-of-group case. Post create table and insert data statements,

SY.
Re: Need age difference between warning and critical [message #680125 is a reply to message #680122] Wed, 22 April 2020 12:10 Go to previous message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
soubalaji wrote on Wed, 22 April 2020 15:52
i am really sorry for this.I am not asking you to write sql query on behalf of me.
I am excepting your guidance on this.
...
Michel Cadot wrote on Wed, 22 April 2020 08:24

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

The test case must be representative of your data. If you have several task then post a test case with several tasks...
Take care care that we have neither the same date format nor the same date language, use TO_DATE.

Have a look at LAG/LEAD functions.


If you are REALLY sorry then post what we requested several times.

Previous Topic: Duplicate returns from list of Values
Next Topic: How too convert rows into columns based on order hiredate month wise
Goto Forum:
  


Current Time: Mon Sep 21 15:03:31 CDT 2020