Date Formula 1

[Jonathan212]

I have a report that displays surgery cases that were scheduled (“datebooked”) AND completed (“datecompleted”) in the same week to identify any cases that were scheduled (and missed) after a report “run” date. The “run” date we are always using is the Friday afternoon of every week.

Here is an example of cases we might miss:
Scheduled Cases report “run” on 7/20/2007
New Case #123 added on 7/24/2007
Case #123 completed on 7/26/2007

The report run on 7/27/2007 would not capture Case #123 as a scheduled case (only as a completed case when we run a completed case report).

The problem for the Researchers is that although they eventually see that a case was done (when they run a completed surgery report) they have no record of the particular case in their scheduled surgery registry. The Researchers want to load information into their scheduled surgery registry even though the case was already done. Naturally, some loopholes could be closed if the scheduled surgery report was run several times every day, but that is not going to happen.

Therefore, to at least identify cases scheduled and done in the same week I created two parameter values “datebooked’ and “datecompleted”. The same date ranges must be entered for both parameters (for example, “7/23/2007 to “7/27/2007”). This works fine.

Is there a way to do this for multiple time periods, using perhaps a datediff formula or some other formula. As an example, without having to run the report multiple times to find out cases scheduled and done in the following weeks:

7/9/2007 to 7/13/2007
7/16/2007 to 7/20/2007
7/23/2007 to 7/27/2007

I am using Crystal Reports 10.

Thanks in advance for any ideas.

Jonathan

 

[lbass]

I think it would help if you showed your record selection formula and if you showed some sample data, say for a week. At the detail level, can you see the history of the cases?

-LB

 

[Jonathan212]

A report run on July 6, 2007 would not have picked up the output (indicated below) because the case was scheduled on July 9, 2007. I can get the correct output, but was looking for a way to do multiple time periods (to check for missed cases) rather than running the report individaully for 7/9/07 to 7/13/07 and then running a report for 7/16/07 to 7/20/07, and then another report for 7/23/07 to 7/27/07.....


Selection Criteria
{pcmCase.status} = 3 andisnull({pcmprocedure.deletedon}) andisnull({pcmanesthes.deletedon}) andisnull({pcmcase.deletedon}) and{pcxSchedulableResources.resourceCart} in [3640, 3636, 3635, 3637, 3638] and{pcmProcedure.primaryProcedure} = "Y" and{@SurgDate} = {?Date} and{@DateBooked} = {?DateBook}

Output
LastName MDName Surgdate Joint case # ScheduledDate
XXXXX Kildare 07/10/2007 Knee 150204 07/09/2007

 

[Turkbear]

Hi,
If run for multiple spans, do you want the data to appear in one detail or grouped by the date range run?

Either way, if the date ranges are contiguous, can't you expand the range to cover as much time as you want--

If you want the data grouped you can create a formula that , for each date range you need defined as a group, provides that info, e.g.
@DateRangeGroup
If datebooked >= 7/1/2007 and datebooked <= 7/14/2007
then
"Reporting Period from July7, 2007 to July 14 2007"
else...

Group on that formula..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

I'm not following why you have to run the report at 4-day intervals to capture this. I was looking for more than one row of data, and for the logic behind your selection criteria that is limiting your ability to pick this up. You didn't answer my question either about history.

Why are you limiting your parameter dates to one date instead of a range?

-LB

 

[Jonathan212]

Thanks for your help, I'm not articulating this very well.
The date booked and date completed are ranges not single dates.

I'm not sure what you mean at the detail level can I see the history of the cases.

Here is output from 7/9/07 to 7/13/07 and output for 7/16/07 to 7/20/07 - these were separate reports - I was trying to be able to do this on one report.

Date Booked and date Completed Ranges both = 7/9/07 to 7/13/07

LastName Firstname MI Sex BirthdateMR # Surg. MDName Surgdate Joint PrimaryorLatera case # Status Entered
M 07/08/19 ARS 07/11/200 Knee Primary RT 150204 307/09/200
F 04/13/19 HIP 07/11/200 Hip Revision RT 150370 307/10/200
D F 10/02/19 ARS 07/12/200 Knee Primary RT 150442 307/11/200

Date Booked and date Completed Ranges both = 7/16/07 to 7/20/07

LastName Firstname MI Sex BirthdateMR # Surg. MDName Surgdate Joint PrimaryorLatera case # Status Entered
M 10/14/19 CLN 07/20/200 Knee Primary LT 150796 307/16/200
C M 02/21/19 ARS 07/19/200 Hip Primary RT 150813 307/16/200
F 07/11/19 KNE 07/20/200 Knee Primary LT 151398 307/19/200

 

[lbass]

I think I finally see. Try setting up the date part of the selection formula like this:

{table.booked} in {?start date} to {?enddate} and
{table.surgdate} in {?start date} to {?enddate} and
datepart("ww",{table.booked}) = datepart("ww",{table.surgdate})

You could enter whatever length of period you like, and then the report would only return those records where the week scheduled = week of surgery.

You could insert a group on {table.booked} by week so that you could compare with your other reports.

What I was missing was that the booking ordinarily occurs in weeks previous to the week of surgery, and that you are running your usual reports the week before surgery takes place.

-LB

 

[Turkbear]

Hi,
What would you get if you made the date ranges

7/9/07 to 7/20/07 ?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Jonathan212]

LB, firstly, thank you for your patience and sticking with me as I tried to state what I needed. As Turkbear paraphrases, the help you get is proportional.....

Secondly, your formula works flawlessly, perfectly, eloquently. What a wonderful Monday morning present.

Turkbear, thank you for your input too. My sense is that your recommendation may have worked, but I did not pursue it this morning after I got the LB option to work. I will explore it further though.

My sincere thanks to both of you. Most appreciated !