Home » SQL & PL/SQL » SQL & PL/SQL » Compare last price of the day and present price (Oracle 11g, 11.2.0.3, Windows)
| Compare last price of the day and present price [message #573067] |
Thu, 20 December 2012 08:08  |
 |
marcopo
Messages: 24
Registered: December 2012
|
Junior Member |
|
|
Dear Community,
I'm a newbie in Oracle and I'm confronted with the following issue.
At first, I have a table with 2 fields (Datetime(Timestamp(6)) and Price(Number(10,2))). The price and the date correspond to the moment of a trade. In this context, it exists no continuous time statements. What I try to do, is to insert values into a new field (Price_END_DAY)of the table. The field Price_END_DAY belongs to the value of the last price of the day compared to the present price. The value can be (DOWN, UP or STABLE)
Example Table
Row Datetime Price Price_END_DAY
1 01.11.09 11:20:10 10,1 DOWN
2 01.11.09 11:21:17 13,1 DOWN
3 01.11.09 12:20:56 15,1 DOWN
4 01.11.09 13:20:47 16,1 DOWN
5 01.11.09 14:20:12 16,1 DOWN
6 01.11.09 15:20:31 12,2 DOWN
7 01.11.09 16:20:17 17,0 DOWN
8 01.11.09 17:20:55 7,1 UP
9 01.11.09 18:02:31 9,1 NULL
10 02.11.09 13:00:12 10,2 DOWN
11 02.11.08 15:00:44 7,3 NULL
12 03.11.08 12:00:12 10,0 STABLE
13 03.11.08 18:12:11 10,0 NULL
Anyone an idee how to make that possible? Thanks in advance.
Marco
|
|
|
|
| Re: Compare last price of the day and present price [message #573069 is a reply to message #573067] |
Thu, 20 December 2012 08:19  |
 |
Michel Cadot
Messages: 54167
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.
With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Post this in text, inline, no image, no attachment.
Regards
Michel
|
|
|
|
|
|
| Re: Compare last price of the day and present price [message #573074 is a reply to message #573067] |
Thu, 20 December 2012 09:25 |
|
marcopo
Messages: 24
Registered: December 2012
|
Junior Member |
|
|
Hello,
CREATE TABLE "INFODB"."TEST"
("TRADE_DATE" TIMESTAMP (6), "PRICE" NUMBER(5,2),"PRICE_END_DAY" VARCHAR2(10 BYTE))
TABLESPACE "USERS" ;
INSERT INTO "INFODB"."test"
(trade_date,
price)
VALUES (To_timestamp('15.12.12 15:43:42,963000000',
'DD.MM.RR HH24:MI:SS,FF'),
'10');
INSERT INTO "INFODB"."test"
(trade_date,
price)
VALUES (To_timestamp('15.12.12 16:33:13,963000000',
'DD.MM.RR HH24:MI:SS,FF'),
'9');
INSERT INTO "INFODB"."test"
(trade_date,
price)
VALUES (To_timestamp('15.12.12 17:42:42,963000000',
'DD.MM.RR HH24:MI:SS,FF'),
'8');
INSERT INTO "INFODB"."test"
(trade_date,
price)
VALUES (To_timestamp('15.12.12 18:43:42,963000000',
'DD.MM.RR HH24:MI:SS,FF'),
'9');
INSERT INTO "INFODB"."test"
(trade_date,
price)
VALUES (To_timestamp('16.12.12 15:43:42,963000000',
'DD.MM.RR HH24:MI:SS,FF'),
'8');
INSERT INTO "INFODB"."test"
(trade_date,
price)
VALUES (To_timestamp('16.12.12 18:43:42,963000000',
'DD.MM.RR HH24:MI:SS,FF'),
'5');
INSERT INTO "INFODB"."test"
(trade_date,
price)
VALUES (To_timestamp('17.12.12 14:13:42,963000000',
'DD.MM.RR HH24:MI:SS,FF'),
'7');
INSERT INTO "INFODB"."test"
(trade_date,
price)
VALUES (To_timestamp('17.12.12 16:33:42,963000000',
'DD.MM.RR HH24:MI:SS,FF'),
'4');
INSERT INTO "INFODB"."test"
(trade_date,
price)
VALUES (To_timestamp('18.12.12 15:43:42,963000000',
'DD.MM.RR HH24:MI:SS,FF'),
'6');
INSERT INTO "INFODB"."test"
(trade_date,
price)
VALUES (To_timestamp('18.12.12 18:43:42,963000000',
'DD.MM.RR HH24:MI:SS,FF'),
'8');
If the present price lower as the last price of the day than UP
If the present price the same as the last price of the day than STABLE
If the present price higher as the last price of the day than DOWN
The result table should look like this.
Row -- Trade_Date -- Price -- Price_END_DAY
1 -- 15.12.12 15:43:42 -- 10 -- DOWN
2 -- 15.12.12 16:33:13 -- 9 -- STABLE
3 -- 15.12.12 17:42:42 -- 8 -- UP
4 -- 15.12.12 18:43:42 -- 9 -- NULL
5 -- 16.12.12 15:43:42 -- 8 -- DOWN
6 -- 16.12.12 18:43:42 -- 5 -- NULL
7 -- 17.12.12 14:13:42 -- 7 -- DOWN
8 -- 17.12.12 16:33:42 -- 4 -- NULL
9 -- 18.12.12 15:43:42 -- 6 -- UP
10 -- 18.12.12 18:43:42 -- 8 -- DOWN
Thanks you
|
|
|
|
| Re: Compare last price of the day and present price [message #573076 is a reply to message #573074] |
Thu, 20 December 2012 10:05 |
Solomon Yakobson
Messages: 1398
Registered: January 2010
|
Senior Member |
|
|
select trade_date,
price,
case sign(
price - last_value(price) over(
partition by trunc(trade_date)
order by trade_date
rows between current row and unbounded following
)
)
when 1 then 'DOWN'
when 0 then case row_number() over(partition by trunc(trade_date) order by trade_date desc)
when 1 then null
else 'STABLE'
end
else 'UP'
end price_end_day
from test
order by trade_date
/
TRADE_DATE PRICE PRICE_
----------------------------------- ---------- ------
15-DEC-12 03.43.42.963000 PM 10 DOWN
15-DEC-12 04.33.13.963000 PM 9 STABLE
15-DEC-12 05.42.42.963000 PM 8 UP
15-DEC-12 06.43.42.963000 PM 9
16-DEC-12 03.43.42.963000 PM 8 DOWN
16-DEC-12 06.43.42.963000 PM 5
17-DEC-12 02.13.42.963000 PM 7 DOWN
17-DEC-12 04.33.42.963000 PM 4
18-DEC-12 03.43.42.963000 PM 6 UP
18-DEC-12 06.43.42.963000 PM 8
10 rows selected.
SQL>
SY.
Missed you want NULL for last price of the day.
[Updated on: Thu, 20 December 2012 10:37] Report message to a moderator |
|
|
|
|
|
| Re: Compare last price of the day and present price [message #573083 is a reply to message #573080] |
Thu, 20 December 2012 12:07 |
Solomon Yakobson
Messages: 1398
Registered: January 2010
|
Senior Member |
|
|
SQL> select *
2 from test
3 /
TRADE_DATE PRICE PRICE_END_
----------------------------------- ---------- ----------
15-DEC-12 03.43.42.963000 PM 10
15-DEC-12 04.33.13.963000 PM 9
15-DEC-12 05.42.42.963000 PM 8
15-DEC-12 06.43.42.963000 PM 9
16-DEC-12 03.43.42.963000 PM 8
16-DEC-12 06.43.42.963000 PM 5
17-DEC-12 02.13.42.963000 PM 7
17-DEC-12 04.33.42.963000 PM 4
18-DEC-12 03.43.42.963000 PM 6
18-DEC-12 06.43.42.963000 PM 8
10 rows selected.
SQL> merge
2 into test t1
3 using (
4 select rowid rid,
5 case sign(
6 price - last_value(price)
7 over(
8 partition by trunc(trade_date)
9 order by trade_date
10 rows between current row and unbounded following
11 )
12 )
13 when 1 then 'DOWN'
14 when 0 then case row_number()
15 over(
16 partition by trunc(trade_date)
17 order by trade_date desc
18 )
19 when 1 then null
20 else 'STABLE'
21 end
22 else 'UP'
23 end price_end_day
24 from test
25 ) t2
26 on (
27 t1.rowid = t2.rid
28 )
29 when matched
30 then update
31 set t1.price_end_day = t2.price_end_day
32 /
10 rows merged.
SQL> select *
2 from test
3 /
TRADE_DATE PRICE PRICE_END_
----------------------------------- ---------- ----------
15-DEC-12 03.43.42.963000 PM 10 DOWN
15-DEC-12 04.33.13.963000 PM 9 STABLE
15-DEC-12 05.42.42.963000 PM 8 UP
15-DEC-12 06.43.42.963000 PM 9
16-DEC-12 03.43.42.963000 PM 8 DOWN
16-DEC-12 06.43.42.963000 PM 5
17-DEC-12 02.13.42.963000 PM 7 DOWN
17-DEC-12 04.33.42.963000 PM 4
18-DEC-12 03.43.42.963000 PM 6 UP
18-DEC-12 06.43.42.963000 PM 8
10 rows selected.
SQL>
SY.
|
|
|
|
|
|
Goto Forum:
Current Time: Tue May 21 16:12:37 CDT 2013
Total time taken to generate the page: 0.61658 seconds
|
] 
Members
Search
Help
Register
Login
Home
