Results 1 to 7 of 7

Thread: Code help

  1. #1
    Join Date
    Oct 2001
    Location
    fairview, tn
    Posts
    8

    Unanswered: Code help

    I have a table that lists a part's price along with the effective date of that price. I need to locate a part's price based upon the effective date within a date range. I run into problems when the part's price is outside (older) and the date range, e.g., the effective date is 4/5/2000 and the date range is between 10/1/06 and 5/1/07. How do I select the older date?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >How do I select the older date?
    OLDER than what?
    It might help if you posted
    SQL> DESC <my_table>
    results
    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.

  3. #3
    Join Date
    Oct 2001
    Location
    fairview, tn
    Posts
    8
    Table example:
    PART# PART$ EFF_DT
    12345 $5.25 5/1/07
    12345 $5.00 10/7/06
    12345 $4.75 7/26/05

    Some invoice dates are between 10/1/06 and 2/15/07, others after 5/1/07. I would like to select the part price either between the dates or after the last date.

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    Code:
    select part#, part$, eff_date
    from   table t1
    where  eff_date = (select max(t2.eff_date)
                       from   table t2
                       where  t2.eff_date between '1-OCT-2006' and '15-FEB-2007' and
                              t1.part# = t2.part#)
    union all
    select part#, part$, eff_date
    from   table
    where  not exists (select *
                       from   table t2
                       where  t2.eff_date between '1-OCT-2006' and '15-FEB-2007' and
                              t1.part# = t2.part#) and
           eff_date = (select max(t2.eff_date)
                       from   table t2
                       where  t2.eff_date > '15-FEB-2007' and
                              t1.part# = t2.part#)
    ---=cf

  5. #5
    Join Date
    Oct 2001
    Location
    fairview, tn
    Posts
    8

    Code Help

    Thanks. I have different part pricing for a given part based upon an effective date. I am trying to select either the price between two dates, or if outside the dates, the one closest to the beginning date. I unioned two statements in hopes of getting unique or distinct parts with their corresponding pricing, but it is not working. The statement before the union gives me a part, but it also shows up in the statement after the union. What am I doing wrong?

    SELECT A.FCLTY_NO, A.VIN_PFX_CD,A.VIN_SER_NO, A.WO_NO, A.WOL_NO, B.WO_OPEN_DT, C.EFF_DT, B.WO_COMPL_DT,
    A.PART_NO, C.DLR_NET_PRC_AM
    FROM DRBA.SVC_WOL_PART_T A,
    DRBA.SVC_WO_T B,
    DRBA.PRT_PART_PRC_T C
    WHERE A.FCLTY_NO = B.FCLTY_NO
    AND A.VIN_PFX_CD = B.VIN_PFX_CD
    AND A.VIN_SER_NO = B.VIN_SER_NO
    AND A.WO_NO = B.WO_NO
    AND A.PART_NO = C.PART_NO
    AND A.FCLTY_NO = '2261'
    AND B.LAST_SUBM_DT BETWEEN '01NOV2006' AND '30APR2007'
    AND C.EFF_DT BETWEEN B.WO_OPEN_DT AND B.WO_COMPL_DT
    AND A.PART_NO LIKE '%999%'
    UNION
    SELECT FCLTY_NO,VIN_PFX_CD,VIN_SER_NO, WO_NO, WOL_NO, WO_OPEN_DT, EFF_DT, WO_COMPL_DT, PART_NO, DLR_NET_PRC_AM
    FROM (SELECT DISTINCT A.FCLTY_NO, A.VIN_PFX_CD, A.VIN_SER_NO, A.WO_NO, A.WOL_NO, B.WO_OPEN_DT, EFF_DT, WO_COMPL_DT, PART_NO, DLR_NET_PRC_AM
    FROM DRBA.SVC_WOL_PART_T A,
    DRBA.SVC_WO_T B,
    DRBA.PRT_PART_PRC_T C
    WHERE A.FCLTY_NO = B.FCLTY_NO
    AND A.VIN_PFX_CD = B.VIN_PFX_CD
    AND A.VIN_SER_NO = B.VIN_SER_NO
    AND A.WO_NO = B.WO_NO
    AND A.PART_NO = C.PART_NO
    AND A.FCLTY_NO = '2261'
    AND B.LAST_SUBM_DT BETWEEN '01NOV2006' AND '30APR2007'
    AND A.PART_NO LIKE '%999%'
    AND C.EFF_DT = (SELECT MAX(EFF_DT)
    FROM DRBA.PRT_PART_PRC_T D
    WHERE C.PART_NO = D.PART_NO
    AND EFF_DT < B.WO_OPEN_DT))

  6. #6
    Join Date
    Dec 2003
    Posts
    1,074
    Your UNION isn't working (*I think*) because some parts have a record where
    Code:
    C.EFF_DT BETWEEN B.WO_OPEN_DT AND B.WO_COMPL_DT
    and where
    Code:
    (SELECT MAX(EFF_DT)
    FROM DRBA.PRT_PART_PRC_T D
    WHERE C.PART_NO = D.PART_NO
    AND EFF_DT < B.WO_OPEN_DT))
    So, the differing EFF_DT between the two causes them both to show up, despite the DISTINCT (BTW, that DISTINCT in the inline view of the second query is not necessary if you're using UNION).

    The second SQL stmt probably needs another piece of criteria like, "and NOT EXISTS a record which fits the criteria from the first query"
    Code:
    and NOT EXISTS (SELECT 1
                    FROM DRBA.SVC_WOL_PART_T D,
                         DRBA.SVC_WO_T E,
                         DRBA.PRT_PART_PRC_T F
                    WHERE D.FCLTY_NO = E.FCLTY_NO
                      AND D.VIN_PFX_CD = E.VIN_PFX_CD
                      AND D.VIN_SER_NO = D.VIN_SER_NO
                      AND D.WO_NO = E.WO_NO
                      AND D.PART_NO = F.PART_NO
                      AND D.FCLTY_NO = A.FCLTY_NO
                      AND B.LAST_SUBM_DT BETWEEN '01NOV2006' AND '30APR2007'
                      AND F.EFF_DT BETWEEN E.WO_OPEN_DT AND E.WO_COMPL_DT
                      AND D.PART_NO = A.PART_NO)
    ---=cf

  7. #7
    Join Date
    Oct 2001
    Location
    fairview, tn
    Posts
    8
    Thanks...I will give it a try...

Posting Permissions

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