Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Location
    Charlotte, NC
    Posts
    79

    Question 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
    Make something idiot proof and someone will make a better idiot...

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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. #3
    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. #4
    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?
    NOTE: Please disregard the label "Senior Member".

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Any ideas what I am doing wrong?
    To make it work use SQUARE brackets, "[" & "]" and not the greater & less than signs.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    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
  •