CODEWITHSUNDEEP
sqloracle-database
Sindhu
Sindhu

Reputation: 11

Comparing values sql

I have a table wherein I have to report the the present status and the date from which this status is applicable. Example:

Status   date
1        26 July
1        24 July
1        22 July
2        21 July
2        19 July
1        16 July
0        14 July

Given this, i want to display the current status as 1 and date as 22 July> I am not sure how to go about this.

Status   date
    1        25 July
    1        24 July
    1        20 July

In this case, I want to show the status as 1 and date as 20th July

Upvotes: 1

Views: 173

Answers (3)

WoMo
WoMo

Reputation: 7246

Another option is to use a window function like LEAD (or LAG depending on how you order your results). In this example we mark the row when the status changes with the date, order the results and exclude rows other than the first one:

with test_data as (
      select 1 status, date '2012-07-26' status_date from dual union all
      select 1 status, date '2012-07-24' status_date from dual union all
      select 1 status, date '2012-07-22' status_date from dual union all
      select 2 status, date '2012-07-21' status_date from dual union all
      select 2 status, date '2012-07-19' status_date from dual union all
      select 1 status, date '2012-07-16' status_date from dual union all
      select 0 status, date '2012-07-14' status_date from dual)

select status, as_of
from (
    select status
    , case when status != lead(status) over (order by status_date desc) then status_date else null end as_of
    from test_data
    order by as_of desc nulls last
    )
where rownum = 1;

Addendum: The LEAD and LAG functions accept two more parameters: offset and default. The offset defaults to 1, and default defaults to null. The default allows you to determine what value to consider when you are at the beginning or end of the result set. In your case when the status has never changed, a default is needed. In this example I supplied -1 as a status default because I am assuming that status value is not part of your expected set:

with test_data as (
      select 1 status, date '2012-07-25' status_date from dual union all
      select 1 status, date '2012-07-24' status_date from dual union all
      select 1 status, date '2012-07-20' status_date from dual)

select status, as_of
from (
    select status
    , case when status != lead(status,1,-1) over (order by status_date desc) then status_date else null end as_of
    from test_data
    order by as_of desc nulls last
    )
where rownum = 1;

You can play around with the case condition (equals/not equals), the order by clause in the lead function, and the desired default to accomplish your needs.

Upvotes: 0

Jodaka
Jodaka

Reputation: 1247

This is a little inelegant, but it should work

SELECT status, date
FROM my_table t
WHERE status = ALL (SELECT status
                    FROM my_table
                    WHERE date = ALL(SELECT MAX(date) FROM my_table))
AND date = ALL (SELECT MIN(date) 
                FROM my_table t1
                WHERE t1.status = t.status 
                AND NOT EXISTS (SELECT * 
                                  FROM my_table t2 
                                  WHERE t2.date > t1.date AND t2.status <> t1.status))

Upvotes: 0

Tim Lehner
Tim Lehner

Reputation: 15251

This should pull what you need using very standard SQL:

-- Get the oldest date that is the current Status
select Status, min(date) as date
from MyTable
where date > (
    -- Get the most recent date that isn't the current Status
    select max(date)
    from MyTable
    where Status != (
        -- Get the current Status
        select Status -- May need max/min here for multiple statuses on same date
        from MyTable
        where date = (
            -- Get the most recent date
            select max(date)
            from MyTable
        )
    )
)
group by Status

I'm assuming that the date column is of a data type suitable for sorting properly (as in, not a string, unless you can cast it).

Upvotes: 1

Related Questions