Prev: nth line where column not null
Next: CPACKET
From: aj on 8 Apr 2010 14:41
I am a novice and need assistance.


I have a table

create table Data_price (
name varchar (34),
current_price dec (12,2),
current_prc_date datetime,
prev_price dec (12,2),
prev_prc_date datetime
)

insert into Data_price values ( 'How to help', 24.99,
04/06/2010,29.99, 01/16/2010)
insert into Data_price values ( 'Asian Culinary', 22.99,
12/06/2009,24.99, 10/14/2009)
insert into Data_price values ( 'Asian Culinary', 16.99,
04/03/2010,22.99, 12/06/2009)
insert into Data_price values ( 'I am the best', 11.97,
04/05/2010,15.99, 11/12/2009)
insert into Data_price values ( 'How to invest', 27.99,
04/05/2010,29.99, 10/21/2009)

I need help writing a query that will display data in the following
format (I tried by inserting data for current and prv into a tmp table
then using a join to output data in the format below.)

How to help 24.99 04/06/2010
How to help 29.99 01/16/2010
Asian Culinary 22.99 12/06/2009
Asian Culinary 24.99 10/14/2009
Asian Culinary 16.99 04/03/2010
I am the best 11.97 04/05/2010
I am the best 15.99 11/12/2009
How to invest 27.99 04/05/2010
How to invest 29.99 10/21/2009


Thank you
Aj
From: Mark Fitzgerald on 8 Apr 2010 15:03
select name,current_prc_date as PriceDated,current_price as Price from
Data_price
UNION
select name,prev_prc_date,prev_price from Data_price

But you should really change your design to :

create table Data_price (
name varchar (34),
price dec (12,2),
effective_date datetime
)

"aj" <ajmiester(a)gmail.com> wrote in message
news:b0db2b87-6eb1-4a4b-9a29-59c9383ddd48(a)e7g2000yqf.googlegroups.com...
> I am a novice and need assistance.
>
>
> I have a table
>
> create table Data_price (
> name varchar (34),
> current_price dec (12,2),
> current_prc_date datetime,
> prev_price dec (12,2),
> prev_prc_date datetime
> )
>
> insert into Data_price values ( 'How to help', 24.99,
> 04/06/2010,29.99, 01/16/2010)
> insert into Data_price values ( 'Asian Culinary', 22.99,
> 12/06/2009,24.99, 10/14/2009)
> insert into Data_price values ( 'Asian Culinary', 16.99,
> 04/03/2010,22.99, 12/06/2009)
> insert into Data_price values ( 'I am the best', 11.97,
> 04/05/2010,15.99, 11/12/2009)
> insert into Data_price values ( 'How to invest', 27.99,
> 04/05/2010,29.99, 10/21/2009)
>
> I need help writing a query that will display data in the following
> format (I tried by inserting data for current and prv into a tmp table
> then using a join to output data in the format below.)
>
> How to help 24.99 04/06/2010
> How to help 29.99 01/16/2010
> Asian Culinary 22.99 12/06/2009
> Asian Culinary 24.99 10/14/2009
> Asian Culinary 16.99 04/03/2010
> I am the best 11.97 04/05/2010
> I am the best 15.99 11/12/2009
> How to invest 27.99 04/05/2010
> How to invest 29.99 10/21/2009
>
>
> Thank you
> Aj

From: aj on 8 Apr 2010 16:06
Thx... The table format was created a while back. I am trying to
extract data to populate a new table (created in the format you
suggested).


On Apr 8, 3:03 pm, "Mark Fitzgerald" <notes_...(a)hotmail.com> wrote:
> select name,current_prc_date as PriceDated,current_price as Price from
> Data_price
> UNION
> select name,prev_prc_date,prev_price from Data_price
>
> But you should really change your design to :
>
> create table Data_price (
>     name varchar (34),
>     price dec (12,2),
>     effective_date datetime
> )
>
> "aj" <ajmies...(a)gmail.com> wrote in message
>
> news:b0db2b87-6eb1-4a4b-9a29-59c9383ddd48(a)e7g2000yqf.googlegroups.com...
>
> > I am a novice and need assistance.
>
> > I have a table
>
> > create table Data_price (
> >   name varchar (34),
> >   current_price dec (12,2),
> >   current_prc_date datetime,
> >   prev_price dec (12,2),
> >   prev_prc_date datetime
> > )
>
> > insert into Data_price values ( 'How to help', 24.99,
> > 04/06/2010,29.99, 01/16/2010)
> > insert into Data_price values ( 'Asian Culinary', 22.99,
> > 12/06/2009,24.99, 10/14/2009)
> > insert into Data_price values ( 'Asian Culinary', 16.99,
> > 04/03/2010,22.99, 12/06/2009)
> > insert into Data_price values ( 'I am the best', 11.97,
> > 04/05/2010,15.99, 11/12/2009)
> > insert into Data_price values ( 'How to invest', 27.99,
> > 04/05/2010,29.99, 10/21/2009)
>
> > I need help writing a query that will display data in the following
> > format (I tried by inserting data for current and prv into a tmp table
> > then using a join to output data in the format below.)
>
> > How to help  24.99  04/06/2010
> > How to help  29.99  01/16/2010
> > Asian Culinary 22.99 12/06/2009
> > Asian Culinary 24.99 10/14/2009
> > Asian Culinary 16.99 04/03/2010
> > I am the best 11.97 04/05/2010
> > I am the best 15.99 11/12/2009
> > How to invest 27.99 04/05/2010
> > How to invest 29.99 10/21/2009
>
> > Thank you
> > Aj

Thank you
From: --CELKO-- on 8 Apr 2010 17:04
Your design is wrong for a history table. Can I assume that you know
what an ISBN is? The table ought to look like this skeleton:

CREATE TABLE Book_Price_History
(isbn CHAR(13) NOT NULL PRIMARY KEY,
book_title VARCHAR (34) NOT NULL,
book_price DECIMAL (12,2) NOT NULL
CHECK (book_price >= 0.00),
price_start_date DATE DEFAULT CURRENT_DATE NOT NULL,
price_end_date DATE, – null means current
CHECK (price_start_date <= price_end_date),
etc );

Time is a continuum and needs to be modeled with a pair of endpoints
for a half-open interval. Get a copy of THINKING IN SETS; it should
help you a lot.

From: m on 9 Apr 2010 21:02
Does the book title vary in time too?

Also, the 1NF schema only has start date - the end date is only important to
make reasonable queries possible ;)

"--CELKO--" <jcelko212(a)earthlink.net> wrote in message
news:5686ec72-3552-4c65-97ec-5e244676459e(a)e7g2000yqf.googlegroups.com...
> Your design is wrong for a history table. Can I assume that you know
> what an ISBN is? The table ought to look like this skeleton:
>
> CREATE TABLE Book_Price_History
> (isbn CHAR(13) NOT NULL PRIMARY KEY,
> book_title VARCHAR (34) NOT NULL,
> book_price DECIMAL (12,2) NOT NULL
> CHECK (book_price >= 0.00),
> price_start_date DATE DEFAULT CURRENT_DATE NOT NULL,
> price_end_date DATE, � null means current
> CHECK (price_start_date <= price_end_date),
> etc );
>
> Time is a continuum and needs to be modeled with a pair of endpoints
> for a half-open interval. Get a copy of THINKING IN SETS; it should
> help you a lot.
>
 |  Next  |  Last
Pages: 1 2
Prev: nth line where column not null
Next: CPACKET