Compare date fields

[n2nuk]

What is the best way to compare a set of dates from the same field/table?

my table looks like this

review_ref --- Client_ref --- review_date
1 ---- 100 --- 18/01/2014
2 ---- 100 --- 27/04/2014
3 ---- 100 --- 05/06/2014
4 ---- 100 --- 11/09/2014
5 ---- 100 --- 21/09/2014
6 ---- 124 --- 14/09/2013
7 ---- 124 --- 18/12/2013
8 ---- 125 --- 11/11/2013
9 ---- 125 --- 01/01/2014
10 --- 125 --- 16/02/2014
and so on..

I want to be able to return the number of days difference and apply conditional formatting if the count is greater than 30 days.

 

[PeterDimitrov]

Which dates do you want to compare? Min/Max per client , difference between consecutive dates or any date with any date ?

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports, SSRS and Dynamic Dashboards.

 

[n2nuk]

I want to compare the first date against programme_start date that is recorded in a another table and all the other dates against the next assessment date.

I have produced this formula that sort of gives me what i want:

if previous ({client._Client_Ref}) <> {client._Client_Ref} then
datediff("d",{review_programme.Start_Date}, {assessment.Assessment_Date})
else
{assessment.Assessment_Date}-previous({assessment.Assessment_Date})

The problem I have is that where a client has more than one active programme, the date is calculated from the previous assessment date rather than the programme date.

 

[PeterDimitrov]

You need to add programme identifier in order to distinguish the records by programme not by client. In case programme idenifier is not unique, you need to include both client and programme.

This kind of tasks usually are handled on the data level, not inside the report.
Apparently your data is not normalized. The first thing you need to do is to normalize it and combine the data for programme_start from the other table with the assessment data. Once you have it you can find the difference between any 2 dates by creating a query which will find the next record for each of the clients records. You can google "gaps and islands" to find examples how to do this.

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports, SSRS and Dynamic Dashboards.