1

I am trying to find the best way to compare between rows in the same table. I wrote a self join query and was able to pull out the ones where the rates are different. Now I need to find out if the rates increased or decreased. If the rates increased, it's an issue. If it decreased, then there is no issue.

My data looks like this

ID       DATE           RATE
1010     02/02/2014      7.4
1010     03/02/2014      7.4
1010     04/02/2014      4.9
2010     02/02/2014      4.9
2010     03/02/2014      7.4
2010     04/02/2014      7.4

So in my table, I should be able to code ID 1010 as 0 (no issue) and 2010 as 1 (issue) because the rate went up from feb to apr.

Improve this question

4 Answers 4

Reset to default
3

You can achieve this with a select..case

select case when a.rate > b.rate then 'issue' else 'no issue' end
from yourTable a
join yourTable b using(id)
where a.date > b.date

See documentation for CASE expressions.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

That would pick up decreases in rates as well.
Thank you all. Is there any way we could code all the IDs as 'Issue' if any one of them is an issue? That way I can filter and get all the records with different dates for the ID with Issues rather than just one or two records. Thanks again!
2

Sounds like a case for LAG():

with sample_data as (select 1010 id, to_date('02/02/2014', 'mm/dd/yyyy') dt, 7.4 rate from dual union all
                     select 1010 id, to_date('03/02/2014', 'mm/dd/yyyy') dt, 7.4 rate from dual union all
                     select 1010 id, to_date('04/02/2014', 'mm/dd/yyyy') dt, 4.9 rate from dual union all
                     select 2010 id, to_date('02/02/2014', 'mm/dd/yyyy') dt, 4.9 rate from dual union all
                     select 2010 id, to_date('03/02/2014', 'mm/dd/yyyy') dt, 7.4 rate from dual union all
                     select 2010 id, to_date('04/02/2014', 'mm/dd/yyyy') dt, 7.4 rate from dual)
select id,
       dt,
       rate,
       case when rate > lag(rate, 1, rate) over (partition by id order by dt) then 1 else 0 end issue
from   sample_data;

        ID DT               RATE      ISSUE
---------- ---------- ---------- ----------
      1010 02/02/2014        7.4          0
      1010 03/02/2014        7.4          0
      1010 04/02/2014        4.9          0
      2010 02/02/2014        4.9          0
      2010 03/02/2014        7.4          1
      2010 04/02/2014        7.4          0

You may want to throw an outer query around that to only display rows that have issue = 1, or perhaps an aggregate query to retrieve id's that have at least one row that has issue = 1, depending on your actual requirements. Hopefully the above is enough for you to work out how to get what you're after.

Improve this answer

Comments

1 
select distinct ID from MyData latest
inner join MyData earlier on latest.id = earlier.id
where earlier.date < latest.date and earlier.rate < latest.rate

would be one way to get them unless you really need to select those with no issues?

Improve this answer

Comments

0

select a.&, case when a.rate > b.rate then 'issue' else 'no issue' end from table a join table b on a.ID=b.ID where a.date > b.date;

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.