Need help to eliminate values from resultant record using view

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Need help to eliminate values from resultant...

Need help to eliminate values from resultant record using view

Rate Topic

Display Mode

Topic Options

Author

Message

maida_rh

Posted Tuesday, October 09, 2012 10:52 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, February 11, 2013 12:10 AM
Points: 42, Visits: 95

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

ChrisM@home

Posted Wednesday, October 10, 2012 1:09 AM

SSC Eights!

Group: General Forum Members
Last Login: Today @ 2:51 PM
Points: 921, Visits: 3,736

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

maida_rh

Posted Wednesday, October 10, 2012 3:40 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, February 11, 2013 12:10 AM
Points: 42, Visits: 95

I tried ur solution but stilll code return the same result as my query return ....

Post #1370797

PiMané

Posted Wednesday, October 10, 2012 4:01 AM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 5:02 AM
Points: 403, Visits: 904

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

ChrisM@home

Posted Wednesday, October 10, 2012 4:08 AM

SSC Eights!

Group: General Forum Members
Last Login: Today @ 2:51 PM
Points: 921, Visits: 3,736

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 »

Permissions