NetNews Usenet Archive 1992 #31/NN_1992_31.iso/spool/comp/database/8732

Text File | 1992-12-30 | 3.8 KB | 124 lines

Newsgroups: comp.databases
Path: sparky!uunet!almserv!mimas!g9utxu
From: [e-mail] (Tanin Uthayanaka)
Subject: Re: Pls help: algorithm wanted (xbase, pref foxpro)
Message-ID: <[e-mail]>
Sender: [e-mail]
Nntp-Posting-Host: mimas
Reply-To: [e-mail]
Organization: Fannie Mae
References: <[e-mail]>
Date: Wed, 30 Dec 1992 21:40:31 GMT
Lines: 110

In article [e-mail], [e-mail] (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