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 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 59996
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 #573072 is a reply to message #573069] Thu, 20 December 2012 09:14 Go to previous messageGo to next message
BlackSwan
Messages: 23151
Registered: January 2009
Senior Member
> What I try to do, is to insert values into a new field (Price_END_DAY)of the table.
Words have meaning when it comes to RDBMS. Do you really "INSERT" column PRICE_END_DAY; as opposed to "UPDATE"?

>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)
at what point is time is PRICE_END_DAY populated?

It is generally a Bad Thing to store computed value (PRICE_END_DAY) in a static table.
Re: Compare last price of the day and present price [message #573074 is a reply to message #573067] Thu, 20 December 2012 09:25 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2078
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 #573080 is a reply to message #573076] Thu, 20 December 2012 11:27 Go to previous messageGo to next message
marcopo
Messages: 24
Registered: December 2012
Junior Member
Thank you very much!

But one issue still occurs. Since the Timestamps are sometimes equal...In the end I get this error.

01427. 00000 - "single-row subquery returns more than one row"

UPDATE test
SET    price_end_day = (SELECT t.trendcase
                        FROM   (SELECT trade_date,
                                       price,
                                       CASE ( price - Last_value(price)
                                                        over(
                                                          PARTITION BY Trunc(
                                                        trade_date)
                                                          ORDER BY datetime 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 trendcase
                                FROM   test
                                ORDER  BY trade_date)t);  



Best Regards

Marco
Re: Compare last price of the day and present price [message #573083 is a reply to message #573080] Thu, 20 December 2012 12:07 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2078
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.
Re: Compare last price of the day and present price [message #573118 is a reply to message #573083] Fri, 21 December 2012 01:47 Go to previous message
marcopo
Messages: 24
Registered: December 2012
Junior Member
Thank you very much!. It works well!
Previous Topic: source does not have a runnable target
Next Topic: Values to columns of another table
Goto Forum:
  


Current Time: Sat Dec 20 06:01:18 CST 2014

Total time taken to generate the page: 0.08380 seconds