October 9, 2012 at 10:52 pm
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
October 10, 2012 at 1:09 am
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
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 10, 2012 at 3:40 am
I tried ur solution but stilll code return the same result as my query return ....
October 10, 2012 at 4:01 am
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...
October 10, 2012 at 4:08 am
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.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply