DateDiff of previous record

[LLowrance]

CR: 8.5
SQL Database

I'm trying to find the difference between two dates which are not of the same record. I have an authorization table that for each record has a referral date and an authorization date. I'm trying to get the diff between the referral date of a record and the authorization date of the previous record. The table looks something like:

Record No Claimant# ReferralDate AuthorizationDate
1 7456 6/10/07 6/12/07
5 7456 7/16/07 7/16/07
12 7456 8/22/07 8/25/07
17 7456 9/1/07 9/3/07

For example I would like the difference of record17.referralDate - record12.authorizationDate. I'm trying to find records where referrals occured less than 30 days of the previous authorization.

Note the record numbers are not sequential but there is a group by claimant#.

Is this so simple I'm not seeing it? Any help is appreciated.

Lowell



-LLL

 

[dgillz]

The following will give you the date difference in days:

Not OnFirstRecord;
Datediff("d",{YourDateField},Previous({YourDateField}))

If this doesn't work for you let me know.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[LLowrance]

Thanks dgillz, worked like a charm.

-LLL