Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need help to eliminate values from resultant record using view Expand / Collapse
Author
Message
Posted Tuesday, October 9, 2012 10:52 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 7:39 AM
Points: 46, Visits: 111
I want to eliminate ('withdrawn','Discontinued','Matured','Withdrawn from Public') from the resultant records but not succeed
QUERY :
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)
)
and
CatId !=7 and
ratingid not in
(
select ratingid from Vu_CurRating where ltrating like 'D%' and rhdate<(select getdate()-365)
)
order by SectorCode, companyname,CatId

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 :
select * from Vu_CurRating where ratingid not in
(
(
select ratingid from Vu_CurRating where actiontype in ('withdrawn','Discontinued','Matured','Withdrawn from Public') and
select ratingid from Vu_CurRating where actionId in ('withdrawn','Discontinued','Matured','Withdrawn from Public')
)
and
rhdate < (select getdate()-1095)
)
and
CatId !=7 and
ratingid not in
(
select ratingid from Vu_CurRating where ltrating like 'D%' and rhdate<(select getdate()-365)
)
order by SectorCode, companyname,CatId

but still no change except when I change :

select * from Vu_CurRating where ratingid not in
(
(
select ratingid from Vu_CurRating where actiontype in ('withdrawn','Discontinued','Matured','Withdrawn from Public') and
select ratingid from Vu_CurRating where actionId in ('withdrawn','Discontinued','Matured','Withdrawn from Public')
)
) and
CatId !=7 and
ratingid not in
(
select ratingid from Vu_CurRating where ltrating like 'D%' and rhdate<(select getdate()-365)
)
order by SectorCode, companyname,CatId


and 
rhdate < (select getdate()-1095)

Any solution to this ?
Thanks in advance
Post #1370670
Posted Wednesday, October 10, 2012 1:09 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 10:13 AM
Points: 1,076, Visits: 6,458
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




Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Post #1370721
Posted Wednesday, October 10, 2012 3:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 7:39 AM
Points: 46, Visits: 111
I tried ur solution but stilll code return the same result as my query return ....
Post #1370797
Posted Wednesday, October 10, 2012 4:01 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 12, 2014 9:30 AM
Points: 513, Visits: 1,129
maida_rh (10/10/2012)
I tried ur solution but stilll code return the same result as my query return ....

Please send some sample data and result obtained and the result expected...




If you need to work better, try working less...
Post #1370808
Posted Wednesday, October 10, 2012 4:08 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 10:13 AM
Points: 1,076, Visits: 6,458
maida_rh (10/10/2012)
I tried ur solution but stilll code return the same result as my query return ....


	AND NOT (
actiontype in ('withdrawn','Discontinued','Matured','Withdrawn from Public')
AND rhdate < (select getdate()-1095)
)

This will 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.



Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Post #1370814
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse