Solved

Query for validating date spans

Posted on 2011-03-04
64
720 Views
Last Modified: 2012-06-27
Experts need your help...

We have a business requirement to validate a code (master tableA) against claim dates (TRANSACTION TABLE B).

If the code dates are within claim dates we return 'TRUE' else 'FALSE'



The existing query is as follows:

SELECT   COUNT ( * ) AS COUNT
into v_cnt
  FROM    tableA
 WHERE       CODE = 'A'
         AND from_date <= TO_DATE ('12/07/2010', 'MM/DD/YYYY') -- claim FROM date
         AND TO_DATE >= TO_DATE ('01/06/2011', 'MM/DD/YYYY');    -- claim TO date


IF v_cnt > 0 RETURN TRUE ELSE  RETURN  FALSE

The problem starts here...

We have these records in master table...

TableA (MASTER TABLE)

CODES   FROM_DATE       TO_DATE
'A'      01/01/2000       12/31/2010
'A'      01/01/2011       12/31/2020


The incoming claim has these records...
TABLEB (claim table ) - Transaction

CLAIMNO    FROM_DATE     TO_DATE
1         12/12/2010       01/07/2011

So the existing  query fails as you can see in TABLEA that we have MULTIPLE spans for codes 'A'

I need help to modify the existing query so that it takes into account for MULTIPLE SPANS and return result appropriately

Let me know if I you need further explaination on this

Thanks



0
Comment
Question by:ajexpert
  • 30
  • 17
  • 15
  • +1
64 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35038674
Sorry, I'm not following.

You want to take the date range in B and check what?
0
 
LVL 14

Author Comment

by:ajexpert
ID: 35038912
Take the date range in B and check against Table A

As you see in the following query, the date range of B are checked in table A

For this existing query, it returns count = 0.

I want to modify the SQL so that it should return count > 0 as its valid record (falling in multiple date spans)

Let me know if its not clear
SELECT   COUNT ( * ) AS COUNT
into v_cnt
  FROM    tableA 
 WHERE       CODE = 'A'
         AND from_date <= TO_DATE ('12/07/2010', 'MM/DD/YYYY') -- claim FROM date
         AND TO_DATE >= TO_DATE ('01/06/2011', 'MM/DD/YYYY');    -- claim TO date

Open in new window

0
 
LVL 14

Author Comment

by:ajexpert
ID: 35038949
Just to clarify
I want to modify the SQL so that it should return count > 0 as its valid record (falling in multiple date spans in the master tableA)

 
TableA (MASTER TABLE)

CODES    FROM_DATE        TO_DATE
'A'      01/01/2000       12/31/2010
'A'      01/01/2011       12/31/2020

Open in new window

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35038993
First in the original question you showed the data in tableB as:

CLAIMNO    FROM_DATE     TO_DATE
1         12/12/2010       01/07/2011


Given the data provided for TableA, there is no single row where
from_date <= '12/07/2010' AND TO_DATE >= '01/06/2011'

Now are you really wanting to compare these rows across the MAX and MIN of ALL 'A' code rows?

If so check out below.  I might have the logic a little messed up but hopefully you'll get the idea.
SELECT   COUNT ( * ) AS COUNT
FROM   
	(select codes, max(from_date) max_from_date, min(to_date) min_to_date from tableA where codes='A' group by codes )
WHERE 
         max_from_date <= TO_DATE ('12/07/2010', 'MM/DD/YYYY') -- claim FROM date
         AND min_TO_DATE >= TO_DATE ('01/06/2011', 'MM/DD/YYYY')    -- claim TO date
/

Open in new window

0
 
LVL 14

Author Comment

by:ajexpert
ID: 35039306
Thanks slightwv I will test it.

I just want to know if your (modified) query can be further optimized.  

Reason for asking is because we have several queries to be modified on similar lines and all of these will be executed (to check the validity) for each claim being adjudicated.  We get approx 100,000 claims each day

Or other wise we are thinking to check the count  as in original query.  If count is 0 then go for your version of query to check if the code is valid between multiple date spans.

Let me know your thoughts.

0
 
LVL 31

Expert Comment

by:awking00
ID: 35060593
I'm not sure how tables A and B tie together. Does table B contain codes or table A contain claim numbers? Is there only one row in table B with one from_date and one to_date? Perhaps you could provide some sample data for the two tables and the expected output to help clarify. Also, verify the datatypes for your date fields.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35061597
I also agree with better sample data and expected results since I'm still not completely sure on the requirements.

