Results 1 to 9 of 9

Thread: 25525303

  1. #1
    Join Date
    May 2002
    Posts
    34

    Unanswered: 25525303

    Hello,
    I have the following table tracking parts installation event at a
    specific locations

    partID time_installed location
    P1 12/20/2007 L1
    P1 12/31/2007 L2
    P1 1/15/2008 L3
    P2 2/1/2008 L1
    P2 2/5/2008 L2
    P2 4/5/2008 L3
    P3 4/30/2008 L1
    P3 5/3/2008 L2

    I need to identify how long each part has been installed at each
    location. Every next install invalidates the previous one for the
    same partID.

    I need to produce the following table

    partID time_delta (days) location
    P1 11 L1
    P1 15 L2
    P1 calculate based L3
    on current_date
    P2 4 L1
    P2 60 L2
    P2 calculate based L3
    on current_date
    P3 3 L1
    P3 calculate based L2
    on current_date

    I can not figure out how to handle intermittent installs in SQL. I was
    thinking about min and max time, but it leaves out one instlall in the
    middle.
    Is it possible to do it in SQL or I'd need to handle it in PL/SQL?

    Please help!

    Regards,
    Pit.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by PeterS
    Every next install invalidates the previous one for the
    same partID.
    please clarify something

    in your example, this --

    P1 12/20/2007 L1
    P1 12/31/2007 L2
    P1 1/15/2008 L3

    becomes this --

    P1 11 L1
    P1 15 L2
    P1 calculate based L3

    are you saying that P1 moved from L1 to L2 to L3???



    also, what's up with 25525303 ?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2002
    Posts
    34

    Time delta per event

    Hello r937

    Hmm, I see the problem with cut and paste...
    I wanted to say that length of install (time delta) for the last valid install should be calculated based on current date: sysdate-time_installed

    Yes, parts can migrate from one location to another; previous install gets invalid.

    I hope I am more clear now.

    as to the thread name - it is paste again , I accidentally pasted some number I had on the clipboard...

    Thanks,
    Pit.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    so basically, for each part, you just want the latest time_installed?

    and do the calculation on that?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2002
    Posts
    34

    Time delta per event

    Not exactly,
    I want time difference in days between consecutive installations as indicated in my sample table

    partID time_delta (days) location
    P1 11 L1
    P1 15 L2
    P1 calculated based on sysdate L3
    P2 4 L1
    P2 60 L2
    P2 calculate based on sysdate L3
    P3 3 L1
    P3 calculate based on current_date L2

    I am trying to use analytical functions

    select partid, time_installed, location
    ,row_number() over (partition by partid order by time_installed asc) as rnk
    from parts a

    not much luck so far.

    Regards,
    Pit

  6. #6
    Join Date
    Mar 2007
    Posts
    623
    Yes, analytical function is the way to go; the PARTITION BY clause looks good, but you shall use LEAD function to get the value from the next row.
    By the way, what is the difference between sysdate and current_date in your description? Can you follow it with some value, e.g. for sysdate = June 30th, 2008?

    [Edit: typo]

  7. #7
    Join Date
    May 2002
    Posts
    34

    25525303

    Thanks a lot! LEAD looks promising, I'll try it right now
    The reason I used current_date being that I was developing on Teradata. There is no LEAD/LAG functions. To resolve my problem and avoid handling it programmatically I want to move some logic into upstream system which is Oracle 9i.

    Thanks a lot for your help!

    Regards,
    Pit.

  8. #8
    Join Date
    May 2002
    Posts
    34
    Quote Originally Posted by flyboy
    Yes, analytical function is the way to go; the PARTITION BY clause looks good, but you shall use LEAD function to get the value from the next row.
    By the way, what is the difference between sysdate and current_date in your description? Can you follow it with some value, e.g. for sysdate = June 30th, 2008?

    [Edit: typo]
    Hello flyboy
    I am running into some issues running LEAD function
    Here are examples:
    When I run this SQL
    ***
    select
    partid
    ,time_installed
    ,location
    ,row_number() over (partition by partid order by time_installed asc) as rnk
    from parts a
    ***
    I get this result
    ***
    PARTID TIME_INSTALLED LOCATION RNK
    P1 12/20/2007 L1 1
    P1 12/31/2007 L2 2
    P1 1/15/2008 L3 3
    P2 2/1/2008 L1 1
    P2 2/5/2008 L2 2
    P2 4/5/2008 L3 3
    P3 4/30/2008 L1 1
    P3 5/3/2008 L1 2
    ***
    which is ranking I expect
    But when I run this SQL
    ***
    select
    partid
    ,time_installed
    ,location
    ,LEAD (time_installed, 1, 0) OVER (partition by partid order by time_installed asc) as next_time_install
    ,row_number() OVER (partition by partid order by time_installed asc) as rnk
    from parts a
    ***
    I am getting error ORA-00932 inconsistent datatypes: expected DATE got NUMBER
    I tried to use to_char:
    ***
    select
    partid
    ,time_installed
    ,location
    ,LEAD(to_char(time_installed), 1, 0) over (partition by partid order by to_char(time_installed) asc) as next_time_install
    ,row_number() over (partition by partid order by time_installed asc) as rnk
    from parts a
    ***
    which runs without throwing errors but now time_installed is not a DATE and it brakes the order of installation dates, notice RNK column, it is out of order
    ***
    PARTID TIME_INSTALLED LOCATION NEXT_TIME_INSTALL RNK
    P1 1/15/2008 L3 20-DEC-07 3
    P1 12/20/2007 L1 31-DEC-07 1
    P1 12/31/2007 L2 0 2
    P2 2/1/2008 L1 05-APR-08 1
    P2 4/5/2008 L3 05-FEB-08 3
    P2 2/5/2008 L2 0 2
    P3 5/3/2008 L1 30-APR-08 2
    P3 4/30/2008 L1 0 1
    ***

    Could you help?

    Thank,
    Pit.

  9. #9
    Join Date
    May 2002
    Posts
    34
    Quote Originally Posted by PeterS
    Hello flyboy
    I am running into some issues running LEAD function
    Here are examples:
    When I run this SQL
    ***
    select
    partid
    ,time_installed
    ,location
    ,row_number() over (partition by partid order by time_installed asc) as rnk
    from parts a
    ***
    I get this result
    ***
    PARTID TIME_INSTALLED LOCATION RNK
    P1 12/20/2007 L1 1
    P1 12/31/2007 L2 2
    P1 1/15/2008 L3 3
    P2 2/1/2008 L1 1
    P2 2/5/2008 L2 2
    P2 4/5/2008 L3 3
    P3 4/30/2008 L1 1
    P3 5/3/2008 L1 2
    ***
    which is ranking I expect
    But when I run this SQL
    ***
    select
    partid
    ,time_installed
    ,location
    ,LEAD (time_installed, 1, 0) OVER (partition by partid order by time_installed asc) as next_time_install
    ,row_number() OVER (partition by partid order by time_installed asc) as rnk
    from parts a
    ***
    I am getting error ORA-00932 inconsistent datatypes: expected DATE got NUMBER
    I tried to use to_char:
    ***
    select
    partid
    ,time_installed
    ,location
    ,LEAD(to_char(time_installed), 1, 0) over (partition by partid order by to_char(time_installed) asc) as next_time_install
    ,row_number() over (partition by partid order by time_installed asc) as rnk
    from parts a
    ***
    which runs without throwing errors but now time_installed is not a DATE and it brakes the order of installation dates, notice RNK column, it is out of order
    ***
    PARTID TIME_INSTALLED LOCATION NEXT_TIME_INSTALL RNK
    P1 1/15/2008 L3 20-DEC-07 3
    P1 12/20/2007 L1 31-DEC-07 1
    P1 12/31/2007 L2 0 2
    P2 2/1/2008 L1 05-APR-08 1
    P2 4/5/2008 L3 05-FEB-08 3
    P2 2/5/2008 L2 0 2
    P3 5/3/2008 L1 30-APR-08 2
    P3 4/30/2008 L1 0 1
    ***

    Could you help?

    Thank,
    Pit.
    I figured out what's wrong:

    select
    partid
    ,time_installed
    ,location
    ,LEAD (time_installed, 1, sysdate) OVER (partition by partid order by time_installed asc) as next_time_install
    ,row_number() OVER (partition by partid order by time_installed asc) as rnk
    from parts a;

    it works perfect1

    Thanks a lot!

Posting Permissions

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