Urgent DB Issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Urgent DB Issue

Author

Topic

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2007-03-13 : 12:56:12

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
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-03-13 : 13:52:45

Ah c'mon OBINNA_EKE! help us out here...
give us some sample input data and matching expected results....

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2007-03-13 : 14:01:54

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

JohnH
Starting Member

13 Posts

Posted - 2007-03-13 : 14:10:54

So that's the desired output, where's the data that output came from?

John Hopkins

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-03-13 : 14:12:42

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

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2007-03-13 : 14:14:50

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-03-13 : 14:18:02

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-13 : 14:32:11

Change the second column!
You are selecting a VARIABLE that has NOT been defined! Therefore you get NULL in return!!!!!!!!!

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-13 : 14:34:02

[code]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
)[/code]

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-13 : 14:35:43

Just use plain

SELECT		EuPreferenceID,
		PeriodPrice
FROM		Period 
WHERE		PeriodStartDate >= @FromDate
		AND PeriodPrice <=@Price 
		AND SuperAreaID = @S

Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-13 : 14:37:28

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources