• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 765
  • Last Modified:

Query for validating date spans

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
ajexpert
Asked:
ajexpert
  • 30
  • 17
  • 15
  • +1
4 Solutions
 
slightwv (䄆 Netminder) Commented:
Sorry, I'm not following.

You want to take the date range in B and check what?
0
 
ajexpertAuthor Commented:
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
 
ajexpertAuthor Commented:
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
slightwv (䄆 Netminder) Commented:
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
 
ajexpertAuthor Commented:
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
 
awking00Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
ajexpertAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
ajexpertAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
ajexpertAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
ajexpertAuthor Commented:
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
 
ajexpertAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
Take your time.

I'm just sorry it took us this long to get on the same page.
0
 
slightwv (䄆 Netminder) Commented:
Give me a minute...
0
 
ajexpertAuthor Commented:
Sure, take your time, I really appreciate your help.
0
 
slightwv (䄆 Netminder) Commented:
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
 
ajexpertAuthor Commented:

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
 
slightwv (䄆 Netminder) Commented:
Please explain why.  1/6/2011 is between 1/1/2011 and 12/31/2020

0
 
ajexpertAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
awking00Commented:
Does the attached scenario describe your situation?
scenario.txt
0
 
ajexpertAuthor Commented:
@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
 
ajexpertAuthor Commented:
slightwv and awking

Shall I open another question relating to this one?
0
 
slightwv (䄆 Netminder) Commented:
>>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
 
ajexpertAuthor Commented:
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
 
ajexpertAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
ajexpertAuthor Commented:
Yes, you got it right.

All date ranges in tableb should be covered in tablea with one or more entries
0
 
slightwv (䄆 Netminder) Commented:
whew.  That took a long time to understand...  :)

Let's see what I (or the others) can come up with.
0
 
ajexpertAuthor Commented:
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
 
sdstuberCommented:
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
 
ajexpertAuthor Commented:
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
 
sdstuberCommented:
oops,  I looked at the "visual" queues,  not the actual dates.

so, overlapping ranges DO count as valid?
0
 
slightwv (䄆 Netminder) Commented:
>>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
 
sdstuberCommented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
sdstuberCommented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
sdstuberCommented:
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
 
sdstuberCommented:
ajexpert,  clearly there is some confusion.

Please provide more extensive samples  and expected results.

Just data, no words
0
 
ajexpertAuthor Commented:
Ok, please give me some time, I will provide the sample data with valid and invalid scenario

Appreciate your time
0
 
sdstuberCommented:
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
 
ajexpertAuthor Commented:
I have attached possible scenario's in excel sheet.

Let me know if any questions ee-help.xls
0
 
sdstuberCommented:
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
 
ajexpertAuthor Commented:
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
 
sdstuberCommented:
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
 
ajexpertAuthor Commented:
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
 
sdstuberCommented:
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
 
ajexpertAuthor Commented:
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
 
ajexpertAuthor Commented:
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
 
sdstuberCommented:
which one are you using?
0
 
ajexpertAuthor Commented:
this one http:#a35108164
0
 
sdstuberCommented:
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
 
ajexpertAuthor Commented:
Awesome explaination Thanks a lot !!
0
 
ajexpertAuthor Commented:
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
 
sdstuberCommented:
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
 
ajexpertAuthor Commented:
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
 
sdstuberCommented:
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
 
ajexpertAuthor Commented:
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
 
sdstuberCommented:
>>>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
 
ajexpertAuthor Commented:
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 30
  • 17
  • 15
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now