Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Select the 2nd date from a list of dates

Select the 2nd date from a list of dates

Select the 2nd date from a list of dates

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:


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.


RE: Select the 2nd date from a list of dates

I'm using Crystal XI, Thanks

RE: Select the 2nd date from a list of dates

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}
datetransmitted := next({uvClaims.DateTransmitted});


RE: Select the 2nd date from a list of dates

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

RE: Select the 2nd date from a list of dates

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.

RE: Select the 2nd date from a list of dates

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.

RE: Select the 2nd date from a list of dates

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close