7 Replies Latest reply: Dec 2, 2010 3:57 PM by 820364 RSS

    Get priority number based on the time stamp

    820364
      Hello Everyone,
      my question is:
      i need to get the priority number(this number cannot exceed 3 digits i.e., max number can be 999) based on the time stamp value. We had an existing query that would get the priority based on hours and min. Following is the query.

      SELECT ROUND((( TO_NUMBER (TO_CHAR (om.ship_dt, 'HH24')) * 60 + TO_NUMBER (TO_CHAR (om.ship_dt, 'MI')) ) / 24*60 ) * 999)
      FROM ord_mast om
      INTO v_priority
      WHERE ord_key = iordkey;

      I need to modify the above query so that the query also considers the day,month and year values to get the priority number.

      example:
      date priority
      -----------------------------------------------------------
      1/1/2011 1:00:00 AM 900
      12/31/2010 8:53:00 PM 700
      12/31/2010 5:53:00 PM 655
      12/30/2010 8:48:05 PM 600
      11/30/2010 11:59:31 PM 400
      10/30/2010 11:59:31 PM 300

      note: the least the priority number will has the highest precedence. I would really appreciate if any one can help me on this.
        • 1. Re: Get priority number based on the time stamp
          Toon Koppelaars
          user8914785 wrote:
          Hello Everyone,
          my question is:
          i need to get the priority number(this number cannot exceed 3 digits i.e., max number can be 999) based on the time stamp value. We had an existing query that would get the priority based on hours and min. Following is the query.

          SELECT ROUND((( TO_NUMBER (TO_CHAR (om.ship_dt, 'HH24')) * 60 + TO_NUMBER (TO_CHAR (om.ship_dt, 'MI')) ) / 24*60 ) * 999)
          FROM ord_mast om
          INTO v_priority
          WHERE ord_key = iordkey;
          So this query 'maps' a timestamp inside a 24 hour timeframe, onto a number between 0 and 999? You realize that different timestamps could get the same priority number, since the 'density' of the timestamp values is much higher than the 'densitiy' of the integer range [0..999]?
          I need to modify the above query so that the query also considers the day,month and year values to get the priority number.

          example:
          date priority
          -----------------------------------------------------------
          1/1/2011 1:00:00 AM 900
          12/31/2010 8:53:00 PM 700
          12/31/2010 5:53:00 PM 655
          12/30/2010 8:48:05 PM 600
          11/30/2010 11:59:31 PM 400
          10/30/2010 11:59:31 PM 300

          note: the least the priority number will has the highest precedence. I would really appreciate if any one can help me on this.
          So now you want to condense a date range onto the integer interval [0..999] ?
          Well, what is the date range? If you don't have that, then this is almost a mission impossible.

          Unless I misunderstand your question...
          • 2. Re: Get priority number based on the time stamp
            820364
            Thanks for the reply, I am sorry my question was not clear. let me try to explain in detail.
            WELL, there is no date range as such, i just have bunch of dates in the table and i need to generate a priority number(this value should be a 3 digit number since the datatype value where it is stored in the table is number(3)..

            SELECT ROUND((( TO_NUMBER (TO_CHAR (om.ship_dt, 'HH24')) * 60 + TO_NUMBER (TO_CHAR (om.ship_dt, 'MI')) ) / 24*60 ) * 999)
            FROM ord_mast om
            INTO v_priority
            WHERE ord_key = iordkey;

            This query is working perfectly fine for all the dates on the same day. Now i need to modify the query such that query also considers day, month and year to generate the priority number..
            output of my query should be something like this..
            _____________________________________
            date | priority
            ______________________________________
            1/1/2011 1:00:00 AM | 900
            12/31/2010 8:53:00 PM | 700
            12/31/2010 5:53:00 PM | 655
            12/30/2010 8:48:05 PM | 600
            11/30/2010 11:59:31 PM | 400
            10/30/2010 11:59:31 PM | 300

            i'd appreciate if any one can help me on this.
            • 3. Re: Get priority number based on the time stamp
              Frank Kulash
              Hi,

              How to do what you want depends on what you want.
              It looks like you want something that will return higher numbers given later dates. Beyond that, it's hard to guess.

              The following will return
              0 if ship_dt was more than 90 days ago,
              999 if ship_dt is more than 90 days in the future, and
              something in between if ship_dt is somewhere in between.
              SELECT     CASE
                       WHEN  ship_dt < SYSDATE - 90  THEN   0
                       WHEN  ship_dt < SYSDATE + 90  THEN 500 + ROUND ( 449 * (ship_dt - SYSDATE)
                                                                                        / 90
                                                       )
                                         ELSE 999
                   END     AS priority
              ...
              Within the 180-day window, the value increases linearly, than is, each day adds about 5.5 (that is 999/180) to the value. I see that's not actually what you want; you want something that increases at about that rate in through October, but jumps 100 in a single 24-hour period between December 30 and December 31.

              If you can say exactly what you want, I'm sure somebody can help you code it.
              Post CREATE TABLE and INSERT statements for a little sample data, and the exact results you want from that data.
              Explain, with specific examples, how you get those results from that data.
              • 4. Re: Get priority number based on the time stamp
                820364
                @frank: I have tried what you suggested here is the output. i could see few dates having same priority value

                SELECT ship_dt,
                CASE
                WHEN ship_dt < SYSDATE - 90 THEN 0
                WHEN ship_dt < sysdate + 90 THEN 500 + ROUND ( 449 * (ship_dt - SYSDATE)
                / 90
                )
                ELSE 999
                END AS priority
                FROM ord_mast om
                ORDER BY PRIORITY DESC

                O/P:
                _____

                SHIP_DT,PRIORITY
                1/1/2011 1:00:00 AM,251
                12/31/2010 11:59:18 PM,251
                12/31/2010 4:53:00 PM,250
                12/31/2010 8:53:00 PM,250
                12/31/2010 5:53:00 PM,250
                12/31/2010 12:59:18 PM,249
                12/31/2010 8:53:00 AM,248
                12/30/2010 5:48:05 PM,245
                12/30/2010 8:48:05 PM,245
                12/30/2010 1:48:05 PM,244
                12/30/2010 1:48:05 AM,242
                12/29/2010 8:48:05 PM,241
                11/30/2010 11:59:31 PM,96
                11/30/2010 10:59:31 PM,96
                11/30/2010 10:59:31 AM,94
                11/30/2010 12:59:31 AM,92
                11/29/2010 11:59:31 PM,91
                11/29/2010 1:59:31 PM,89
                11/29/2010 12:59:31 AM,87
                • 5. Re: Get priority number based on the time stamp
                  Frank Kulash
                  Hi,
                  user8914785 wrote:
                  @frank: I have tried what you suggested here is the output. i could see few dates having same priority value
                  Is that good or bad? Would more duplicates be better or worse? This is the first time you've menitioned different rows having the same priority value.
                  SELECT ship_dt,
                  CASE
                  WHEN ship_dt < SYSDATE - 90 THEN 0
                  WHEN ship_dt < sysdate + 90 THEN 500 + ROUND ( 449 * (ship_dt - SYSDATE)
                  / 90
                  )
                  ELSE 999
                  END AS priority
                  FROM ord_mast om
                  ORDER BY PRIORITY DESC

                  O/P:
                  _____

                  SHIP_DT,PRIORITY
                  1/1/2011 1:00:00 AM,251
                  12/31/2010 11:59:18 PM,251
                  12/31/2010 4:53:00 PM,250
                  12/31/2010 8:53:00 PM,250
                  12/31/2010 5:53:00 PM,250
                  12/31/2010 12:59:18 PM,249
                  12/31/2010 8:53:00 AM,248
                  12/30/2010 5:48:05 PM,245
                  12/30/2010 8:48:05 PM,245
                  12/30/2010 1:48:05 PM,244
                  12/30/2010 1:48:05 AM,242
                  12/29/2010 8:48:05 PM,241
                  11/30/2010 11:59:31 PM,96
                  11/30/2010 10:59:31 PM,96
                  11/30/2010 10:59:31 AM,94
                  11/30/2010 12:59:31 AM,92
                  11/29/2010 11:59:31 PM,91
                  11/29/2010 1:59:31 PM,89
                  11/29/2010 12:59:31 AM,87
                  Do you have a question? If so, post some sample data (CREATE TABLE and INSERT statements) and the results you want from that data. Explain how you get those results from that data.
                  • 6. Re: Get priority number based on the time stamp
                    Nicosa-Oracle
                    Hi,

                    Not sure to understand the requirement correctly, but are you asking for something like that :
                    with bunchOfDates as (
                    ---------- sample data generation -----------
                    select sysdate+dbms_random.value(-100,100) dt
                    from dual
                    connect by level <= 10
                    ------- end of sample data generation -------
                    )
                    select dt,
                    case when rn>1000 then 999 else rn-1 end as priority
                    from (
                    select dt, row_number() over (order by dt asc) rn
                    from bunchOfDates
                    )
                    /
                    
                    DT                    PRIORITY
                    ------------------- ----------
                    28/09/2010 20:47:47          0
                    12/10/2010 21:09:54          1
                    08/11/2010 19:02:08          2
                    02/12/2010 02:42:02          3
                    04/12/2010 18:02:53          4
                    23/12/2010 03:42:59          5
                    27/12/2010 18:40:13          6
                    07/01/2011 13:44:08          7
                    14/02/2011 14:14:31          8
                    05/03/2011 19:08:40          9
                    Using more rows in sample data gives :
                    DT                    PRIORITY
                    ------------------- ----------
                    24/08/2010 15:18:53          0
                    24/08/2010 18:05:19          1
                    25/08/2010 04:15:44          2
                    (....)
                    10/03/2011 06:04:26        994
                    10/03/2011 08:41:54        995
                    10/03/2011 15:49:37        996
                    10/03/2011 19:26:35        997
                    10/03/2011 19:36:40        998
                    11/03/2011 08:19:53        999
                    11/03/2011 09:47:30        999
                    11/03/2011 18:49:13        999
                    11/03/2011 19:29:19        999
                    11/03/2011 23:05:54        999
                    12/03/2011 04:37:08        999
                    
                    1005 rows selected.
                    • 7. Re: Get priority number based on the time stamp
                      820364
                      Here are some of the points:
                      1. the priority number should always be the same number anytime we query that particular timestamp. Formula should consider minutes, hours, months and year to generate a number. higher date should have higher priority number than the one lower date.
                      for example
                      12/2/2010 8:50:02 AM -- 850
                      12/2/2010 7:00:00 AM-- 750

                      Note: the above numbers are not the exact numbers that the formula has generated.