Hi all,
I have a database containing tables called ACCOUNT, CONTRACT & CONTRACTDETAILS. I have two tables in QV where one flags if contracts are going to expire within the next 60 days and another that shows all the new contracts currently in the process of being processed.
What I would like to do is to place a flag in the "Contract < 60 days" table if there is a similarly named contract currently being renewed. All contracts (current & upcoming) are stored in the CONTRACTS table in the database and the flag for upcoming projects (Status = 'Planned') is stored in the CONTRACTDETAILS table - all other info such as Start/End Dates, etc. are stored in the CONTRACT table. In QV, the tables look similar to below:
Contract < 60 Days:
ACCOUNT | CONTRACT REF | ENDDATE |
---|
Duffy's Shop | DUF - Support 2013-2014 | 01/04/2014 |
Sharp Supermarket | SHP - Maintenance 2013-2014 | 07/04/2014 |
Upcoming Contracts:
ACCOUNT | CONTRACT REF | STATUS | STARTDATE |
---|
Duffy's Shop | DUF - Support 2014-2015 | Planned | 02/04/2014 |
What I'm looking to accomplish is to read all values in the CONTRACTS table and flag if there is a similar named CONTRACT REF for the same account, where the Start Date (upcoming) >= End Date (current).