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

# Get priority number based on the time stamp

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
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.

• ###### 2. Re: Get priority number based on the time stamp
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
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
@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
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
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
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.