From: seth.esrig@brent.uucp (Seth Esrig) 
Newsgroups: comp.databases
Subject: Pls help: algorithm wante
Date: 3 Jan 93 12:44:00 GMT
Distribution: world
Organization: The Brentwood BBS Harrison, NY  914-835-7898
Reply-To: seth.esrig@brent.uucp (Seth Esrig) 

DV>I have an application that keeps track of subcontracts issued to outsiders.
DV>For every subcontract, we have to receive various rate agreements.  The
DV>subcontracts we issue usually span across multi years, but the rate
DV>agreement we receive is always for one year, and renewed every year for the
DV>duration of the contract.  The rate agreements can be provisional or
DV>final.  We should at least have a provisional agreement on file, in order
DV>to issue subcontract.  The rate agreement can be according the vendor's
DV>fiscal year.

DV>When the user enters the subcontract start and end dates, the system has to
DV>check the available rate agreements for the period, and summarize how the
DV>various rate agreements cover the subcontract.

DV>For example, I have a subcontract for the period 01/01/92 - 12/31/94 (3
DV>years).  I have the following rate agreements:

DV>Final  07/01/91 - 06/30/92
DV>Final  07/01/92 - 06/30/93
DV>Provl  07/01/93 - 12/31/93
DV>Final  01/01/94 - 06/30/94

DV>The coverage report should look as follows:

DV>Subcontract Period: 01/01/92 - 12/31/94

DV>How Covered ?

DV>01/01/92 - 06/30/92 Final
DV>07/01/92 - 06/30/93 Final
DV>07/01/93 - 12/31/93 Provl
DV>01/01/94 - 06/30/94 Final
DV>07/01/94 - 12/31/94 Uncovered

DV>My problem:
DV>I have all the individual agreements on a database.  When the user enters a
DV>new subcontract, the system compares the contract period with the agreement
DV>period to establish coverage or otherwise.  I am using foxpro, and a command

I haven't been using FoxPro for a couple of years, so I could only give
you my R:Base program, using database CONTR and table AGREEMENTS. It
works, and if you know anyone who knows both SQL or R:Base, and Foxpro,
maybe they can create a Foxpro translation. I saw the posted FP code,
and was surprised how compact and elegant it is. Maybe the database
gurus raves were right:

cls
set messages off
connect contr
set var vcont_start_date date
set var vcont_end_date date
fillin vcont_start_date=8 using ' subcontract start date: '
fillin vcont_end_date=8 using ' subcontract end date: '

create view subagree as +
  select agree_start_date, agree_end_date, status +
    from agreements +
      where agree_start_date < .vcont_end_date and +
            agree_end_date > .vcont_start_date +
        order by agree_start_date

declare datevars cursor for select agree_start_date, +
  agree_end_date, status +
    from subagree
open datevars

set var vstart = .vcont_start_date
set var vend = .vcont_end_date
set var vrow = 1
cls
while sqlcode = 0 then
   fetch datevars into vagree_start_date, vagree_end_date, +
     vagree_status

   if sqlcode = 100 then
     if vstart < .vcont_end_date then
       write .vstart at .vrow, 5
       write .vcont_end_date at .vrow, 15
       write 'Uncovered at End' at .vrow, 25
     endif
     break
   endif

   if vagree_start_date > .vstart then
      write .vstart at .vrow, 5
      set var vst = (.vagree_start_date - 1)
      write .vst at .vrow, 15
      write 'Uncovered' at .vrow, 25
      set var vrow = (.vrow + 1)
   endif

   if vagree_start_date < .vcont_start_date then
      write .vcont_start_date at .vrow, 5
   else
      write .vagree_start_date at .vrow, 5
   endif
   if vagree_end_date > .vcont_end_date then
      write .vcont_end_date at .vrow, 15
   else
      write .vagree_end_date at .vrow, 15
   endif
   write .vagree_status at .vrow, 25

   set var vrow = (.vrow + 1)
   set var vstart = (.vagree_end_date + 1)
endwhile

pause
drop cursor datevars
drop view subagree

---
 . SLMR 2.1a . Real programmers use CS majors at $4.25/hr.