As far as performance, you'll just need to test it against your data.  Try both ways and see which one performs better.
0
 
LVL 14

Author Comment

by:ajexpert
ID: 35062154
slightwv

I modifed your query a bit (Changed MIN and MAX)

I believe this query needs little bit modification if we have scenario in TABLEA as below

 
SELECT   COUNT ( * ) AS COUNT
FROM   
	(select codes, MIN(from_date) max_from_date, MAX(to_date) min_to_date from tableA where codes='A' group by codes )
WHERE 
         max_from_date <= TO_DATE ('12/07/2010', 'MM/DD/YYYY') -- claim FROM date
         AND min_TO_DATE >= TO_DATE ('01/06/2011', 'MM/DD/YYYY')    -- claim TO date

Open in new window

CODES    FROM_DATE        TO_DATE
------------------------------------
'A'      01/01/2000       12/01/2010
'A'      01/01/2011       12/31/2020

Open in new window

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35062294
>>I believe this query needs little bit modification if we have scenario in TABLEA as below

OK and?

You now are selecting the MIN from date which is '01/01/2000'.  Then seeing if that is '<=' '12/07/2010'  which it is, therefore it returns TRUE.

Please post more sample data, expected results and why those results are expected.

Feel free to add to the testcase below.
drop table tableA purge;
create table tableA(CODES char(1),FROM_DATE date,TO_DATE date);

insert into tableA values('A', to_date('01/01/2000','MM/DD/YYYY'), to_date('12/01/2010','MM/DD/YYYY'));
insert into tableA values('A', to_date('01/01/2011','MM/DD/YYYY'), to_date('12/31/2020','MM/DD/YYYY'));
commit;


SELECT   COUNT ( * ) AS COUNT
FROM   
	(select codes, MIN(from_date) max_from_date, MAX(to_date) min_to_date from tableA where codes='A' group by codes )
WHERE 
         max_from_date <= TO_DATE ('12/07/2010', 'MM/DD/YYYY') -- claim FROM date
         AND min_TO_DATE >= TO_DATE ('01/06/2011', 'MM/DD/YYYY')    -- claim TO date
/

Open in new window

0
 
LVL 14

Accepted Solution

by:
ajexpert earned 0 total points
ID: 35062457
Sorry slightwv,

I want to test the validation when there is a gap in timespan

So here it is

CODES    FROM_DATE        TO_DATE
------------------------------------
'A'      01/01/2000       12/01/2010
'A'      02/01/2011       12/31/2020

Open in new window

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35062570
I'm sorry as well.  We are not understanding each other.  I'm not sure why.

How are those sample dates really different from before?  The MIN from_date is still <= '12/07/2010' which makes it true.  This still makes count=1.

Based on those provided dates for tableA, what defines a gap based on the hard coded values?  This is not what I'm understanding.
0
 
LVL 14

Author Comment

by:ajexpert
ID: 35062610
No problem, I try to be clear, I apologize for not being clear

by "gap" I mean with respect to claim dates

We have start date as 12/07/2010 ending on 01/06/2011

We do not have corresponding record in tableA covering claim dates, so ideally it should be invalid.

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35062816
>>I apologize for not being clear

Don't apologize.  It's likely you are perfectly clear and I'm having problems...  ;)

OK.  I'm starting to get it.  The values in tableB cannot be between the min(from_date) and max(to_date)?

If so, try reversing the check.
drop table tableA purge;
create table tableA(CODES char(1),FROM_DATE date,TO_DATE date);

insert into tableA values('A', to_date('01/01/2000','MM/DD/YYYY'), to_date('12/01/2010','MM/DD/YYYY'));
insert into tableA values('A', to_date('01/01/2011','MM/DD/YYYY'), to_date('12/31/2020','MM/DD/YYYY'));
commit;


SELECT   COUNT ( * ) AS COUNT
FROM   
	(select codes, MIN(from_date) min_from_date, MAX(to_date) max_to_date from tableA where codes='A' group by codes )
WHERE 
         min_from_date >= TO_DATE ('12/07/2010', 'MM/DD/YYYY') -- claim FROM date
         AND max_TO_DATE <= TO_DATE ('01/06/2011', 'MM/DD/YYYY')    -- claim TO date
/

Open in new window

0
 
LVL 14

Author Comment

by:ajexpert
ID: 35062857
Awesome slightwv :)

Seems we have covered all scenarios..I will close the question once I discuss it with my lead.

