Number of days between multiple records 1

[Viscount]

I have got a table like so...

ID On-Date Off-Date Off-Reason

1 04/07/2004 04/06/2004 DEF
2 07/07/2004 10/07/2004 WLR
1 04/09/2004 04/10/2004 DEF
1 04/11/2004

I want to know the difference between todays date and the on-date less the sum of the differences between Off-Date and the next On-Date where the Off-Reason equals DEF.

I have managed to sort it to give the correct results if there is 1 extra entry (using Maximum On-Date) but I am stuck when lookin to every entry.

I am using CR9 with SQL2000

Any help will be greatly received..!

Thanks

Vis

 

[Viscount]

Having mumbled & thought about this I have to add a few more little problems...

Line ID On-Date Off-Date Off-Reason

1 1 04/02/2004 04/03/2004 DEF
2 1 04/04/2004 04/05/2004 DEF
3 1 04/06/2004 10/06/2004 WLR
4 1 04/07/2004 04/06/2004 DEF
5 2 07/07/2004 10/07/2004 WLR
6 1 04/09/2004 04/10/2004 DEF
7 1 04/11/2004

The extra problem is that an id can have multiple Def's as part of a sequence which ends with a WLR.

In the above example I would like have:

the difference between todays date and the on-date (line 4),
less the sum of the difference between the off-date of line 4 and the on-date of line 6,
less the sum of the difference between the off-date of line 6 and the on-date of line 7.

I hope that this makes sense..!

Thank for any help.

Vis

 

[Viscount]

Bump...

Can anyone help me with this..?

Cheers

Vis

 

[lbass]

Your example doesn't make sense. It is unclear why you would be working with line 4, since it is neither the maximum date when ID = 1 or the maximum date when ID =1 and off-reason = "DEF". Maybe you should carefully rework your example. It might also help to explain a little about what you are trying to do--about what these fields relate to. It's a little too abstract as is.

-LB

 

[Viscount]

In short it is a waiting list - patients come on to the waiting list - but then come off the list when they are deferred (DEF) then when the procedure is performed they are removed from the list (WLR).

What I need to know is the number of days between coming off the waiting list (DEF) and coming back on to it. This number is then added to the maximum wait the patient can have before it is a requirement to preform the procedure.

My problem is that patients can have more than 1 procedure so that means thay can have multiple deferrals before the actual procedure and this can then be repeated, depending on how sick a patient is.

This is why I was interested in line 4. (There was a mistake in the one above)

Line ID On-Date Off-Date Off-Reason

1 1 04/02/2004 04/03/2004 DEF - family issue
2 1 04/04/2004 04/05/2004 DEF - patient sick
3 1 04/06/2004 10/06/2004 WLR - performed
4 1 04/07/2004 04/08/2004 DEF - lost touch
5 2 07/07/2004 10/07/2004 WLR - performed
6 1 04/09/2004 04/10/2004 DEF - school hols
7 1 04/11/2004

In the above example a patient was originally put on to the waiting list on 4/2/04 and then deferred (4/3/04)because the family had a breavement and they needed some time to sort things out.

They were then put back on the waiting list a month later (4/4/04) only to be deferred again on 4/5/04 because the patient was too sick to have the procedure performed.

When the sickness passed they were put back on the list on 4/6/04 and had the procedure on 4/7/04 when they were removed from the list (WLR).

Further investigations revelaed that the patient needed a second procedure. They were put on the waiting list on 4/7/04 - but the patient moved house during the month and did not give a forwarding address - so the patient had to be removed (DEF) until they could be traced.

They were eventually traced and put back onto the list on 4/9/04 but had to look after children during the holidays so was deferred until after the holidays - they have been put back on to the list on 4/11 and are currently waiting their procedure.

I hope that is a little bit clearer..!

Thanks for any help you can give.

 

[lbass]

Not sure how the multiple procedures fit in. It looks like a new procedure is given a new ID number, and yet your calculations ignore that new procedure. If you mean to ignore the new procedure, then insert a group on ID, so that the rows you are working with are consecutive.

Then create three formulas:
//{@reset} to be placed in the group (ID) header:
whileprintingrecords;
numbervar x := 0;
numbervar y := 0;
datevar z := date(0,0,0);

//{@calc} to be placed in the detail section:
whileprintingrecords;
numbervar x;
numbervar y;
datevar z;

if {table.offreason} = "WLR" then
z := {table.ondate};
if {table.offreason} = "WLR" then
x := currentdate - next({table.ondate});
if {table.offreason} = "DEF" and
{table.ondate} > z and
next({table.ID}) = {table.ID} then
y := y + next({table.ondate}) - {table.offdate};

//{@displayresults} to be placed in the group (ID) footer:
whileprintingrecords;
numbervar x;
numbervar y;
x-y

Not sure if this meets the needs of your logic, but it might get you started.

-LB

 

[Viscount]

Thanks LB - I think that I have finally sorted it baed on your suggestion with a few minor tweaks.

//{@reset} to be placed in the group (ID) header:
whileprintingrecords;
numbervar x := 0;
numbervar y := 0;
datevar z := date(0,0,0);

//{@calc} to be placed in the detail section:
whileprintingrecords;
numbervar x;
numbervar y;
datevar z;

if {table.offreason} = "WLR" then
z := {table.ondate};
if {table.offreason} = "WLR" then
x := numbervar y := 0;
if {table.offreason} = "DEF" and
{table.ondate} > z and
next({table.ID}) = {table.ID} then
y := y + next({table.ondate}) - {table.offdate};

//{@displayresults} to be placed in the group (ID) footer:
whileprintingrecords;
numbervar x;
numbervar y;
y