Search code examples
sqlsql-serversubquery

how many banks are currently rated B+ or above and when was the last time (dateindex) that they had been below

enter image description here

enter image description here

I have these two tables and im trying to get the dateindex of the last time that the company was rated below a B+. dateindex=19941 which means 1994 quarter 1

This selects all the companies that have B+ or above in q2 2020

SELECT DISTINCT mr.name, mc.rating, mr.DateIndex 
FROM [Model].[rating]mc inner join [Model].[RawHist]mr 
ON mc.BankId=mr.BankId
WHERE mc.Rating in ('A+','A','A-','B+') AND mr.DateIndex in('20202')

And it yields the following

enter image description here

How can I add the dateindex the last time it was below B+. so it would have those three fields and two more fields one with the last grade below b+ and its date index for 5 total fields.

This is what i have so far with the results enter image description here enter image description here

Its giving me way to many rows.

Solution

  • I have these two tables and im trying to get the dateindex of the last time that the company was rated below a B+.

    This sounds like aggregation:

    SELECT mr.name, MAX(mr.DateIndex)
FROM [Model].[rating] mc JOIN
     [Model].[RawHist]mr 
     ON mc.BankId = mr.BankId
WHERE mc.Rating NOT IN ('A+', 'A', 'A-','B+') 
GROUP BY mr.name;

    This assumes that "less than B+" means that it is not one of the listed ratings.