Thanks a lot...you rock!!
0
 
LVL 14

Author Comment

by:ajexpert
ID: 35062882
Sorry slightwv, but this failed...

It's a valid scenario...

drop table tableA purge;

create table tableA(CODES char(1),FROM_DATE date,TO_DATE date);

insert into tableA values('A', to_date('01/01/2000','MM/DD/YYYY'), to_date('12/31/2010','MM/DD/YYYY'));

insert into tableA values('A', to_date('01/01/2011','MM/DD/YYYY'), to_date('12/31/2020','MM/DD/YYYY'));

commit;


SELECT   COUNT ( * ) AS COUNT
FROM   
    (select codes, MIN(from_date) min_from_date, MAX(to_date) max_to_date from tableA where codes='A' group by codes )
WHERE 
         min_from_date >= TO_DATE ('12/07/2010', 'MM/DD/YYYY') -- claim FROM date
         AND max_TO_DATE <= TO_DATE ('01/06/2011', 'MM/DD/YYYY')    -- claim TO date

Open in new window

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35062885
Take your time.

I'm just sorry it took us this long to get on the same page.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35062894
Give me a minute...
0
 
LVL 14

Author Comment

by:ajexpert
ID: 35062900
Sure, take your time, I really appreciate your help.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35063044
for the test values:

insert into tableA values('A', to_date('01/01/2011','MM/DD/YYYY'), to_date('12/31/2020','MM/DD/YYYY'));

doesn't the test values of '12/07/2010' and '01/06/2011' cover that gap?  01/06/2011 is between those values.
0
 
LVL 14

Author Comment

by:ajexpert
ID: 35063070

insert into tableA values('A', to_date('01/01/2011','MM/DD/YYYY'), to_date('12/31/2020','MM/DD/YYYY'));

With above data and the following query, I get count 0 which means its invalid, though there is NO gap.


SELECT   COUNT ( * ) AS COUNT
FROM   
    (select codes, MIN(from_date) min_from_date, MAX(to_date) max_to_date from tableA where codes='A' group by codes )
WHERE 
         min_from_date >= TO_DATE ('12/07/2010', 'MM/DD/YYYY') -- claim FROM date
         AND max_TO_DATE <= TO_DATE ('01/06/2011', 'MM/DD/YYYY')    -- claim TO date

Open in new window

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35063087
Please explain why.  1/6/2011 is between 1/1/2011 and 12/31/2020

0
 
LVL 14

Author Comment

by:ajexpert
ID: 35063123
Yeah, though 1/6/2011 is between 1/1/2011 and 12/31/2020, meaning its valid, the count should be > 0

This is what I expect from the query, but query returns count 0

0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 100 total points
ID: 35063172
OK.  Complete change of plan.

Take a look at:
SELECT COUNT ( * ) AS COUNT
FROM   
tableA
WHERE 
codes='A' and (
        TO_DATE ('12/07/2010', 'MM/DD/YYYY') between from_date and to_date -- claim FROM date
         or
	TO_DATE ('01/06/2011', 'MM/DD/YYYY') between from_date and to_date  -- claim TO date
)
/

Open in new window

0
 
LVL 31

Expert Comment

by:awking00
ID: 35069628
Does the attached scenario describe your situation?
scenario.txt
0
 
LVL 14

Author Comment

by:ajexpert
ID: 35070622
@awking00:
I have modified your attachment with my comments.

Sorry slightwv, My lead told me something which I believe you might have been thinking right from beginning

The entire claim span dates i.e. 12/07/2010 to 01/07/2011 should be present in TABLEA, either as single record or multiple record (spans).

