CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Qasim0787
Qasim0787

Reputation: 301

How to compare dates in a query with previous month dates

I am trying to write a query in oracle for which I have a table A, I want to find Transfer in course. In this requirement I have to compare Feb data with Jan and produce another column with Y or N results.

ID     Date          Course1    Course2
123    01-JAN-2010   2000       3500
123    01-FEB-2010   2000       3500
123    01-MAR-2010   2500       3500
123    01-APR-2010   2000       3500
123    01-MAY-2010   2000       3000
123    01-JUN-2010   2000       3500

Requirement: If course1 <> previous month course1 or course2 <> previous month course2 then Y else N

Expected Results:

ID     Date          Course1    Course2   Transfer
123    01-JAN-2010   2000       3500      
123    01-FEB-2010   2000       3500      N
123    01-MAR-2010   2500       3500      Y
123    01-APR-2010   2000       3500      Y
123    01-MAY-2010   2000       3000      Y
123    01-JUN-2010   2000       3000      N

My Query:

select a.ID, a.Date, a.Course1, a.Course2,

case when (a.Course1 <> (select b.Course1 from TableA b where b.Date = add_months(a.Date-1) and b.ID = a.ID )  or a.Course2 <> (select b.Course2 from TableA b where b.Date = add_months(a.Date-1) and b.ID = a.ID ) )
        then Y
    else N
end  as Transfer

from TableA a
where a.ID = '123';

But this query I am getting error or invalid arguments....

Need help to correct the query or any other approach to get required results.

Thanks

Adding------

if you can use this data you will see what issue I am getting

insert into TableA (idd, datee, course1, course2) values (123,'01-JAN-2010', 2000, NULL);
insert into TableA (idd, datee, course1, course2) values (123,'01-FEB-2010', 2000, NULL);
insert into TableA (idd, datee, course1, course2) values (123,'01-MAR-2010', 2000, NULL);
insert into TableA (idd, datee, course1, course2) values (123,'01-APR-2010', 2000, NULL);
insert into TableA (idd, datee, course1, course2) values (123,'01-MAY-2010', 2000, NULL);
insert into TableA (idd, datee, course1, course2) values (123,'01-JUN-2010', 2000, NULL);
insert into TableA (idd, datee, course1, course2) values (123,'01-JUL-2010', 2000, NULL);
insert into TableA (idd, datee, course1, course2) values (123,'01-AUG-2010', 2000, NULL);
insert into TableA (idd, datee, course1, course2) values (123,'01-SEP-2010', 2500, NULL);
insert into TableA (idd, datee, course1, course2) values (123,'01-OCT-2010', 2000, NULL);
insert into TableA (idd, datee, course1, course2) values (123,'01-NOV-2010', 2000, NULL);
insert into TableA (idd, datee, course1, course2) values (123,'01-DEC-2010', 2000, NULL);

insert into TableA (idd, datee, course1, course2) values (123,'01-JAN-2011', 2000, NULL);
insert into TableA (idd, datee, course1, course2) values (123,'01-FEB-2011', 2000, NULL);
insert into TableA (idd, datee, course1, course2) values (123,'01-MAR-2011', 2000, NULL);
insert into TableA (idd, datee, course1, course2) values (123,'01-APR-2011', 2000, NULL);
insert into TableA (idd, datee, course1, course2) values (123,'01-MAY-2011', 2000, NULL);
insert into TableA (idd, datee, course1, course2) values (123,'01-JUN-2011', 2000, NULL);
insert into TableA (idd, datee, course1, course2) values (123,'01-JUL-2011', 2000, NULL);
insert into TableA (idd, datee, course1, course2) values (123,'01-AUG-2011', 2000, NULL);
insert into TableA (idd, datee, course1, course2) values (123,'01-SEP-2011', 2500, NULL);
insert into TableA (idd, datee, course1, course2) values (123,'01-OCT-2011', 2000, NULL);
insert into TableA (idd, datee, course1, course2) values (123,'01-NOV-2011', 2000, NULL);
insert into TableA (idd, datee, course1, course2) values (123,'01-DEC-2011', 2000, NULL);

insert into TableA (idd, datee, course1, course2) values (123,'01-JAN-2012', 2000, NULL);
insert into TableA (idd, datee, course1, course2) values (123,'01-FEB-2012', 2000, NULL);
insert into TableA (idd, datee, course1, course2) values (123,'01-MAR-2012', 2000, NULL);
insert into TableA (idd, datee, course1, course2) values (123,'01-APR-2012', 2000, NULL);
insert into TableA (idd, datee, course1, course2) values (123,'01-MAY-2012', 2000, NULL);
insert into TableA (idd, datee, course1, course2) values (123,'01-JUN-2012', 2000, NULL);
insert into TableA (idd, datee, course1, course2) values (123,'01-JUL-2012', 2000, NULL);
insert into TableA (idd, datee, course1, course2) values (123,'01-AUG-2012', 2000, NULL);
insert into TableA (idd, datee, course1, course2) values (123,'01-SEP-2012', 2500, NULL);
insert into TableA (idd, datee, course1, course2) values (123,'01-OCT-2012', 2000, NULL);
insert into TableA (idd, datee, course1, course2) values (123,'01-NOV-2012', 2000, NULL);
insert into TableA (idd, datee, course1, course2) values (123,'01-DEC-2012', 2000, NULL);


