SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Urgent DB Issue
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 03/13/2007 :  12:56:12  Show Profile  Reply with Quote
I have a well UNDESIGNED table EuBoatsPreferencesView and Period
with 1 to many relationship ie 1 boat ha many thing eg, price date etc

The problem I have is that the PeriodPrice is in PERIOD Table
I need to be able to show the users the PeriodPrice



Declare @Price as int
Declare @S as int
Declare @FromDate as varchar(33)
Declare @myPrice as int
SET @Price = 500
SET @S = 71
SET @FromDate = '2007-03-09 00:00:00'



select ID, @myPrice AS PeriodPrice from EuBoatsPreferencesView where ID IN
(
SElECT EuPreferenceID from Period
WHERE
PeriodStartDate >= CONVERT(DATETIME,@FromDate, 102)
AND
PeriodPrice <=@Price
AND
SuperAreaID = @S
)

If it is that easy, everybody will be doing it

AndrewMurphy
Flowing Fount of Yak Knowledge

Ireland
2916 Posts

Posted - 03/13/2007 :  13:52:45  Show Profile  Reply with Quote
Ah c'mon OBINNA_EKE! help us out here...
give us some sample input data and matching expected results....
Go to Top of Page

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 03/13/2007 :  14:01:54  Show Profile  Reply with Quote
primary key is ID
Foriegn Key is EuPreferenceID

Since the PeriodPrice is in PeriodTable
is it possible to get the PeriodPrice displayed

output is
10 NULL
11 NULL
12 NULL
102 NULL

i need output to be

10 657
11 784
12 234
102 633

If it is that easy, everybody will be doing it
Go to Top of Page

JohnH
Starting Member

13 Posts

Posted - 03/13/2007 :  14:10:54  Show Profile  Reply with Quote
So that's the desired output, where's the data that output came from?

John Hopkins
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36787 Posts

Posted - 03/13/2007 :  14:12:42  Show Profile  Visit tkizer's Homepage  Reply with Quote
Instead of @myPrice in your query, use the column that you want. In order to use the column though, you'll need to join to the table that contains the column.

If you need help on the query, then you need to provide example data of your tables and not what your current output is.

Tara Kizer
Go to Top of Page

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 03/13/2007 :  14:14:50  Show Profile  Reply with Quote
The output came from here

select ID, @myPrice AS PeriodPrice from EuBoatsPreferencesView where ID IN
(
SElECT EuPreferenceID from Period
WHERE
PeriodStartDate >= CONVERT(DATETIME,@FromDate, 102)
AND
PeriodPrice <=@Price
AND
SuperAreaID = @S
)


If it is that easy, everybody will be doing it
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36787 Posts

Posted - 03/13/2007 :  14:18:02  Show Profile  Visit tkizer's Homepage  Reply with Quote
I don't see how we can help you with the information that you have provided.

WE NEED TO SEE SAMPLE DATA FROM YOUR TABLES!!!

If your problem is so urgent, you would think that you'd provide the needed information rather than having us read your mind.

Tara Kizer
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30177 Posts

Posted - 03/13/2007 :  14:32:11  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Change the second column!
You are selecting a VARIABLE that has NOT been defined! Therefore you get NULL in return!!!!!!!!!


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30177 Posts

Posted - 03/13/2007 :  14:34:02  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Declare @Price as int
Declare @S as int
Declare @FromDate as varchar(33) datetime
Declare @myPrice as int 
SET @Price = 500
SET @S = 71
SET @FromDate = '2007-03-09 00:00:00'

select ID, @myPrice AS PeriodPrice from EuBoatsPreferencesView where ID IN -- WTF factor is incredible!
(
SELECT EuPreferenceID from Period 
WHERE 
PeriodStartDate >= CONVERT(DATETIME,@FromDate, 102)
AND 
PeriodPrice <=@Price 
AND 
SuperAreaID = @S
)


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30177 Posts

Posted - 03/13/2007 :  14:35:43  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Just use plain
SELECT		EuPreferenceID,
		PeriodPrice
FROM		Period 
WHERE		PeriodStartDate >= @FromDate
		AND PeriodPrice <=@Price 
		AND SuperAreaID = @S


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 03/13/2007 :  14:37:28  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
Over 200 posts, and still no idea how to ask us a question and how to provide any details .... scary.

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000