Partial date spans (as in comment#35063123) are considered to be invalid, i.e. the count should return 0

I tried all the queries but somehow I feel it still needs modification ee-span.txt
0
 
LVL 14

Author Comment

by:ajexpert
ID: 35073609
slightwv and awking

Shall I open another question relating to this one?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35074891
>>Shall I open another question relating to this one?

Personally I don't think so.  I think it's still the same question.

The fact that your lead changed things doesn't matter to me because I still really don't understand the original requirements.

I'll see if I can take a look later to see if the latest posts help me understand.
0
 
LVL 14

Author Comment

by:ajexpert
ID: 35074953
Thanks slightwv.

Let me know if I can make it more clear by providing examples.

In nutshell we are validating for claim dates - 12/07/2010 --01/06/2011 against tableA

If we have above claim dates covered in tableA (entirely) as 1 record or multiple records its valid scenario
0
 
LVL 14

Author Comment

by:ajexpert
ID: 35083240
slightwv and awking00

I believe if we can find out the missing "span" or the gap in tableA and then find out if claim dates fall within the gap - If Yes, - Invalid Scenario, If No- Valid Scenario

Is there any other way to get it done easily?

May be a function will also help if not query
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35084816
>>If we have above claim dates covered in tableA (entirely)

It's getting clearer (I think).

So you want to make sure ALL the days in the date range in tableB, 12/07/2010 --01/06/2011, has overlapping dates in tableA in one or more entries?

While I wait for confirmation, let me see if I can come up with that query.
0
 
LVL 14

Author Comment

by:ajexpert
ID: 35084851
Yes, you got it right.

All date ranges in tableb should be covered in tablea with one or more entries
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35085866
whew.  That took a long time to understand...  :)

Let's see what I (or the others) can come up with.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 14

Author Comment

by:ajexpert
ID: 35086831
No problem, seems I need to improve on my communication skills

Well, how about my comment http://35083240

As said, I am happy with function as well, if its too difficult with SQL
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 35090874
I don't understand why scenario 2 in awking00's file isn't valid.


Every day from 1/1/2000 to 12/31/2020 is covered by the 2 rows in A

so why is the 12/12/2010-1/7/2011 not valid?

There is no gap, but there is a period that is double-covered.

Is double coverage the same as a gap?  An illegal timespan?
0
 
LVL 14

Author Comment

by:ajexpert
ID: 35094275
Sean,

Scenario 2 per awking is


We are missing 1/1/2011 to 1/31/2011 in Table A its a gap and table B dates (12/12/2010-1/7/2011) partially fall in gap so its not valid


Record#
1			From		To
			|----------------|
		      1/1/2000	     12/31/2010
2						From		To
						  |----------------|
					       2/1/2011	     12/31/2020

Open in new window

0
 
LVL 73

Expert Comment

by:sdstuber
ID: 35094297
oops,  I looked at the "visual" queues,  not the actual dates.

so, overlapping ranges DO count as valid?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35094422
>>Well, how about my comment http://35083240

I haven't forgotten about this.  I'm just not seeing the 'easy' way and the 'hard' way I came up with ran FOREVER so I don't even want to post it...

I'll see if I can free up some time later today to continue working on it.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 35096264
date range consolidation is a tricky problem within the constraints of sql.

The most compact way I can think to write it is something like this...


SELECT b.*
  FROM tableb b
 WHERE todate - fromdate =
           (SELECT COUNT(DISTINCT b.fromdate + COLUMN_VALUE) -1
              FROM tablea a,
                   TABLE(SELECT     COLLECT(LEVEL - 1)
                               FROM DUAL
                         CONNECT BY b.fromdate + LEVEL - 1 <= b.todate)
             WHERE     b.fromdate <= a.todate
                   AND b.todate >= a.fromdate
                   AND b.fromdate + COLUMN_VALUE BETWEEN a.fromdate AND a.todate)

Open in new window



but, I'm not fond of that because it's sort of an index killer
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35096538
It's not even my question and you had me all excited...

Based on my understanding of the requirements, that doesn't work.

From the sample below:  1/1/2011 is in the range in tableB but is not covered in a range in tableA.

(At least I don't think so...I've been looking at this on and off for a couple days).
drop table tableA purge;
create table tableA(CODES char(1),FROMDATE date,TODATE date);

----fail because 1/1/2011 isn't covered
insert into tableA values('A', to_date('01/01/2000','MM/DD/YYYY'), to_date('12/31/2010','MM/DD/YYYY'));
insert into tableA values('A', to_date('01/02/2011','MM/DD/YYYY'), to_date('12/31/2020','MM/DD/YYYY'));
commit;

drop table tableB purge;
create table tableB(FROMDATE date,TODATE date);
insert into tableB values(to_date('12/07/2010'),to_date('01/06/2011'));
commit;

SELECT b.*
  FROM tableb b
 WHERE todate - fromdate =
           (SELECT COUNT(DISTINCT b.fromdate + COLUMN_VALUE) -1
              FROM tablea a,
                   TABLE(SELECT     COLLECT(LEVEL - 1)
                               FROM DUAL
                         CONNECT BY b.fromdate + LEVEL - 1 <= b.todate)
             WHERE     b.fromdate <= a.todate
                   AND b.todate >= a.fromdate
                   AND b.fromdate + COLUMN_VALUE BETWEEN a.fromdate AND a.todate)
