My report consists of joining 3 tables. One of the tables might have several entries for the same item with different dates. I am only interested in pulling the most recent entries. This is my current output:
|
TS Fee Type |
TS Pat Cost |
TS Med Cost |
TS CPT |
TS Billable |
Scheduled Date |
Test Code |
TC Pat Cost |
TC Med Cost |
TC CPT |
Scheduled Date |
TC Billable |
TS to Bill |
|
C C120 GFR |
|
Group |
0.00 |
0.00 |
12345 |
Yes |
3/31/2009 |
C1170 |
0.00 |
0.00 |
45 |
3/31/2009 |
Yes |
C117 |
|
Group |
0.00 |
0.00 |
12345 |
Yes |
3/31/2009 |
C1195 |
|
|
56 |
3/31/2009 |
No |
|
|
Group |
0.00 |
0.00 |
12345 |
Yes |
3/31/2009 |
C1196 |
|
|
78 |
3/31/2009 |
No |
|
|
Single |
0.00 |
0.00 |
12345 |
Yes |
3/30/2009 |
|
|
|
|
|
|
|
|
C C110 GTT |
|
Single |
5.00 |
10.00 |
12345 |
Yes |
4/31/2009 |
|
|
|
|
|
|
|
|
Single |
0.00 |
0.00 |
12345 |
No |
5/30/2008 |
|
|
|
|
|
|
|
This is what I would like to get:
|
TS Fee Type |
TS Pat Cost |
TS Med Cost |
TS CPT |
TS Billable |
Scheduled Date |
Test Code |
TC Pat Cost |
TC Med Cost |
TC CPT |
Scheduled Date |
TC Billable |
TS to Bill |
|
C C120 GFR |
|
Group |
0.00 |
0.00 |
12345 |
Yes |
3/31/2009 |
C1170 |
0.00 |
0.00 |
45 |
3/31/2009 |
Yes |
C117 |
|
Group |
0.00 |
0.00 |
12345 |
Yes |
3/31/2009 |
C1195 |
|
|
56 |
3/31/2009 |
No |
|
|
Group |
0.00 |
0.00 |
12345 |
Yes |
3/31/2009 |
C1196 |
|
|
78 |
3/31/2009 |
No |
|
|
C C110 GTT |
|
Single |
5.00 |
10.00 |
12345 |
Yes |
4/31/2009 |
|
|
|
|
|
|
|
I am only interested in the most recent scheduled date for the TS which is bolded. Is there a formula I can write to accomplish this?
IP Logged
