parameter and select expert problem 1

[sorchard2000]

Crystal 8.5
SQL Server connection

I am working on a simple report that tracks patient appointments in the scheduling software. I need to see when an appointment is added, moved to another date and/or canceled (it's usually in that order). I currently have 2 parameters setup: StartDate (date/time) and StopDate (datetime). Then I have the select expert to select between the ?StartDate and ?StopDate. But my problem is that when an appointment cancels, the appointment date field becomes null for that cancellation (since it is canceled off the appointment software).

Currently showing:
John Smith Added appt date: 10/1/2004
John Smith Moved appt date: 10/8/2004

Needs to show:
John Smith Added appt date: 10/1/2004
John Smith Moved appt date: 10/8/2004
John Smith Canceled


So when the user puts in a search date range, my report will show the added and moved appointments but not the cancels.
My question is: is there a way to tell the select expert to choose the first date associated with that patient (the date added to the appointment software)?
This is a McKesson version of Crystal so I am required to use that StartDate and StopDate syntax in the parameter field.

I hope that is clear. Thanks for any help given!

sorchard2000

 

[synapsevampire]

You should abandon the select expert and start directly editing the record selection formulas, you'll have more flexibility.

Select Report->Edit Selection Formula->Record

Change it to:

(
isnull({table.enddate})
and
{table.date} >= {table.startdate}
)
or
(
{table.date} >= {table.startdate}
and
{table.date} <= {table.enddate}
)

You might also paste in your existing record selection formulas to the post when requesting help with them rather than trying to describe what might be in there.

-k

 

[sorchard2000]

OK, sorry I didn't show the actual code. I tried your suggestion (and probably misinterpreted it) but here is what my Select Report->Edit Selection Formula->Record
currently reads:

{service.abbr} = "ORTHO" and
{patbooking.appt_id} = 22436 and
not ({res.name} like "MISC*") and
(
{schedlog.scheduled_datetime} >= {?StartDate}
and
{schedlog.scheduled_datetime} <= {?StopDate}
)

What am I doing wrong?

sorchard2000

 

[synapsevampire]

Try:

{service.abbr} = "ORTHO" and
{patbooking.appt_id} = 22436 and
not ({res.name} like "MISC*") and
(
(
isnull({schedlog.scheduled_datetime})
)
OR
(
{schedlog.scheduled_datetime} >= {?StartDate}
and
{schedlog.scheduled_datetime} <= {?StopDate}
)
)

-k

 

[kskid]

{service.abbr} = "ORTHO" and
{patbooking.appt_id} = 22436 and
not ({res.name} like "MISC*") and
(
isnull({schedlog.scheduled_datetime}) or
{schedlog.scheduled_datetime} in [{?StartDate} to
{?StopDate}]
)

 

[sorchard2000]

Thanks to both of you for your help!
synapsevampire's code almost completely worked but wichitakid's code worked perfectly. I really appreciate both of your help!

sorchard2000

 

[sorchard2000]

OK, let me qualify that:
It DOES work with PAST dates, but not FUTURE dates. When I put a future date in, it suppresses the {schedlog.scheduled_datetime}. No dates at all show up.

Any ideas?

Sorchard2000

 

[kskid]

I know the date portion works because I use the same formula in my reports. It must be one of your other selection criteria that is preventing it.

Try it with just {patbooking.appt_id} = 22436 and see what returns

 

[sorchard2000]

wichitakid:
Yes, it works with {patbooking.appt_id} = 22436 but this is an appointment from last week. When I took off this {patbooking.appt_id} = 22436
and put in next week's dates (as the end user would do), every scheduled appointment date comes back blank.

It's very odd.

sorchard2000

 

[synapsevampire]

Our code was the same...

-k

 

[kskid]

How are patbooking, service, res and schedlog linked together? Has to be something there that does not tie in the schedlog on future appointments. If possible, post your Show SQL Query statement

 

[sorchard2000]

I was afraid you might think it was the links--

SELECT
schedlog.schedlog_id, schedlog.appt_id, schedlog.scheduled_datetime,
audittype.abbr,
probooking.name,
patbooking.appt_id, patbooking.act_start_datetime,
res.name, res.resunit_id,
pat.name_legal,
service.abbr
FROM
prod.dbo.schedlog schedlog,
prod.dbo.audittype audittype,
prod.dbo.probooking probooking,
prod.dbo.patbooking patbooking,
prod.dbo.res res,
prod.dbo.pat pat,
prod.dbo.pro pro,
prod.dbo.service service
WHERE
schedlog.audittype_id *= audittype.audittype_id AND
schedlog.appt_id = probooking.appt_id AND
schedlog.appt_id = patbooking.appt_id AND
probooking.prim_pract_id = res.res_id AND
patbooking.pat_id = pat.pat_id AND
probooking.pro_id = pro.pro_id AND
pro.service_id = service.service_id AND
res.name NOT LIKE 'MISC%' AND
(schedlog.scheduled_datetime IS NULL OR
schedlog.scheduled_datetime >= "2004/11/22 02:38:02AM" AND
schedlog.scheduled_datetime < "2004/11/26 11:38:03PM") AND
service.abbr = 'ORTHO' AND
res.resunit_id = 4

 

[kskid]

My sugggestion is to create a new report with just schedlog.appt_id = 22436 and check the results.

{schedlog.appt_id} = 22436 and
(
isnull({schedlog.scheduled_datetime}) or
{schedlog.scheduled_datetime} in [{?StartDate} to
{?StopDate}]
)

If you have future dates with schedlog, then add one table at a time and recheck results until the future dates disappear. If you don't have any future dates with schedlog, then those records are in some other table.

Without knowing the intricacies of your business, that's about all any of us can offer at this time.

 

[sorchard2000]

Thanks so much for your help! I appreciate you making the time to help me!

I slowly recreated the report, again, changed some links and found a workaround. It's not the best solution but it will work.

Thanks again!

sorchard2000