Help with sql query

[sqlhelp]

Hello... 

I am trying to calculate a column called 'New' by joining three tables .. separate ga query chesthe by mentioning different status I get right results but when I combine the two queries right result ravatledu 

Please help! 

--Query 1

select * from Role R

join R_TOOL RT on RT.RID = R.RID and RT.Expire = R.Expire

where R.RID = 55

and R.vdate BETWEEN '02-01-2017' and '02-28-2017'

and R.expire BETWEEN '02-01-2017' and '02-02-2017'

and RT.Status = 'Open'

 

--Query 2 

select distinct (RO.AMOUNT-RT.Price)AS New,RT.ID from RoleOFFER RO

Join R_Tool RT on RT.ID = RO.ID

Where RT.RID = 55

AND Effective_date between dateadd(month, datediff(month, 0, RT.EXPIRE), 0) AND dateadd(month, datediff(month, 0, RT.EXPIRE) +1, 0)

AND RO.SELECT = 'Yes'

AND RT.EXPIRE between '02-01-2017' and '02-28-2017'

AND RT.STATUS = 'Available' 

 

--Combined Query 3

select distinct (RO.AMOUNT-RT.Price)AS New,RT.ID from RoleOFFER RO

Join R_Tool RT on RT.ID = RO.ID

Join (

select * from Role R

--join R_TOOL RT on RT.RID = R.RID and RT.Expire = R.Expire

where R.RID = 55

and R.vdate BETWEEN '02-01-2017' and '02-28-2017'

AND R.expire BETWEEN '02-01-2017' and '02-02-2017'

) AS R on R.RID = RT.ID

Where RT.RID = 55

AND Effective_date between dateadd(month, datediff(month, 0, RT.EXPIRE), 0) AND dateadd(month, datediff(month, 0, RT.EXPIRE) +1, 0)

AND RO.SELECT = 'Yes'

AND RT.EXPIRE between '02-01-2017' and '02-28-2017'

AND RT.STATUS in ( 'Available', 'Open') 

 

Query 3 execute chesina only query 2 lo results vasthunay both combined ravatledu.. 

[reality]

You are putting RT.STATUS filter outside of subquery, which is wrong...

[Bhai]

.

[manchivaadu]

27 minutes ago, Bhai said:

.

what do you mean by dot?

[Bhai]

3 minutes ago, manchivaadu said:

what do you mean by dot?

i would love to help.. malli choosta ani