From: g9utxu@fnma.COM (Tanin Uthayanaka)
Subject: Re: Pls help: algorithm wanted (xbase, pref foxpro)
Reply-To: g9utxu@fnma.COM
Organization: Fannie Mae
Date: Wed, 30 Dec 1992 21:40:31 GMT

In article 725742702@ortta.umn.edu, durai@ortta.umn.edu (Durai Venkatasubramanian) writes:
>I need an algorithm to accomplish a date comparison task.  Any help will be 
>appreciated and gratefully remembered for life.
>
>I have an application that keeps track of subcontracts issued to outsiders.  
>For every subcontract, we have to receive various rate agreements.  The 
>subcontracts we issue usually span across multi years, but the rate 
>agreement we receive is always for one year, and renewed every year for the 
>duration of the contract.  The rate agreements can be provisional or 
>final.  We should at least have a provisional agreement on file, in order 
>to issue subcontract.  The rate agreement can be according the vendor's 
>fiscal year.
>
>When the user enters the subcontract start and end dates, the system has to 
>check the available rate agreements for the period, and summarize how the 
>various rate agreements cover the subcontract.
>
>For example, I have a subcontract for the period 01/01/92 - 12/31/94 (3 
>years).  I have the following rate agreements:
>
>Final  07/01/91 - 06/30/92
>Final  07/01/92 - 06/30/93
>Provl  07/01/93 - 12/31/93
>Final  01/01/94 - 06/30/94	
>	
>The coverage report should look as follows:
>
>Subcontract Period: 01/01/92 - 12/31/94
>
>How Covered ?  
>
>01/01/92 - 06/30/92 Final 
>07/01/92 - 06/30/93 Final
>07/01/93 - 12/31/93 Provl
>01/01/94 - 06/30/94 Final
>07/01/94 - 12/31/94 Uncovered
>
>
>My problem:
>
>I have all the individual agreements on a database.  When the user enters a 
>new subcontract, the system compares the contract period with the agreement 
>period to establish coverage or otherwise.  I am using foxpro, and a command 
>line like "for between(contractdate, agreement_start_date, 
>agreement_end_date)" or "for between(agreement_end_date, 
>contract_start_date, contract_end_date)".  I am not getting the desired 
>result.  How do I properly tabulate the results of the comparison ?  What am 
>I doing wrong ?  
>
>I apologize if I am asking too much.  If someone has a solution for similar 
>problem, and is willing to share, I will be very thankful.
>


I will attempt to help.

First, I'm assuming that you have a rate database (lets call it RATE.DBF) with the 
folowing field:

	1 RATE
	2 START_DATE
	3 END_DATE
	4 STATUS

Second, the user enters dStart and dEnd

Third, the program would do the following:

@ 10,10 SAY "Subcontract Period: "+DTOC(dStart)+" - "+DTOC(dEnd)
@ 11,0  SAY ""
USE RATE
INDEX ON START_DATE TO START
SET SOFTSEEK ON
SEEK dStart
IF ! EOF()
   IF dStart < RATE->START_DATE
      @ ROW()+1,15 SAY DTOC(dStart)+" - "+DTOC(RATE->START_DATE-1)
      @ ROW(),  30 SAY "Uncovered"
   ENDIF
   DO WHILE RATE->START_DATE >= dStart
      IF RATE->END_DATE <= dEnd
         @ ROW()+1,15 SAY DTOC(RATE->START_DATE)+" - "+DTOC(RATE->END_DATE)
         @ ROW(),  30 SAY RATE->STATUS
      ELSE
         @ ROW()+1,15 SAY DTOC(RATE->END_DATE+1)+" - "+DTOC(dEnd)
         @ ROW(),  30 SAY "Uncovered"
         EXIT
      ENDIF
      SKIP +1
      IF EOF()
         SKIP -1
         @ ROW()+1,15 SAY DTOC(RATE->END_DATE+1)+" - "+DTOC(dEnd)
         @ ROW(),  30 SAY "Uncovered"
         EXIT
      ENDIF
   ENDDO
ELSE
   @ ROW()+1,15 SAY DTOC(dStart)+" - "+DTOC(dEnd)
   @ ROW(),  30 SAY "Uncovered"
ENDIF
SET SOFTSEEK OFF
SET INDEX TO
USE

Hope this helps, I know it's hard coded but the algorithm is there.

TU

P.S. Hope there's no bug I took only 10 minutes to write the code and didn't have 
     time to run the program