C****n
发帖数: 2324 | 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****c
发帖数: 25 | 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****n
发帖数: 2324 | 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****n
发帖数: 40 | 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 的大作中提到】
: 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.
| s**********i
发帖数: 711 | 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****N
发帖数: 430 | 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****n
发帖数: 2324 | 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**********i
发帖数: 711 | 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****n
发帖数: 2324 | 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.
|
|
