Toad World® Forums

Flattening dates of service table


#1

flattening dates of service table


#2

I have a table where I have customer id, a start date and end date in it. There
are multiple rows for the same customer id.

Customerid StartDate EndDate

1 1/1/09 12/31/09

2 1/1/09 06/30/09

3 1/1/09 06/30/09

3 07/01/09 09/31/09

3 10/01/09 12/31/09

4 02/01/09 04/30/09

4 07/31/09 12/31/09

5 06/01/09 09/30/09

5 11/01/09 11/30/09

I was hoping to flattening this table so it would look for something like this.

CustomerId StartDate EndDate1 BeginDate1 EndDate

1 1/1/09 12/31/09

2 1/1/09 06/30/09

3 1/1/09 12/31/09

4 02/01/09 04/30/09 07/31/09 12/31/09

5 06/01/09 09/30/09 11/01/09 11/30/09

I need to see if the customer’s request is in the date of service. I have
been playing around the with a decode statement, but haven’t figure out
the best way to do this. I want something like this.

CustomerId StartDate EndDate1 BeginDate1 EndDate DateOfTest Eligable

1 1/1/09 12/31/09 05/12/09 Yes

2 1/1/09 06/30/09 07/01/09 No

3 1/1/09 12/31/09 01/01/09 Yes

4 02/01/09 04/30/09 07/31/09 12/31/09 06/01/09 No

5 06/01/09 09/30/09 11/01/09 11/30/09 12/15/09 No

Thanks for your help

James


#3

Hi James,

I have a table where I have customer id, a start date and end date
in it.

There are multiple rows for the same customer id.
Is it possible for a customer to have a gap between an end date and the
following begin date?

If not, then this might give you what you need:

SELECT ‘In Service’ FROM (
SELECT ID,min(begin_date) AS begin_date, max(end_date) AS end_date
FROM norm
WHERE ID = :some_id
GROUP BY ID
)
WHERE to_date(:some_date_string, ‘mm/dd/yyyy’) BETWEEN begin_date AND
end_date;

You obviously have to supply the user id and the date to be tested.

If gaps are allowed, and from the data for ID 4, it appears that way, it
gets more difficult.

I was hoping to flattening this table so it would look for something
like this.
The problems with flattening in this way is the fact that you need a
DECODE for each entry across the page. If you find that ID 4 has more
than one gap, you will need an extra DECODE for each gap. Unless you
want to end up with a huge pile of DECODEs you may need to restrict the
number of gaps an ID can have.

If this isn’t possible, then your SQL statement will either have far too
many DECODEs or not enough. The latter case could mean that a date that
is in service is flagged as out.

I suspect that there’s probably an ‘analytic’ solution to this problem
of an unknow number of gaps, but as I’m not all that great at analytics,
I’ll have to resort to PL/SQL:

CREATE OR REPLACE FUNCTION in_service(pId IN norm.ID%TYPE, pDate IN
date) RETURN varchar2 IS
BEGIN
FOR x IN (SELECT begin_date, end_date
FROM norm
WHERE ID = pId) LOOP
IF (pDate BETWEEN x.begin_date AND x.end_date) THEN
RETURN ‘In Service’;
END IF;
END LOOP;
RETURN NULL;
END;

(I’ve used a table called NORM in the parameters and the SELECT. You’ll
probably have a different table name!)

If the function returns NULL, the date is not in service. If it returns
‘In Service’ then it is. Regardless of how ever many gaps you allow in
the data.

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk


#4

If your requirement is “ I need to see if the customer’s request is
in the date of service ”, then I don’t see a need to flatten the
table. Assuming you’ve got a SERVICE table with customerid, startdate, and
enddate, and a TESTDATA table with customerid and dateoftest, then

select tst . customerid

, svc . startdate

, svc . enddate

, tst . dateoftest

, case when svc . customerid is null then ‘No’ else ‘Yes’ end eligible

from testdata tst

left join service svc

on tst . customerid = svc . customerid

and tst . dateoftest between svc . startdate and svc . enddate

order by tst . customerid

;

gives

CUSTOMERID STARTDATE ENDDATE DATEOFTEST ELIGIBLE

1 1/1/2009 12/31/2009 5/12/2009 Yes

2 7/1/2009 No

3 1/1/2009 6/30/2009 1/1/2009 Yes

4 6/1/2009 No

