OraFAQ Forum: SQL & PL/SQL » Date validation

Home » SQL & PL/SQL » SQL & PL/SQL » Date validation (11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Date validation [message #637391]

Sat, 16 May 2015 09:30

rohit_shinez
Messages: 139
Registered: January 2015

Senior Member

Hi,

I would like to validate particular date which is falling between start date and end and ignore those records if they don't met below criteria: for Eg


Create table Table_1(Balance_date date);
create table Table_2(Start_date date,End_date date);
create table Ref_table(Start_date date,end_date date);

insert into Table_1
select to_Date('15-Jan-2015','DD-MON-YYYY') from dual;

insert into Table_1
select to_date('20-Jan-2015','DD-MON-YYYY') from dual;

insert into Table_2
select to_date('15-Dec-2014','DD-MON-YYYY'), to_date('15-Jan-2015','DD-MON-YYYY') from dual;

Insert into table_2
select to_date('16-Jan-2015','DD-MON-YYYY'), to_date('16-Feb-2015','DD-MON-YYYY') from dual;

Insert into Ref_table
select to_date('01-Jan-2015','DD-MON-YYYY'), to_date('01-Feb-2015','DD-MON-YYYY') from dual;


Table_1

Balance_date
15-JAN-15
20-JAN-15

Table_2

Start_date        End_date
15-DEC-14	15-JAN-15
16-Jan-15         16-Feb-15

Ref_table
Start_date        End_date
01-JAN-15	01-FEB-15

I need to consider 20-JAN-2015 from table_1 and ignore 15-Jan-2015 record because i need to consider only those records which are falling between 01-Jan-2015 and 01-Feb-2015 of ref_table.
I will first compare the date with table_2 in this case both dates are falling between respective dates of Table_2 but need to ignore if the dates are not actually falling between 01-JAN-15 and 01-FEB-15 of reference table. I need to consider Ref_table start date as master reference, hence only 20-Jan-15 from table_1 should only be considered.

Report message to a moderator

Re: Date validation [message #637392 is a reply to message #637391]

Sat, 16 May 2015 10:43

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

rohit_shinez wrote on Sat, 16 May 2015 20:00

I need to consider 20-JAN-2015 from table_1 and ignore 15-Jan-2015 record because i need to consider only those records which are falling between 01-Jan-2015 and 01-Feb-2015 of ref_table.

15-Jan-2015 and 20-JAN-2015 both fall between 01-Jan-2015 and 01-Feb-2015. It is not clear to me why/how 15-Jan-2015 should be ignored?

[Updated on: Sat, 16 May 2015 10:43]

Report message to a moderator

Re: Date validation [message #637393 is a reply to message #637392]

Sat, 16 May 2015 10:50

rohit_shinez
Messages: 139
Registered: January 2015

Senior Member

yeah but i will first verify that whether the balance_date are falling first in Table_2 and since in table_2 start date and end (15-DEC-14 15-JAN-15) are not falling from ref_table i need to ignore 15-Jan-2015 from table_1

Jan 15 is between jan 1 and Feb but what i need to exclude is i should not even consider if any date is lesser than 01-JAN 15 of ref_table basically. The start date and end date of table_2 should be between Jan1 15 and Feb 1 15 though you can see
15-DEC-14 15-JAN-15
falls between Jan and Feb but i should not consider because the start date(i.e. 15-DEC-14) of table_2 is before Jan 1

[Updated on: Sun, 17 May 2015 03:01]

Report message to a moderator

Re: Date validation [message #637507 is a reply to message #637391]

Tue, 19 May 2015 05:02

vippysharma
Messages: 73
Registered: May 2013
Location: www

Member

rohit_shinez wrote on Sat, 16 May 2015 19:30

I will first compare the date with table_2 in this case both dates are falling between respective dates of Table_2 but need to ignore if the dates are not actually falling between 01-JAN-15 and 01-FEB-15 of reference table.

Its not fulfilling that's why i update your ref_Table.
-------------------------------------------------------------------------------------------

Create table Table_1(Balance_date date);
create table Table_2(Start_date date,End_date date);
create table Ref_table(Start_date date,end_date date);

insert into Table_1
select to_Date('15-Jan-2015','DD-MON-YYYY') from dual;

insert into Table_1
select to_date('20-Jan-2015','DD-MON-YYYY') from dual;

insert into Table_2
select to_date('15-Dec-2014','DD-MON-YYYY'), to_date('15-Jan-2015','DD-MON-YYYY') from dual;

Insert into table_2
select to_date('16-Jan-2015','DD-MON-YYYY'), to_date('16-Feb-2015','DD-MON-YYYY') from dual;

Insert into Ref_table
select to_date('01-Jan-2015','DD-MON-YYYY'), to_date('01-Mar-2015','DD-MON-YYYY') from dual;


Table_1

Balance_date
15-JAN-15
20-JAN-15

Table_2

Start_date        End_date
15-DEC-14  15-JAN-15
16-Jan-15         16-Feb-15

Ref_table
Start_date        End_date
01-JAN-15  01-Mar-15

---------------------------------------------------------------------------------------------------
declare
  cursor c1 is
    select balance_date from table_1;
  cursor c2 is
    select start_date, end_date from table_2;
  cursor c3 is
    select start_date, end_date from ref_table;
  d1 date;
  d2 date;
begin
  for i in c2 loop
    for j in c3 loop
      if (i.start_date between j.start_date and j.end_date and
         i.end_Date between j.start_date and j.end_date) then
        d1 := i.start_date;
        d2 := i.end_date;
        for k in c1 loop
          if k.balance_date between d1 and d2 then
            dbms_output.put_line('Valid: ' || k.balance_date);
          end if;
          exit when c1%notfound;
        end loop;
      end if;
      exit when c3%notfound;
    end loop;
    exit when c2%notfound;
  end loop;
end;
-----------------------------------------------------------------------------
Output-   Valid: 20-JAN-15

Report message to a moderator

Re: Date validation [message #637512 is a reply to message #637507]

Tue, 19 May 2015 07:16

cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester

Senior Member

For loops automatically exit when the last record is found so those EXIT statements are pointless.
Also those three selects can be combined into a single select that does all the comparisons.
No variables are needed. No IF statements are needed.

Report message to a moderator

Re: Date validation [message #637518 is a reply to message #637507]

Tue, 19 May 2015 09:34

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

NEVER do in PL/SQL, that which can be done in plain SQL.

Report message to a moderator

Re: Date validation [message #637543 is a reply to message #637518]

Tue, 19 May 2015 23:32

vippysharma
Messages: 73
Registered: May 2013
Location: www

Member

@cookiemonster @Blackswan .. Thanks for highlighting basic points.
& Can you please give same solution using SQL only.

Report message to a moderator

Re: Date validation [message #637556 is a reply to message #637543]

Wed, 20 May 2015 02:56

cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester

Senior Member

Why don't you give it a try? The logic isn't complicated, just write the IF statements as joins in the query.

Report message to a moderator

Re: Date validation [message #637581 is a reply to message #637556]

Wed, 20 May 2015 07:01

vippysharma
Messages: 73
Registered: May 2013
Location: www

Member

select e.balance_date
  from table_1 e,
       (select start_date d1, end_date d2
          from table_2
         where (start_date >= (select start_date from ref_table) and
               start_date <= (select end_date from ref_table) and
               end_date >= (select start_date from ref_table) and
               end_date <= (select end_date from ref_table))) f
 where e.balance_date between d1 and d2;
--------------------------------------------------------------------------------------
Result : 20-JAN-15

Report message to a moderator

Re: Date validation [message #637582 is a reply to message #637581]

Wed, 20 May 2015 07:23

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

SQL> SELECT e.balance_date
  2  FROM table_1 e,
  3    table_2 f,
  4    ref_table g
  5  WHERE e.balance_date BETWEEN f.start_date AND f.end_date
  6  AND f.start_date BETWEEN g.start_date AND g.end_date
  7  AND f.end_date BETWEEN g.start_date AND g.end_date;

BALANCE_D
---------
20-JAN-15

SQL>

Report message to a moderator

Previous Topic:	Row length of table in Oracle
Next Topic:	DELETING of RECORDS simultaneously.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jul 27 21:42:08 CDT 2026