Limiting results to date diff answers 1

[Viscount]

Hello..

I am trying to limit a report to show only procedures that took place within 30 days of each other.

The table that I am using has the following fields

Procedure _ID - Name_ID - Name - Date of Procedure - Description

I only want to show this information for procedures that have taken place less than 30 days apart.

Any ideas.?

Cheers

Vis

 

[Viscount]

Forgot to mention - using CR10 and SQL2000

Cheers

vis

 

[Naith]

There's a possibility I may have misunderstood your data, as you've not provided the luxury of a dataset example, but I guess what you're after is a conditional suppression on:

DateDiff('d',Previous(Procedure.Date),{Procedure.Date}) < 30

based on procedure id, or name, or whatever's relevant in this case. You can switch the 'previous' for 'next' as appropriate.

Naith

 

[Viscount]

Thanks Naith

I think that you are on to the right lines - however I would also need the previous procedure.

As an luxurious example....

Name_ID Name Date Procedure

1 Joe 1/1 XYZ
1 Joe 12/2 ABC
2 Pete 12/2 BCD
3 Bob 14/2 CDE
1 Joe 18/2 CDE
2 Pete 23/5 ABC


From the above example I would want the report to show

Name_ID Name Date Procedure
1 Joe 12/1 ABC
18/1 CDE

Pete would be excluded as his second procedure was outside 30 days. Bob would also be excluded as he has not had a second procedure.

I hope that it explains it a bit better

Thanks for any help you can give.

Cheers

Vis

 

[ShortyA]

If your database is sorted by Name_Id or Name and then by date you should be able to place Naith's selection in the Record Selection Formula. If not, you could sort the records in the report by either Name_ID or Name and then by the date before adding a conditional suppresion at the Detail level (Section Expert/Suppress and select the X2 button).

ShortyA

 

[Naith]

Thanks. I work a lot better when you pamper me.

Create a suppressed running total at detail level, set to reset for each group.

Group by the ID, and conditionally suppress the details section using the following:

If 
	Count({Procedure},{NameID}) > 1
	AND
	(
	(
	DateDiff('d',Next(Procedure.Date),{Procedure.Date}) < 30
	AND
	{NameID} = Next({NameID})
	)
	OR
	(
	DateDiff('d',Previous(Procedure.Date),{Procedure.Date}) < 30
	AND
	{NameID} = Previous({NameID})
	)
	)
Then False
Else True

I don't have Crystal in front of me, so you may have to sex it up a bit after you test it.

Naith

 

[Naith]

I should add, incidentally, that if you aren't interested in anyone with less than two procedures, you'd be better off sticking the above in the group selection criteria of the report rather than suppressing records you don't want.

 

[Naith]

I was working on two things at the same time. Disregard the reference to the running total field.

 

[Viscount]

Thanks Naith,

I will give it a go and a tweak if necessary!

vis

 

[Viscount]

Cheers - It seems to work fine!