check dates

[rekhatr10]

Hi eveyone,

CR 7.0
SQL Server 2000

Here is my dilema . 2table
order charge
ordate billdate
cpt cpt
status rdoc
donedate bdoc
donetime
rdoc

Right now I am pulling the report by orderdate. But now a sitation has come up that I have to chekc the appt date. The providers can set a future order and the pt can come back say 2 day before or after the orderdate. But when the person walk through the door they will get an app. How do I check in the apptable.
Appttable
adate
Doc
account
name
Each pt will have multiple apptdate in appttable. Can you suggest a way of doing where I can see 2 day before and 2days after the apptdate is set.
Thank you in advance
Rekha

 

[lbass]

I'm assuming you haven't shown us all the table fields, since it seems like you must have an account field in the order table. If this is the case, then you could use a left join from the order table to the appointment table on the account field, and then create a formula to identify appointments that fall within +- 2 days of the order date:

if {appt.date} in {order.date}-2 to {order.date}+2 then {appt.date}

-LB

 

[rekhatr10]

lbass,


Should I put the formula in the record selection criteris? I am just totally confused about this report. If I do a left join wouldn't all the field that I link become left join and I get more records than i want. I also have to give user to pick a date and the status. Any suggestion will be helpful.

RR

 

[lbass]

If you only want to see patients who have an order date and an appointment date then use an equal join on the accountID field.

Are you saying the user needs to select the order date? And you have not mentioned "status" before. Please explain what this is, what table field is involved, etc.

-LB

 

[rekhatr10]

LB,

Can you specify for each link what kind of join we want.
Does crystal 7.0 allow it.
Sorry about that. The status is coming from the ordertable and if somebody sticks tha patient they change it W or Y. That is an indication that the patietn showed up.

Thank you
Rekha

 

[lbass]

You are not providing enough information for me to know what exactly you are trying to do. Why do you need the charge table at all? I can't tell you what to join on, since I am not familiar with your fields. I would assume that you would link from order to appt and from appt to chart on some account ID field. Whether these are all equal joins (which would only show those who have a record in all tables, ie., there was an order, an appt, a charge) or left joins (all patients with orders, whether or not there is an appointment or charge), or an equal followed by a left join (all orders with appointments, with or without charges) depends upon what you are trying to do.

You could create two parameters, {?orderdate} and {?status} and use them in your record selection formula:

{table.orderdate} = {?orderdate} and
{table.status} = {?status} and
{table.apptdate} in {order.date}-2 to {order.date}+2

If you use the apptdate in the record selection formula as above, you will only return those order dates that have an appt date within two days before or after the order date. If you want to see the order dates regardless of whether there is an appt date, then use left joins, and create the formula from my previous post and place it in the detail section of your report.

-LB

 

[rekhatr10]

LB,

I have to have the charge table to check whether the visit was billed or not.


RR