insert into TableA (idd, datee, course1, course2) values (456,'01-JAN-2011', 2000, NULL);
insert into TableA (idd, datee, course1, course2) values (456,'01-FEB-2011', 2000, NULL);
insert into TableA (idd, datee, course1, course2) values (456,'01-MAR-2011', 2000, NULL);
insert into TableA (idd, datee, course1, course2) values (456,'01-APR-2011', 2000, NULL);
insert into TableA (idd, datee, course1, course2) values (456,'01-MAY-2011', 2000, NULL);
insert into TableA (idd, datee, course1, course2) values (456,'01-JUN-2011', 2000, NULL);
insert into TableA (idd, datee, course1, course2) values (456,'01-JUL-2011', 2000, NULL);
insert into TableA (idd, datee, course1, course2) values (456,'01-AUG-2011', 2000, NULL);
insert into TableA (idd, datee, course1, course2) values (456,'01-SEP-2011', 2500, NULL);
insert into TableA (idd, datee, course1, course2) values (456,'01-OCT-2011', 2500, NULL);
insert into TableA (idd, datee, course1, course2) values (456,'01-NOV-2011', 2500, NULL);
insert into TableA (idd, datee, course1, course2) values (456,'01-DEC-2011', 2500, NULL);

insert into TableA (idd, datee, course1, course2) values (456,'01-JAN-2012', 2500, NULL);
insert into TableA (idd, datee, course1, course2) values (456,'01-FEB-2012', 2500, NULL);
insert into TableA (idd, datee, course1, course2) values (456,'01-MAR-2012', 25000, NULL);
insert into TableA (idd, datee, course1, course2) values (456,'01-APR-2012', 2000, NULL);
insert into TableA (idd, datee, course1, course2) values (456,'01-MAY-2012', 2000, NULL);
insert into TableA (idd, datee, course1, course2) values (456,'01-JUN-2012', 2000, NULL);
insert into TableA (idd, datee, course1, course2) values (456,'01-JUL-2012', 2000, NULL);
insert into TableA (idd, datee, course1, course2) values (456,'01-AUG-2012', 2000, NULL);
insert into TableA (idd, datee, course1, course2) values (456,'01-SEP-2012', 2000, NULL);
insert into TableA (idd, datee, course1, course2) values (456,'01-OCT-2012', 2000, NULL);
insert into TableA (idd, datee, course1, course2) values (456,'01-NOV-2012', 2000, NULL);
insert into TableA (idd, datee, course1, course2) values (456,'01-DEC-2012', 2000, NULL);

insert into TableA (idd, datee, course1, course2) values (456,'01-JAN-2013', 2000, NULL);
insert into TableA (idd, datee, course1, course2) values (456,'01-FEB-2013', 2000, NULL);
insert into TableA (idd, datee, course1, course2) values (456,'01-MAR-2013', 2000, NULL);
insert into TableA (idd, datee, course1, course2) values (456,'01-APR-2013', 2000, NULL);
insert into TableA (idd, datee, course1, course2) values (456,'01-MAY-2013', 2000, NULL);
insert into TableA (idd, datee, course1, course2) values (456,'01-JUN-2013', 2000, NULL);
insert into TableA (idd, datee, course1, course2) values (456,'01-JUL-2013', 2000, NULL);
insert into TableA (idd, datee, course1, course2) values (456,'01-AUG-2013', 2000, NULL);
insert into TableA (idd, datee, course1, course2) values (456,'01-SEP-2013', 2500, NULL);
insert into TableA (idd, datee, course1, course2) values (456,'01-OCT-2013', 2500, NULL);
insert into TableA (idd, datee, course1, course2) values (456,'01-NOV-2013', 2500, NULL);
insert into TableA (idd, datee, course1, course2) values (456,'01-DEC-2013', 2500, NULL);

my query

select idd, datee, course1, course2, 
  case when (course1 <> lag(course1,1,course1) over (order by datee)) or
        (course2 <> lag(course2,1,course2) over (order by datee)) 
  then 'Y' else 'N' end as transfer
 from tableA 
  where idd in ('123', '456')
 order by idd, datee;

Upvotes: 3

Views: 77

Answers (1)

Kacper
Kacper

Reputation: 4818

In oracle there is function lag.

select id, date, course1, course2, 
  case when (course1 <> lag(course1,1,course1) over (order by date)) or
            (course2 <> lag(course2,1,course2) over (order by date)) 
  then 'Y' else 'N' end as transfer
  from tableA 
  order by date;

EDIT

select idd, datee, course1, course2, 
  case when (course1 <> lag(course1,1,course1) over (order by idd, datee)) or
        (course2 <> lag(course2,1,course2) over (order by idd, datee)) 
  then 'Y' else 'N' end as transfer
 from tableA 
  where idd in ('123', '456')
 order by idd, datee;

Upvotes: 2

Related Questions