Jump to content

comparing dates in 2 different tables


ADAMH
 Share

Recommended Posts

I have 2 tables: Documentation and Bill.Documentation table has a PATIENT column, EMPLOYEE column and a DATE column.Bill table has a PATIENT column, EMPLOYEE column and a DATE column.The tables are 1-to-many.When an employee documents a service, it inserts the date of service into the Documentation table.Then the employee sends a charge to the Bill table for the service that was documented.I want to be able to check that the date billed matches the date documented.Any ideas?Thanks.

Link to comment
Share on other sites

You would also need to include the patient field, most likely more then one patient is being billed per day.SELECT * FROM Documentation AS D, Bill AS B WHERE D.date = B.date AND D.patient = B.patientI'm not sure there's a guarantee that the same employee is responsible for both entries, or else you can also include the employee field.Keep in mind this only selects records where the entries match. It doesn't do anything if they don't match, so it's not going to find entries that don't match. There's no way to tell just with this data which transactions match. There could be billings on sequential days for the same patient and you wouldn't know which ones go with which. The tables should have an id column that tells which record in the documentation table a record in the bill table corresponds to. Without that you don't have any relationship that says which records are for the same transaction.

Link to comment
Share on other sites

There is an ID column, sorry

You would also need to include the patient field, most likely more then one patient is being billed per day.SELECT * FROM Documentation AS D, Bill AS B WHERE D.date = B.date AND D.patient = B.patientI'm not sure there's a guarantee that the same employee is responsible for both entries, or else you can also include the employee field.Keep in mind this only selects records where the entries match. It doesn't do anything if they don't match, so it's not going to find entries that don't match. There's no way to tell just with this data which transactions match. There could be billings on sequential days for the same patient and you wouldn't know which ones go with which. The tables should have an id column that tells which record in the documentation table a record in the bill table corresponds to. Without that you don't have any relationship that says which records are for the same transaction.
Link to comment
Share on other sites

Oh. Well why didn't you say that? So if there is a column called documentation_id in the Bill table that corresponds to an ID in the Documentation table, then you can get IDs from the bill table that don't have the same date as the record in the documentation table like this:SELECT B.id FROM Bill AS B, Documentation AS D WHERE B.documentation_id = D.id AND B.date != D.date

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...