Home » SQL & PL/SQL » SQL & PL/SQL » Selecting Second Option
Selecting Second Option [message #255954] Thu, 02 August 2007 09:41 Go to next message
mutton0820
Messages: 3
Registered: August 2007
Junior Member
Hello,

I'm attempting to run a query where for each date in a range, a price is returned along with the date. However, for each date, there are two prices, an 'actual' and an 'estimate', although for some dates there is not an 'actual'. So, in the query, for each date, I need to bring in the price, which will be the actual if it exists and the estimate if it does not.

Select returns.date, returns.price From returns Where ???

Thank you very much for your help!

Matt
Re: Selecting Second Option [message #255956 is a reply to message #255954] Thu, 02 August 2007 09:49 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Matt !! can you please provide some sample data.so can try on that.
Re: Selecting Second Option [message #255957 is a reply to message #255954] Thu, 02 August 2007 09:53 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
However, for each date, there are two prices, an 'actual' and an 'estimate'.

Where are they? As you did not post your table description (desc returns in sqlplus; relevant columns would suffice), I may only guess what your issue is.

Just curious whether you are able to distinguish actual and estimate price. If so, how?
Re: Selecting Second Option [message #255960 is a reply to message #255957] Thu, 02 August 2007 10:01 Go to previous messageGo to next message
mutton0820
Messages: 3
Registered: August 2007
Junior Member
My apologies. The table looks like:

Date Type Price
5/31/2001 actual 111.00
6/30/2001 actual 112.00
6/30/2001 estimate 114.00
7/31/2001 actual 117.00
7/31/2001 estimate 119.00
8/31/2001 actual 121.00
8/31/2001 estimate 119.00
9/30/2001 estimate 135.00
10/31/2001 actual 141.00
10/31/2001 estimate 137.00
11/30/2001 actual 155.00
11/30/2001 estimate 167.00
12/31/2001 actual 175.00
12/31/2001 estimate 174.00
1/31/2002 estimate 189.00
2/28/2002 estimate 202.00

So, I want to run a query where my returned table
looks like

5/31/2001 actual 111.00
6/30/2001 actual 112.00
7/31/2001 actual 117.00
8/31/2001 actual 121.00
9/30/2001 estimate 135.00
10/31/2001 actual 141.00
11/30/2001 actual 155.00
12/31/2001 actual 175.00
1/31/2002 estimate 189.00
2/28/2002 estimate 202.00

Where the estimate is used if the actual is not available.

Thank you!
Re: Selecting Second Option [message #255972 is a reply to message #255960] Thu, 02 August 2007 10:35 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
i have created table like this

create table tmp11(d date,flag varchar2(30),num number)



out put is as:-

8/2/2007 11:22:45 AM,actual,11
8/2/2007 11:22:59 AM,estimate ,12
8/1/2007 11:23:10 AM,estimate ,12
7/31/2007 11:23:21 AM,estimate ,13
7/31/2007 11:23:32 AM,actual,13



query is as-:

select d,flag,num 
from (select row_number() over(partition by trunc(D) order by flag) rn,d,flag,num from tmp11) 
where rn=1


output is as

7/31/2007 11:23:32 AM,actual,13
8/1/2007 11:23:10 AM,estimate ,12
8/2/2007 11:22:45 AM,actual,11



--Yash

[Updated on: Thu, 02 August 2007 12:46] by Moderator

Report message to a moderator

Re: Selecting Second Option [message #256002 is a reply to message #255954] Thu, 02 August 2007 13:09 Go to previous messageGo to next message
mutton0820
Messages: 3
Registered: August 2007
Junior Member
Thank you.

That makes sense. I still have a problem tough with selecting the row number. I see that we are taking the first row from the sorted subquery, but the I get an error when I attempt to execute. I'm trying to do this via ADODB in vba. I get an error on the cmd.Execute step

Set cmd = New ADODB.Command
cmd.ActiveConnection = cnMTPS
cmd.CommandType = adCmdText

cmd.CommandText = "SELECT Date, type, price " & _
"FROM (SELECT row_number() OVER(partition by trunc(date) ORDER BY type) " & _
"date, type, price FROM stu) " & _
"WHERE rn=1"

cmd.Execute
Set rst = cmd.Execute
Re: Selecting Second Option [message #256003 is a reply to message #256002] Thu, 02 August 2007 13:12 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't see any error in your post.
But maybe you think we have a crystal ball to see it.

Regards
Michel
Previous Topic: all_tab_columns distinct column_name
Next Topic: number format
Goto Forum:
  


Current Time: Sun Dec 04 06:20:10 CST 2016

Total time taken to generate the page: 0.07132 seconds