/

Open in new window

0
 
LVL 73

Expert Comment

by:sdstuber
ID: 35097237
I don't get the B row returned from your example


I had to change your to_date calls to add the date format other than that I ran your example "as is"

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35097348
>>I don't get the B row returned from your example

As far as I understand the requirements, you should.  The values in B are not entirely covered by the date ranges in A.

1/1/2011 is a date in the range from B.  That date is not covered by any ranges in A.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 35097474
I think we're supposed to find all B where B is completely covered in A

Since every day of B except 1/1/2011 is covered we leave it out
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 35097518
ajexpert,  clearly there is some confusion.

Please provide more extensive samples  and expected results.

Just data, no words
0
 
LVL 14

Author Comment

by:ajexpert
ID: 35097555
Ok, please give me some time, I will provide the sample data with valid and invalid scenario

Appreciate your time
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 35097717
assuming my interpretation of requirements is correct  here's another variation.  This should scale better

I'm assuming A is small and B is large


SELECT b.*
  FROM tableb b,
       (SELECT   fromdate, MAX(todate) todate
            FROM (SELECT   MIN(fromdate) fromdate, todate
                      FROM (SELECT     fromdate, CONNECT_BY_ROOT (todate) todate
                                  FROM tablea
                            CONNECT BY PRIOR fromdate > fromdate AND todate >= PRIOR fromdate)
                  GROUP BY todate)
        GROUP BY fromdate) a
 WHERE b.fromdate >= a.fromdate AND b.todate <= a.todate

Open in new window

0
 
LVL 14

Author Comment

by:ajexpert
ID: 35097778
I have attached possible scenario's in excel sheet.

Let me know if any questions ee-help.xls
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 35098054
I thought you had lots of B records?

You aren't trying to look up the B's?  You have only one date range to validate?


this query, mod from previous one will show all B's that PASS.  


SELECT b.*
  FROM tableb b,
       (SELECT   fromdate, MAX(todate) todate
            FROM (SELECT   MIN(fromdate) fromdate, todate
                      FROM (SELECT     fromdate, CONNECT_BY_ROOT (todate) todate
                                  FROM tablea
                            CONNECT BY PRIOR fromdate > fromdate AND PRIOR fromdate <= todate + 1)
                  GROUP BY todate)
        GROUP BY fromdate) a
 WHERE b.fromdate >= a.fromdate AND b.todate <= a.todate;

Open in new window



If you are looking for a function that accepts a single from/to and validates it returning PASS/FAIL
that's even easier


CREATE OR REPLACE FUNCTION validaterange(p_from IN DATE, p_to IN DATE)
    RETURN VARCHAR2
IS
    v_dummy   NUMBER;
BEGIN
    SELECT NULL
      INTO v_dummy
      FROM (SELECT   fromdate, MAX(todate) todate
                FROM (SELECT   MIN(fromdate) fromdate, todate
                          FROM (SELECT     fromdate, CONNECT_BY_ROOT (todate) todate
                                      FROM tablea
                                CONNECT BY PRIOR fromdate > fromdate AND PRIOR fromdate <= todate + 1)
                      GROUP BY todate)
            GROUP BY fromdate)
     WHERE p_from >= fromdate AND p_to <= todate AND ROWNUM = 1;

    RETURN 'PASS';
EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
        RETURN 'FAIL';
END;

Open in new window

0
 
LVL 14

Author Comment

by:ajexpert
ID: 35098261
Though we have lot of B records, we have to validate for EACH claim against entire set of TableA and stamp as valid or invalid.

Each claim will have only 1 FROM_DATE and TO_DATE

0
 
LVL 73

Expert Comment

by:sdstuber
ID: 35098458
you "could" call the function for every row in B


more efficient would be to use the query to find all rows that PASS in one step.
everything not returned by that function is FAIL
0
 
LVL 14

Author Comment

by:ajexpert
ID: 35098530
Each claim will pass through different validation checks with different set of tables, tableA is one of them.

So we will process claim one by one.

I will try your query and let you know

Thanks a lot
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 400 total points
ID: 35108164
if you'll be iterating row by row, then this might work out better
CREATE OR REPLACE FUNCTION validaterange(p_from IN DATE, p_to IN DATE)
    RETURN VARCHAR2
IS
    v_dummy   NUMBER;
