CODEWITHSUNDEEP
sqloracle-database
pratish_v
pratish_v

Reputation: 137

Get maximum date from database on conditional basis

I hae a flag and date combination in my table, so my table is like this

DATE        FLAG 
----------------
01-JAN-18   1
02-JAN-18   0.5
03-JAN-18   3
06-JAN-18   9

I want an SQL query for this table which returns me 0 or 1 for a particular date based on weather the previous maximum date in table was with flag 0 or not

I have tried this- first I get previous day for a particular day-

max_date = select max(date) from table where date < input_date;

after getting this date I get the flag for this date

select flag from table where date=max_date

I want a smart/better way to do this

Upvotes: 0

Views: 57

Answers (2)

Kaushik Nayak
Kaushik Nayak

Reputation: 31726

LAG is a good option, but if you wish to not use any analytical functions, you may have to use multiple joins.

You want a case where the previous flag value is zero, but you don't bother to give one example or expected output in your question. I have added it anyway.

SQL Fiddle

Oracle 11g R2 Schema Setup:

create table yourtable as 
select TO_DATE ('01-JAN-2018','DD-MON-YYYY') dt, 1 flag from dual UNION ALL
select TO_DATE ('02-JAN-2018','DD-MON-YYYY'), 0.5 from dual UNION ALL
select TO_DATE ('03-JAN-2018','DD-MON-YYYY'), 3  from dual UNION ALL
select TO_DATE ('06-JAN-2018','DD-MON-YYYY'), 9  from dual UNION ALL
select TO_DATE ('08-JAN-2018','DD-MON-YYYY'), 0  from dual UNION ALL
select TO_DATE ('11-JAN-2018','DD-MON-YYYY'), 7  from dual UNION ALL
select TO_DATE ('14-JAN-2018','DD-MON-YYYY'), 12 from dual

Query 1:

     SELECT s.dt,s.flag,
           CASE
             WHEN t.flag = 0 THEN 1
             ELSE 0
           END is_prev_dt_flag_zero
    FROM   yourtable t
           RIGHT OUTER JOIN (SELECT a.dt,MAX(a.flag) flag,
                              Max(b.dt) prev_date
                       FROM   yourtable a
                              LEFT OUTER JOIN yourtable b
                                     ON b.dt < a.dt
                       GROUP  BY a.dt) s
                   ON t.dt = s.prev_date
    ORDER  BY s.dt

Results:

|                   DT | FLAG | IS_PREV_DT_FLAG_ZERO |
|----------------------|------|----------------------|
| 2018-01-01T00:00:00Z |    1 |                    0 |
| 2018-01-02T00:00:00Z |  0.5 |                    0 |
| 2018-01-03T00:00:00Z |    3 |                    0 |
| 2018-01-06T00:00:00Z |    9 |                    0 |
| 2018-01-08T00:00:00Z |    0 |                    0 |
| 2018-01-11T00:00:00Z |    7 |                    1 |
| 2018-01-14T00:00:00Z |   12 |                    0 |

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

Use lag with a case expression.

select dt,flag,case when lag(flag) over(order by dt) = 0 then 1 else 0 end as prev_zero_or_not
from tbl

Upvotes: 1

Related Questions