Analyse date fields from one table

[n2nuk]

I want to produce a red amber green (RAG report) to see if clients are being seen on time.
From the date they start working with us we need to carry out a review with them every 3 months.

I have a table table that records the start date and another table that records each review they have had. The reviews need to be within 3 months from the previous review date.

for example:

Fred Smith
started on 16/12/2013
first review date:

20/12/2013 - OK Green​

second review date:

18/03/2014 - OK Green​

third review date:

21/06/2014 - should have been seen by 18/06/2014 - AMBER as seen within 5 days​

fourth review date:

01/10/2014 - should have been seen by 19/09/2014 - RED as not seen within 5 days​

fifth review date:

28/12/2014 - OK Green​

ideally i'd like to present the date in a table type format where the top row displays the anniversary date for each review and below that the actual date of the review coloured in RAG to say if the client was see within the date range.

I have 3 tables
client - this table holds the client personal information
client_programme - start_date, end_date, programme_type
client_review - review_date, review_type and review_score
there are keys to link client_programme to client_review so I am returning all the reviews that are greater than or eual to the programme.start_date.

any ideas how best to achieve this this?
Do i need to create an array to

 

[n2nuk]

Do I need to create an array containing the reviewdate and also the anniversary dates? would welcome any feedback on this, please!

 

[Charliy]

Here is one way to do this:

I'm going to assume that you are grouping by client id and the review date are in sequence.

First you need a due date formula:
if {table.client_id} <> previous({table.client_id})
then dateadd(m,3,{table.start_dt})
else dateadd(m,3,previous({table.review_dt})

then you need a Green/Abmer/Red formula

if {table.review_dt} <= {@Due Date}
then "Green"
else
if {table.review_dt} <= {@Due Date)+5
then "Amber"
else "Red