Help on SQL statement - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

Help on SQL statement

Help on SQL statement# Database - 数据库

C*n2002-10-29 08:10

1 楼

Hello,
I have a table, has:
ProductID, Price, CreateDate
I need to create a view, has:
ProductID, LatestPriceof1999,Latestpriceof2000,Latestpriceof2001
Latestpriceof1999 means the latest price in year 1999.
Assume there're only data for 1999,2000,2001.
Who can help me out?
Note: I am creating a view, if you come up with a stored procedure
solution, don't answer.
If you Select statement is more than 2 pages, don't answer.
Demo Data:
1 1

a*c2002-10-29 08:10

2 楼

Try this.
Create or Replace View Latest_Price
as
select '1999' Year, ProductID, Price LatestPrice
from DEMO_TABLE a
where CreateDate = (
select max(CreateDate)
from DEMO_TABLE b
where b.ProductID = a.ProductID
and b.CreateDate between to_date('01-JAN-99') and to_date('31-DEC-99') )
union all
select '2000' Year, ProductID, Price LatestPrice
from DEMO_TABLE a
where CreateDate = (
select max(CreateDate)
from DEMO_TABLE b
where b.ProductID = a.ProductID
and b.CreateDate between to_date('01-JAN-00')

【在 C****n 的大作中提到】

: Hello,
: I have a table, has:
: ProductID, Price, CreateDate
: I need to create a view, has:
: ProductID, LatestPriceof1999,Latestpriceof2000,Latestpriceof2001
: Latestpriceof1999 means the latest price in year 1999.
: Assume there're only data for 1999,2000,2001.
: Who can help me out?
: Note: I am creating a view, if you come up with a stored procedure
: solution, don't answer.

C*n2002-10-29 08:10

3 楼

It will work.
But I need something like:
productID, price1999,price2000,price2001
4 columns
not 3 columns like:
productID, year, price.
Calvin

【在 a****c 的大作中提到】

: Try this.
: Create or Replace View Latest_Price
: as
: select '1999' Year, ProductID, Price LatestPrice
: from DEMO_TABLE a
: where CreateDate = (
: select max(CreateDate)
: from DEMO_TABLE b
: where b.ProductID = a.ProductID
: and b.CreateDate between to_date('01-JAN-99') and to_date('31-DEC-99') )

g*n2002-10-29 08:10

4 楼

select productID,
max(LatestPriceof1999) LatestPriceof1999,
max(LatestPriceof2000) LatestPriceof2000,
max(LatestPriceof2001) LatestPriceof2001
from (
select productID,
decode(to_char(createdate, 'YYYY'), '1999', price, null) LatestPriceof1999,
decode(to_char(createdate, 'YYYY'), '2000', price, null) LatestPriceof2000,
decode(to_char(createdate, 'YYYY'), '2001', price, null) LatestPriceof2001
from product
where (createdate, productid) in (
select max(createdate

【在 C****n 的大作中提到】

s*i2002-10-29 08:10

5 楼

I believe you get max prices of 1999, 2000, 2001
instead of lastest prices.

【在 g****n 的大作中提到】

: select productID,
: max(LatestPriceof1999) LatestPriceof1999,
: max(LatestPriceof2000) LatestPriceof2000,
: max(LatestPriceof2001) LatestPriceof2001
: from (
: select productID,
: decode(to_char(createdate, 'YYYY'), '1999', price, null) LatestPriceof1999,
: decode(to_char(createdate, 'YYYY'), '2000', price, null) LatestPriceof2000,
: decode(to_char(createdate, 'YYYY'), '2001', price, null) LatestPriceof2001
: from product

D*N2002-10-29 08:10

6 楼

this query is enlightening!..
i want go back to work right now and rewrite our
massive self-joins-that-doesn't-work.. :)

【在 s**********i 的大作中提到】

: I believe you get max prices of 1999, 2000, 2001
: instead of lastest prices.

C*n2002-10-29 08:10

7 楼

This is a real world question asked by my Ex-GF, she's a CPA. She has an EXCEL
file including ProductID, Price, and Date, and she's trying to get the latest
price of every fiscal year for every product.
The the actual question is even more complicated because the fiscal year is
different than our year, i.e., Fiscal year of 1999 is actually between
10/01/1998 - 09/30/1999 instead of simply year 1999.
I was driven mad because she works in ACCESS, and ACCESS SQL is different to
any other T-SQL or P

【在 D****N 的大作中提到】

: this query is enlightening!..
: i want go back to work right now and rewrite our
: massive self-joins-that-doesn't-work.. :)

s*i2002-10-29 08:10

8 楼

unlikely the prices for past years will change again thus
this look like a one time thing for me. why have to spend
tons of time to find out a SQL to do it rather than simply
pull some data out and use whatver your preferred language
to do some simple processing.

【在 C****n 的大作中提到】

: This is a real world question asked by my Ex-GF, she's a CPA. She has an EXCEL
: file including ProductID, Price, and Date, and she's trying to get the latest
: price of every fiscal year for every product.
: The the actual question is even more complicated because the fiscal year is
: different than our year, i.e., Fiscal year of 1999 is actually between
: 10/01/1998 - 09/30/1999 instead of simply year 1999.
: I was driven mad because she works in ACCESS, and ACCESS SQL is different to
: any other T-SQL or P

C*n2002-10-29 08:10

9 楼

Yea, this is one time thing.
You mean to write a program t od this? It's really over kill. A CPA won't
understand what you are talk about, and she won't know what to do.
a SQL is the simplest solution cause she can just copy and paste and got wgat
she wants.
Here is the SQL I worked out, no joins. :-)
select ProductID,
Max(IIF(Date > "1999-09-30" and Date <="2000-09-30", DateDiff("D",Date,
"1999-09-30") * 100000 + price, 0)) Mod 100000 as LatestPriceOf2000,
Max(IIF(Date > "2000-09-30" and Date <

【在 s**********i 的大作中提到】

:
: unlikely the prices for past years will change again thus
: this look like a one time thing for me. why have to spend
: tons of time to find out a SQL to do it rather than simply
: pull some data out and use whatver your preferred language
: to do some simple processing.