Difficult question

24 Nov 2005 6:46 PM

Mark Nijhof

Hello,

I have a table with stock values, they are grouped by a stock id. now I want
to get the trend of each stock. I only need this for one value per week for
one month.
for example:
stockid price
1          20
2         10
3         5
1         21
2         9
3         5
1         24
2         8
3         4
1         28
2         5
3         5

now I wan tot group them like:
stockid price1 price2 price3 price4
1          20      21       24      28
2          10      9         8        5
3          5        5         4        5

This way I can tell what the trend of the prices are, going up or down.

Or is there an other way of doing this?

Any help is appreciated,

-Mark

24 Nov 2005 7:18 PM

--CELKO--

Your design is wrong; we need a date or something by which to arange
the prices. Get a book on basic RDBMS and read Dr. Codd's 12 rules.
Look at the rule about using scalar values in columns of tables to
model all relationships.

SELECT ticker_sym,
            CASE WHEN quote_date = '2005-11-22'
                        THEN price END AS price_1,
            CASE WHEN quote_date = '2005-11-23'
                        THEN price END AS price_2,
            CASE WHEN quote_date = '2005-11-24'
                        THEN price END AS price_3
FROM StockHistory
GROUP BY ticker_sym;

24 Nov 2005 7:29 PM

Mark Nijhof

Thanks,

Well actually there is a date column and other columns as well, I just used
these because I thought they where the importante ones, my mistake.

Table Def:
ID (PK)
StockID (FK)
Date (datetime)
ClosePrice (money)

This gives me the following result:

StockID, P1, P2, P3, P4
1, 20, NULL, NULL, NULL
1, NULL, 21, NULL, NULL
1, NULL, NULL, 24, NULL
1, NULL, NULL, NULL, 28

What I would like is
StockID, P1, P2, P3, P4
1, 20, 21, 24, 28

-Mark

Show quote

"--CELKO--" <jcelko***@earthlink.net> wrote in message
news:1132859937.260224.130920@f14g2000cwb.googlegroups.com...
> Your design is wrong; we need a date or something by which to arange
> the prices. Get a book on basic RDBMS and read Dr. Codd's 12 rules.
> Look at the rule about using scalar values in columns of tables to
> model all relationships.
>
> SELECT ticker_sym,
>            CASE WHEN quote_date = '2005-11-22'
>                        THEN price END AS price_1,
>            CASE WHEN quote_date = '2005-11-23'
>                        THEN price END AS price_2,
>            CASE WHEN quote_date = '2005-11-24'
>                        THEN price END AS price_3
> FROM StockHistory
> GROUP BY ticker_sym;
>

24 Nov 2005 7:50 PM

--CELKO--

SELECT ticker_sym,
           SUM( CASE WHEN quote_date = '2005-11-22'
                        THEN price END) AS price_1,
           SUM( CASE WHEN quote_date = '2005-11-23'
                        THEN price END) AS price_2,
           SUM( CASE WHEN quote_date = '2005-11-24'
                        THEN price END) AS price_3
FROM StockHistory
GROUP BY ticker_sym;

24 Nov 2005 8:01 PM

Mark Nijhof

Thanks Celko,

Show quote

"--CELKO--" <jcelko***@earthlink.net> wrote in message
news:1132861841.665270.278530@g14g2000cwa.googlegroups.com...
> SELECT ticker_sym,
>           SUM( CASE WHEN quote_date = '2005-11-22'
>                        THEN price END) AS price_1,
>           SUM( CASE WHEN quote_date = '2005-11-23'
>                        THEN price END) AS price_2,
>           SUM( CASE WHEN quote_date = '2005-11-24'
>                        THEN price END) AS price_3
> FROM StockHistory
> GROUP BY ticker_sym;
>