How to compare 2 datasets that aren't identical

[mhalfpap]

Hello,

I’ve just about scrambled my brain on this, searching and trying things, so I’m hoping someone sees something that I’m missing or can explain it to me. I’m new to .NET, used to do a lot of programming in VB but that was many years ago. I mainly lurk, but I’m just stumped now, so anybody have any ideas?

I have 2 datasets, a patient record set and a billing record set. I’m building an audit, so I need to compare the two record sets and identify any missing patient entries that are not in the billing record set. These are not stored in a database, they are imported only for auditing then the results are stored in a file and the datasets are destroyed, I don’t even display them in any kind of grid or anything, it’s all just to confirm them.

The patient record set has:
ID - int32 (I’m generating this as I import it, thinking I needed a key)
fName - String
lName - String
dos - String (this is date of service and is later converted to a short date)
desc – String
confirmed - String (I added this as a way to track if the record was in the billing file as part of the audit)

The billing record set has:
fName - String
lName - String
dos - String (this is date of service and is later converted to a short date)
desc – String

The record sets are populated by reading text file dumps from other systems. I need to identify records that are in the patient record set, but not in the billing record set. Unfortunately the only fields I can actually compare are names and date of service (the descriptions don’t necessarily match for the same tasks), so I already know while I should be able to tell a record is missing, I just can’t tell necessarily which one. In this case that’s OK because the user will go back and confirm those records that are caught and validate the actual records.

So in the patient table I may have:
1,tom,jones,8/12/2010, right leg fracture,no
2,jim,smith,8/15/2010,left hernia,no
3,jim,smith,8/15/2010,left drainage,no
4,sally,may,8/21/2010,slipped disk,no

Note that the same patient may have multiple entries, so I added the confirmed field thinking when I stepped through I’d change that to a “yes” which would allow me to account for multiple billing entries, also when I reported later I could just filter all the “no” records as a report.

In the billing table I may have:
tom,jones,8/12/2010, rt leg fracture,no
jim,smith,8/15/2010,lt hernia,no
sally,may,8/21/2010,disk injury,no

Note only one entry against Jim Smith, so as part of the audit we need to catch that so we can bill him for both services. I need to show that we’ve audited the record, so that was another reason for the confirmed field.

Initially I tried to do it using For Each loops:

For each drbilling in dtbilling.Rows
	For each drpatient in dtpatient.Rows
		If drbilling(“lname”) = drpatient(“lname”) and drbilling(“dos”) = drpatient(“dos”) and confirmed = “no”…
			drpatient.BeginEdit
drpatient(“confirmed”) = “yes”…
			drpatient.EndEdit
	Next
Next

I actually had multiple if statements trying to speed it up, if the first name matched, then if the last name matched then if the date of service matched and confirmed was no, but I got caught up with the fact that the second for each loop continued to loop through the second file even if it had found a matching record. So if you had multiple records like jim smith above, both records got marked as confirmed. I tried to reverse it, using the billing file as the first loop, but no go there either.

I tried to use a .Find on the data set, but as I understood it was looking for the index, which I couldn’t identify without a SQL like statement, which it didn’t seem to like.

I tried to use a .Select on the table, but it returned multiple rows, and I couldn’t seem to separate them to edit only the first affected row.

At this point my code is a shamble, so I’m pretty much starting over on the “audit” portion. Any help would be greatly appreciated.

Thanks,
Mark

[mhalfpap]

OK, disregard. I found that you exit a for loop, tactically placed this seems to have resolved my issue.

For Each drCPU In dtcpuTable.Rows
            For Each drVendor In dtvnedorTable.Rows
                If drVendor("fname") = drCPU("fname") Then
                    If drVendor("lname") = drCPU("lname") Then
                        If drVendor("dos") = drCPU("dos") And drVendor("confirmed") = "No" Then
                            ' There is a row that has not been confirmed, update confirmed and move on
                            drVendor.BeginEdit()
                            drVendor("confirmed") = "Yes"
                            drVendor.EndEdit()
                            Exit For

                            p = p + 1
                        End If
                    End If
                End If

            Next
            ProgressBar1.Value = ProgressBar1.Value + 1
            a = a + 1
        Next

It may not be perfect, or even "right", but it seems to be working.