ConfusingDates

[rekhatr10]

Hi Everyone,

cr 7.0
sql server 2000

2 table

appt charge
adate billdate
pname account
atime servicedate
account cpt
userid entrydate
pdoc
adoc

I am doing a left outer join on account and adate to servicedate. There will be only one appt for the day but multiple bills for the day as labs are billed seperate.
But sometimes the docs don't change the service date and the adate and the service date don't match. What I want to do is if a patietn had appt on 6/16 and the bill said 6/17. i want the report to be displayed. Right now the report picks a ealier charge eg. 6/14 which was entered on 6/17 but the patietn had appt on that day. Any suggestionwill be helpful

Thank you
RR

 

[dGrouse]

If there's some consistent pattern to the mismatched adate & servicedate fields -- if the one is always on or up to 2 days after the other, say -- then you could simply write that into the join condition between your two fields (going to, say, a SQL command object rather than using the native Crystal-generated query), although I'd think that you'd get duplicate records eventually.

Jeff Prenevost
IS Administrator
Emergency Physicians Medical Group, PC
Ann Arbor, MI

 

[dgillz]

Is there an invoice number in both tables? That is what I would look for.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[Madawc]

I use Crystal 8.5, but what I'd do would be to write formulae to spot the cases. Something like

If {patietn}+1 = {bill} then "One day gap"

Madawc Williams (East Anglia)