Results 1 to 2 of 2

Thread: Help Plz

  1. #1
    Join Date
    Mar 2003
    Posts
    10

    Unanswered: Help Plz

    Hi,
    Below is my sample data from table TranscationIn

    PositionNbr FromDate ToDate IncentivePlanTypeCode

    ----------- ------------------------------------------------------ ------------------------------------------------------ ---------------------
    90176256 2003-05-03 00:00:00.000 2003-06-30 00:00:00.000 C1

    90176256 2003-07-01 00:00:00.000 2003-11-20 00:00:00.000 C1

    90176256 2003-11-21 00:00:00.000 2003-11-24 00:00:00.000 C1



    90176256 2003-11-25 00:00:00.000 2003-12-03 00:00:00.000 S2

    90176256 2003-12-04 00:00:00.000 9999-12-31 00:00:00.000 C1


    in this FromDate, ToDate columns are of datatype DateTime

    i need to write a sql query( not a stored proc or a user defined function), so tht i can get my result as


    PositionNbr FromDate ToDate IncentivePlanTypeCode

    ----------- ------------------------------------------------------ ------------------------------------------------------ ---------------------
    90176256 2003-05-03 00:00:00.000 2003-11-24 00:00:00.000 C1


    basically it shuld look for contingious records where FromDate of record1 should be exactly next day of the previous record and all shuld have similar IncentivePlanTypeCode


    TIA,
    sudheer

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Something like this:

    select t1.PositionNbr, t1.FromDate, t2.ToDate, t1.IncentivePlanTypeCode
    from TransactionIn t1
    inner join TransactionIn t2
    on t1.PositionNbr = t2.PositionNbr
    and t1.FromDate = t2.FromDate
    and t1.IncentiveTypeCode = t2.IncentiveTypeCode
    order by 1, 4, 2, 3

Posting Permissions

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