Calculation using current and previous records in a query

[lostgrrl]

I have a table that has a phone number field (MAINTNO), a date reported field (RECDT), and a date cleared field (CLRDT). I want to order the table by grouping phone numbers, then ascending dates in the cleared field within the same group of phone numbers. Then I want to subtract the date cleared of one record from the date reported of the next record. (My goal is to find the phone numbers and dates when the next time a date is reported is less than 3 days after the previous date cleared). For Example:
MAINTNO RECDT CLRDT
5552890176 7/2/01 7/5/01
5552890176 7/6/01 7/7/01
There is one day between the CLRDT of the first record and the RECDT of the second record.
I set up this SQL query which almost worked:

 SELECT t1.MAINTNO, t1.RECDT, t1.CLRDT, (SELECT TOP 1 t2.RECDT FROM Table AS t2 WHERE (t2.MAINTNO=t1.MAINTNO) AND (t1.RECDT<t2.RECDT) AND (t2.RECDT>=t1.CLRDT)) AS PrevVal, ([PrevVal]-t1.CLRDT) AS Range1
From Table AS t1
ORDER BY t1.MAINTNO, t1.RECT, t1.CLRDT

This pretty much worked, but sometimes (for no apparant reason) it would not calculate the right date or interval. Can I do this with SQL, or do I need to include VB code, and how do I do it? Thanks

 

[MichaelRed]

Without going through the actual records for the details, I am not qualified to tell you WHY the statement you post does not ALWAYS work. I suspect that there are occassions where the t2.Fields are null or perhaps pich up a third (or subsquent) record from the first. It can be done in SQL - IF the proper conditions are met. In fact this topis was discussed recently in these forums - with a soloution provided by Terry Hoey? Search the forums for his name and other keywords ("Previous Record"?)

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[lostgrrl]

I have not found any similar threads in all of my searches. How would I run this query in Visual Basic? Thanks

 

[MichaelRed]

With a self join,


SELECT TOP 1 tblPhnMaint.MaintNo, tblPhnMaint.RecDt, tblPhnMaint.ClrDt, tblPhnMaint_1.RecDt
FROM tblPhnMaint INNER JOIN tblPhnMaint AS tblPhnMaint_1 ON tblPhnMaint.MaintNo = tblPhnMaint_1.MaintNo;

Obviously, my table name is different, but the field names are the same as in your (all to brief sample) dateset. I am NOT at all sure this is what you are after, but it is failry close. If it fails, please provide additional sample info and some example of what it does do and what it SHOULD do.


MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over