sqlhelp Posted May 8, 2017 Report Share Posted May 8, 2017 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.. Quote Link to comment Share on other sites More sharing options...
reality Posted May 8, 2017 Report Share Posted May 8, 2017 You are putting RT.STATUS filter outside of subquery, which is wrong... Quote Link to comment Share on other sites More sharing options...
Bhai Posted May 9, 2017 Report Share Posted May 9, 2017 . Quote Link to comment Share on other sites More sharing options...
manchivaadu Posted May 9, 2017 Report Share Posted May 9, 2017 27 minutes ago, Bhai said: . what do you mean by dot? Quote Link to comment Share on other sites More sharing options...
Bhai Posted May 9, 2017 Report Share Posted May 9, 2017 3 minutes ago, manchivaadu said: what do you mean by dot? i would love to help.. malli choosta ani Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.