JoIN IN T SQL

Question

Post reply

JoIN IN T SQL

cpinky01

Ten Centuries

Points: 1144
More actions
September 16, 2010 at 11:20 am

#224076

HI HAVE WRITTEN A CNDITION LIKE THIS.
select cast(right(201008,2) + '/01/' + left(201008,4) as datetime), ARD.PeriodMonth,RMP.StartMonth, RMP.EndMonth,RMP.StartDate, RMP.EndDate from Report.AggResponseData ard
inner join Report.refMonPeriod RMP ON
(cast(right(ARD.PeriodMonth,2) + '/01/' + left(ARD.PeriodMonth,4) as datetime)
BETWEEN
(select StartDate as 'RP Start Date' from [IAG].[Report].[refMonPeriod] where periodid = 8) and
(select (dateadd(m,11,enddate)) as 'RP End Date' from [IAG].[Report].[refMonPeriod] where periodid = 8) )
whatever the period id i give it is returing all the periods tresspective og whether taht date lies in between teh selected range or not.
Can join on bewteen condition.
please let m eknow the error
if i put RMP.PeriodID =8 instead of PeriodID=8 Then it says an error that sub query returned more tahn one value.
i have to finish this task today. please help me out.
Thank you
thank you.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

BETWEEN requires a pair of values;

BETWEEN X AND Y

your query:

select StartDate as 'RP Start Date' from [IAG].[Report].[refMonPeriod] where RMP.periodid = 8

returns more than one row...if you cahnge it to select the min or the TOP 1, it should work:

select MIN(StartDate) as 'RP Start Date' from [IAG].[Report].[refMonPeriod] where RMP.periodid = 8

select TOP 1 StartDate as 'RP Start Date' from [IAG].[Report].[refMonPeriod] where RMP.periodid = 8

the same is true for your calculation of the end date.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

cpinky01 Ten Centuries Points: 1144 More actions · Answer 2

THANKS FOR REPLYING ME.

In the between clause it is returing only one value not more than one.

it just returns the start date and end date of that period.

I have not got the solution for this.

please help me out with this.

Thank you

Lowell SSC Guru Points: 323518 More actions · Answer 3

whetehr you beleive it or not, it's returning more than one value:

if i put RMP.PeriodID =8 instead of PeriodID=8 Then it says an error that sub query returned more than one value.

did you try changing your code to the example i posted? what happened?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!