5 12/13/2009 No

(This assumes that periods of service will not overlap for a given customer. If
customer 1 had a service record for 1/1/09 through 12/31/09 and also one for
5/1/09 through 12/31/10, then my query will return two rows for that customer,
showing the two different service periods that covered the date of test.)

If it is important to also show the nearest dates of service for those that were
ineligible, that can be done too, but it would be more complicated.

Nate Schroeder

IT Commercial Technical Services - Data Management Team

Monsanto Company

800 N. Lindbergh Blvd. G3WI - Saint Louis, MO - 63167

314-694-2592
image001.jpeg


#5

If it is important to also show the nearest dates of service for those that
were ineligible, that can be done too, but it would be more complicated.

For the fun of it, I took the above as a challenge. Here is what I worked out,
for those who like convoluted SQL (there could easily be easier ways). If the
test was eligible, it displays the start and end dates of the service record
that shows that it was eligible. If the test was not eligible, it shows the
start and end dates for the previous service record (if any) and the following
service record (if any).

select customerid

, max( eligible_startdate ) eligible_startdate

, max( eligible_enddate ) eligible_enddate

, case when max( eligible ) = ‘No’ then max( previous_startdate ) else null end
previous_startdate

, case when max( eligible ) = ‘No’ then max( previous_enddate ) else null end
previous_enddate

, case when max( eligible ) = ‘No’ then max( following_startdate ) else null end
following_startdate

, case when max( eligible ) = ‘No’ then max( following_enddate ) else null end
following_enddate

, dateoftest

, max( eligible ) eligible

from (

select customerid

, case when dateoftest between startdate and enddate then startdate else null
end eligible_startdate

, case when dateoftest between startdate and enddate then enddate else null end
eligible_enddate

, case when enddate = lastbefore then startdate else null end previous_startdate

, case when enddate = lastbefore then enddate else null end previous_enddate

, case when startdate = firstafter then startdate else null end
following_startdate

, case when startdate = firstafter then enddate else null end following_enddate

, dateoftest

, case when dateoftest between startdate and enddate then ‘Yes’ else ‘No’ end
eligible

from (

select tst . customerid

, tst . dateoftest

, svc . startdate

, svc . enddate

, case when svc . enddate

max( svc . enddate ) over (partition by svc . customerid , case when svc .
enddate

else null end lastbefore

, case when svc . startdate > tst . dateoftest then

min( svc . startdate ) over (partition by svc . customerid , case when svc .
startdate > tst . dateoftest then 1 else 0 end)

else null end firstafter

from testdata tst

left join service svc

on tst . customerid = svc . customerid

)

)

group by customerid , dateoftest

order by customerid , dateoftest

;

It gives this result, with the sample data:

CUSTOMERID

ELIGIBLE_STARTDATE

ELIGIBLE_ENDDATE

PREVIOUS_STARTDATE

PREVIOUS_ENDDATE

FOLLOWING_STARTDATE

FOLLOWING_ENDDATE

DATEOFTEST

ELIGIBLE

1

1/1/2009

12/31/2009

5/12/2009

Yes

2

1/1/2009

6/30/2009

7/1/2009

No

3

1/1/2009

6/30/2009

1/1/2009

Yes

4

2/1/2009

4/30/2009

7/31/2009

12/31/2009

6/1/2009

No

5

11/1/2009

11/30/2009

12/13/2009

No

Nate Schroeder

IT Commercial Technical Services - Data Management Team

Monsanto Company

800 N. Lindbergh Blvd. G3WI - Saint Louis, MO - 63167

314-694-2592

This e-mail message may contain privileged and/or confidential information, and
is intended to be received only by persons entitled to receive such information.
If you have received this e-mail in error, please notify the sender immediately.
Please delete it and all attachments from any servers, hard drives or any other
media. Other use of this e-mail by you is strictly prohibited.

All e-mails and attachments sent and received are subject to monitoring, reading
and archival by Monsanto, including its subsidiaries. The recipient of this
e-mail is solely responsible for checking for the presence of “Viruses” or other
“Malware”. Monsanto, along with its subsidiaries, accepts no liability for any
damage caused by any such code transmitted by or accompanying this e-mail or any
attachment.


#6

Thanks, it is nice to have another set of eyes looking at it. Sometimes you
look at a problem from one direction and ignore an easier solution.

James