Select the 2nd date from a list of dates

[GayleC]

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

 

[GayleC]

I'm using Crystal XI, Thanks

 

[kray4660]

I think you could do something like this. Not tested. First off you have to group by the patientvisitid and sort by datetransmitted.

You will need a reset formula in the Group Header i.e, datevar datetranssmitted := date(1900,01,01);

Then in the details
datevar datetransmitted;
if datetransmitted = date(1900,01,01) then
if {PatientVisitID} <> next({PatienVisitID}) or next({OrderforClaims}) = 2 then
datetransmitted := {uvClaims.DateTransmitted}
else
datetransmitted := next({uvClaims.DateTransmitted});

datetransmitted

 

[GayleC]

Thank you. It got me a little closer but there are two issues. The first issue is that the reset formula is not working, when I ran a test using two separate patientvisitID's the results for the 2nd patientvisitID were identical to the 1st patientvisitid. The second issue is that it is only pulling the 1st uvClaims.DateTransmitted

Below is the results I received. The 2NDCLAIMDATE field for patientvisitid 471443 should be 7/13/16 and for patientvisitid 470039 it should be 6/8/16.

I was looking at loops last night, wondering if that needed to be incorporated, but I'm as lost on loops as I am on arrays.

PatientVisit TicketNumber DateTransmitted OrderforClaims 2NDCLAIMDATE
471443 ASHBL015161 06/15/2016 1 6/15/2016
471443 ASHBL015161 06/15/2016 2 6/15/2016
471443 ASHBL015161 07/13/2016 1 6/15/2016
471443 ASHBL015161 07/13/2016 2 6/15/2016
471443 ASHBL015161 08/29/2016 1 6/15/2016
471443 ASHBL015161 08/29/2016 2 6/15/2016
471443 ASHBL015161 09/09/2016 1 6/15/2016
471443 ASHBL015161 09/09/2016 2 6/15/2016
471443 ASHBL015161 10/11/2016 1 6/15/2016
471443 ASHBL015161 10/11/2016 2 6/15/2016

470039 BHSIN018185 06/08/2016 1 6/15/2016

 

[kray4660]

Sometimes with global variables, I have read that you need to put in whileprintingrecords into the formulas for values to be passed correctly. Normally I just use shared variables to avoid the issue entirely.

 

[GayleC]

Thanks, adding whileprintingrecords to the reset formula fixed the issue with it grabbing the date from the previous patientvisitid. I've been playing with the formula more, if I use my record selector filter to only pull OrderforClaims = 1 the formula works in my sample, but I'm worried that there might be some data missed that I actually need.

 

[kray4660]

Lots of luck on that. I can only go by the examples you showed.