BEGIN
    SELECT null
      INTO v_dummy
      FROM (SELECT     LEVEL - 1 n
                  FROM DUAL
            CONNECT BY p_from + LEVEL - 1 <= p_to)
     WHERE NOT EXISTS
               (SELECT NULL
                  FROM tablea a
                 WHERE p_from + n BETWEEN a.fromdate AND a.todate)
           AND ROWNUM = 1;

    RETURN 'FAIL';
EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
        RETURN 'PASS';
END;

Open in new window

0
 
LVL 14

Author Comment

by:ajexpert
ID: 35108185
Yes, we will iterate row by row.  For each row in table B we want to validate against tableA and get the result.

I will try your function and let you know.

Thanks!!
0
 
LVL 14

Author Comment

by:ajexpert
ID: 35139738
Thanks Sean, your query works for all scenarios.

Its bit difficult for me to digest the logic, can you please explain in simple words?
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 35139793
which one are you using?
0
 
LVL 14

Author Comment

by:ajexpert
ID: 35139847
this one http:#a35108164
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 400 total points
ID: 35140156
This part generates numbers 0,1,2,3,4...  for every day in the from/to range
if from and to are both today you get 0
if from is today and to is tomorrow you get 0,1  etc
 (SELECT     LEVEL - 1 n
                  FROM DUAL
            CONNECT BY p_from + LEVEL - 1 <= p_to)

Open in new window


this part  says, for every N add it to to p_from (thus generating every day in the range)
and check if that day is between table A's from/to range
if it is then it fails the NOT EXISTS clause and that day is excluded
only a day that is NOT between A's from/to will be returned
NOT EXISTS
               (SELECT NULL
                  FROM tablea a
                 WHERE p_from + n BETWEEN a.fromdate AND a.todate)

Open in new window


this filter says to only return one row out of any found
the reason being we don't really care how many days in the range are missing from the A rows, we just care if any are missing.  1 day, 2 days, 70 days isn't particuarly important
and rownum = 1

Open in new window


So putting it all together you get,  find one day in your range that isn't in A.

If you find one then return 'FAIL'
If you don't find one then return 'PASS'

0
 
LVL 14

Author Comment

by:ajexpert
ID: 35140595
Awesome explaination Thanks a lot !!
0
 
LVL 14

Author Comment

by:ajexpert
ID: 35140796
How to modify the function quey to return 'PASS' when there is no exception and 'FAIL' at exception?

Will this work?

CREATE OR REPLACE FUNCTION validaterange(p_from IN DATE, p_to IN DATE)
    RETURN VARCHAR2
IS
    v_dummy   NUMBER;
BEGIN
    SELECT null
      INTO v_dummy
      FROM (SELECT     LEVEL - 1 n
                  FROM DUAL
            CONNECT BY p_from + LEVEL - 1 <= p_to)
     WHERE  EXISTS
               (SELECT NULL
                  FROM tablea a
                 WHERE p_from + n BETWEEN a.fromdate AND a.todate)
           AND ROWNUM = 1;

    RETURN 'PASS';
EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
        RETURN 'FAIL';
END;

Open in new window

0
 
LVL 73

Expert Comment

by:sdstuber
ID: 35140945
NO!

that will give false positives

if any one day in your range is found then it will return PASS even if every other day in the range is left uncovered
0
 
LVL 14

Author Comment

by:ajexpert
ID: 35140962
Actually if I write the valid records that fall into exception block, someone will raise questions.

Can you please modify it so that all records falling in exception block are invalid?

I wish I could exceed points more than 500
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 35140979
I don't understand what you are asking

I think I've answered the original question.

if you want a new function to do something differently please open a new question and provide sample data and expected results
0
 
LVL 14

Author Comment

by:ajexpert
ID: 35152861
What I mean to say is that in your function, Scenario's that are valid  (i.e. 'PASS')  ONLY if they fall in exception block.

I was thinking to modify the function in a to work in other manner,  so as to RETURN 'FAIL' only when they fall in exception block
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 35153976
>>>Scenario's that are valid  (i.e. 'PASS')  ONLY if they fall in exception block.


correct, the function looks for dates that aren't covered.  If it finds any then FAIL, if it can't find any then PASS
0
 
LVL 14

Author Closing Comment

by:ajexpert
ID: 35178859
I wish I could exceed the points more than 500

Thanks slightwv and sean for your assitance, patience and support.

I will open another question similar to this if I have other questions.

Thanks a lot once again to both of you
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now