# Thread: Compare values by date in same table

1. Registered User
Join Date
Feb 2004
Location
Charlotte, NC
Posts
79

## Unanswered: Compare values by date in same table

Hi

I am trying to write a SQL statement for a colleague, but can't work out how to do it.

He has a table that contains (amongst other fields) a date, a starting monetary value and an ending monetary value.

What he wants to do is to determine if the ending value for a particular date is the same as the starting value for the next record that was written to the table. A record is written to the table most days. This problem is compounded by the fact that the dates aren't consecutive due to weekends, holidays, etc.

Neither of us can see a solution, so I wonder if anyone can help?

Thanks

2. Moderator.
Join Date
Sep 2002
Location
UK
Posts
5,171
This is a good example for using the LEAD analytic function. Suppose you have this data:
Code:
```SQL> select * from t1;

DATECOL       STARTVAL     ENDVAL
----------- ---------- ----------
15-NOV-2004         10         20
16-NOV-2004         20         25
17-NOV-2004         26         30
18-NOV-2004         30         35```
Note that the startval for 17-NOV-2004 is 26, not 25 like the endval of the previous record. Now run this:
Code:
```SQL> select *
2  from
3  ( select datecol, endval, lead(startval) over (order by datecol) next_startval
4    from t1
5  )
6  where endval != next_startval;

DATECOL         ENDVAL NEXT_STARTVAL
----------- ---------- -------------
16-NOV-2004         25            26```
(It makes no difference whether the dates are consecutive or there are gaps between them.)

3. Registered User
Join Date
May 2004
Location
Dominican Republic
Posts
721
You can also accomplish this with a litle trick ( joining the table to itself ),
like this..
Code:
```select t1.*
from ( select rownum rn, t1.*
from t1 ) t1,
( select rownum - 1 rn, t1.*
from t1 ) t2
where t1.rn = t2.rn
and t1.endval <> t2.startval```
The analytic version is neater and safer.

4. Registered User
Join Date
Nov 2003
Location
down on the cube farm, left then right then another left
Posts
467
I do not understand the "OVER" construct. The documentation of "LEAD" does not explain. Is it possible direct me to some documentation on the subject. Or better yet can you discribe that it means Tony?

On a second note - I have not been successful when I have tried to use the <code> </code> construct in any of my posts. Any ideas what I am doing wrong?

5. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776
>Any ideas what I am doing wrong?
To make it work use SQUARE brackets, "[" & "]" and not the greater & less than signs.

6. Registered User
Join Date
Sep 2004
Location
London, UK
Posts
565
OVER introduces an analytic clause, which is documented under Analytic Functions.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•