InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 06:42:38 GMT20http://www.sqlservercentral.com/Forums/Topic1370670-392-1.aspx[quote][b]maida_rh (10/10/2012)[/b][hr]I tried ur solution but stilll code return the same result as my query return ....[/quote][code="sql"] AND NOT ( actiontype in ('withdrawn','Discontinued','Matured','Withdrawn from Public') AND rhdate < (select getdate()-1095) )[/code]This [i]will [/i]return rows where actiontype is in 'withdrawn','Discontinued','Matured','Withdrawn from Public', but only if they are more recent than 1095 days ago. I think you are getting confused with the logic. Try writing down the rules - for us - it will probably help you too.Wed, 10 Oct 2012 04:08:34 GMTChrisM@homehttp://www.sqlservercentral.com/Forums/Topic1370670-392-1.aspx[quote][b]maida_rh (10/10/2012)[/b][hr]I tried ur solution but stilll code return the same result as my query return ....[/quote]Please send some sample data and result obtained and the result expected...Wed, 10 Oct 2012 04:01:59 GMTPiManéhttp://www.sqlservercentral.com/Forums/Topic1370670-392-1.aspxI tried ur solution but stilll code return the same result as my query return ....Wed, 10 Oct 2012 03:40:43 GMTmaida_rhhttp://www.sqlservercentral.com/Forums/Topic1370670-392-1.aspx[code="sql"]SELECT * FROM Vu_CurRating WHERE CatId <> 7 AND NOT ( actiontype in ('withdrawn','Discontinued','Matured','Withdrawn from Public') AND rhdate < (select getdate()-1095) ) AND NOT ( ltrating LIKE 'D%' AND rhdate < (select getdate()-365) )ORDER BY SectorCode, companyname,CatId[/code]Wed, 10 Oct 2012 01:09:35 GMTChrisM@homehttp://www.sqlservercentral.com/Forums/Topic1370670-392-1.aspxI want to eliminate ('withdrawn','Discontinued','Matured','Withdrawn from Public') from the resultant records but not succeedQUERY :[code="sql"]select * from Vu_CurRating where ratingid not in(select ratingid from Vu_CurRating where actiontype in ('withdrawn','Discontinued','Matured','Withdrawn from Public') and rhdate < (select getdate()-1095)) andCatId !=7 and ratingid not in(select ratingid from Vu_CurRating where ltrating like 'D%' and rhdate<(select getdate()-365)) order by SectorCode, companyname,CatId[/code]I want this query like this as it is not eliminating actiontype('withdrawn','Discontinued','Matured','Withdrawn from Public') from the resultant records for this I edit the query :[code="sql"]select * from Vu_CurRating where ratingid not in((select ratingid from Vu_CurRating where actiontype in ('withdrawn','Discontinued','Matured','Withdrawn from Public') andselect ratingid from Vu_CurRating where actionId in ('withdrawn','Discontinued','Matured','Withdrawn from Public') )and rhdate < (select getdate()-1095)) andCatId !=7 and ratingid not in(select ratingid from Vu_CurRating where ltrating like 'D%' and rhdate<(select getdate()-365)) order by SectorCode, companyname,CatId[/code]but still no change except when I change :[code="sql"]select * from Vu_CurRating where ratingid not in((select ratingid from Vu_CurRating where actiontype in ('withdrawn','Discontinued','Matured','Withdrawn from Public') andselect ratingid from Vu_CurRating where actionId in ('withdrawn','Discontinued','Matured','Withdrawn from Public') )) andCatId !=7 and ratingid not in(select ratingid from Vu_CurRating where ltrating like 'D%' and rhdate<(select getdate()-365)) order by SectorCode, companyname,CatId[/code][code="sql"][b]and rhdate < (select getdate()-1095)[/b][/code]Any solution to this ?Thanks in advanceTue, 09 Oct 2012 22:52:35 GMTmaida_rh