I need to capture the 2nd time, where it exists, a claim was filed to a patients primary insurance for a specific visit, unless the 2nd time occurs after a claim has been filed to the patients secondary insurance. The number of dates for filed claims can range anywhere from 1 to infinity. The result of the query would be used to compare against a separate date field from another table.
The table involved is called uvclaims and the fields involved are PatientVisitID, DateTransmitted, OrderforClaims. The data in the table is linked to other tables through the PatientVisitID field using a Left Outer Join.
Data Example 1
PatientVisitID DateTransmited OrderforClaims
471443 6/15/2016 1
471443 7/13/2016 1
471443 8/29/2016 1
471443 9/9/2016 2
Desired result: 7/13/2016
Data Example 2
PatientVisitID DateTransmited OrderforClaims
471443 6/15/2016 1
471443 7/13/2016 2
471443 8/29/2016 1
Desired result: 6/15/16
Data Example 2
PatientVisitID DateTransmited OrderforClaims
471443 6/15/2016 1
Desired result: 6/15/16
I thought an array would do the job (ignoring the OrderforClaims criteria) so I wrote the below array:
local datevar array DateTransmitted;
redim DateTransmitted [2];
DateTransmitted[1] := date(minimum({uvClaims.DateTransmitted}));
DateTransmitted[2] := date(next ({uvClaims.DateTransmitted}))
These were the results when placed into the detail of the report:
6/15/2016
7/13/2016
7/13/2016
8/29/2016
8/26/2016
9/9/2016
9/9/2016
when I tried grouping on PatientVisitID and placing the array in the group header my result was : 6/15/2016
Any help would be appreciated.
Gayle
The table involved is called uvclaims and the fields involved are PatientVisitID, DateTransmitted, OrderforClaims. The data in the table is linked to other tables through the PatientVisitID field using a Left Outer Join.
Data Example 1
PatientVisitID DateTransmited OrderforClaims
471443 6/15/2016 1
471443 7/13/2016 1
471443 8/29/2016 1
471443 9/9/2016 2
Desired result: 7/13/2016
Data Example 2
PatientVisitID DateTransmited OrderforClaims
471443 6/15/2016 1
471443 7/13/2016 2
471443 8/29/2016 1
Desired result: 6/15/16
Data Example 2
PatientVisitID DateTransmited OrderforClaims
471443 6/15/2016 1
Desired result: 6/15/16
I thought an array would do the job (ignoring the OrderforClaims criteria) so I wrote the below array:
local datevar array DateTransmitted;
redim DateTransmitted [2];
DateTransmitted[1] := date(minimum({uvClaims.DateTransmitted}));
DateTransmitted[2] := date(next ({uvClaims.DateTransmitted}))
These were the results when placed into the detail of the report:
6/15/2016
7/13/2016
7/13/2016
8/29/2016
8/26/2016
9/9/2016
9/9/2016
when I tried grouping on PatientVisitID and placing the array in the group header my result was : 6/15/2016
Any help would be appreciated.
Gayle