Create Row where no value is given Microsoft SQL Server

Question

I am probably just thinking about this wrong but I am trying not to redo work here.

I have a query that finds all the credit ratings of customers and creates an average with some other data.

DECLARE @StartDate int = CONVERT(VARCHAR(8), GETDATE()-90, 112)
DECLARE @EndDate int = CONVERT(VARCHAR(8), GETDATE(), 112)

SELECT
    [Credit Rating],
    LoanTypeKey,
    LoanTypeDescription,
    MIN(InterestRate) AS Minimum_Rate,
    MIN(NULLIF(InterestRate, 0)) AS Minimum_Rate_Non_Zero,
    MAX(interestrate) AS Maximum_Rate,
    CONCAT(MIN(InterestRate), ' - ', MAX(interestrate)) AS [Given_Rates],
    CONCAT(MIN(NULLIF(InterestRate, 0)),' - ', MAX(interestrate)) AS [Given_Rates_Non_Zero],
    AVG(InterestRate) AS '90Day Avg Interest Rate'
FROM
    (SELECT DISTINCT        
         LoanTypeKey,
         ls.AccountNumber, ls.LoanID,
         lc.LoanTypeDescription, InterestRate,
         CASE 
             WHEN ld.LoanCreditScore >= 740 THEN 'A+'
             WHEN ld.LoanCreditScore >= 740 THEN 'A+'
             WHEN ld.LoanCreditScore >= 700 THEN 'A'
             WHEN ld.LoanCreditScore >= 670 THEN 'B'
             WHEN ld.LoanCreditScore >= 640 THEN 'C'
             WHEN ld.LoanCreditScore >= 600 THEN 'D'
             WHEN ld.LoanCreditScore >= 1 THEN 'E'
             ELSE 'No Credit Score'
         END AS 'Credit Rating'
     FROM 
         cu.LAFactLoanSnapShot ls       
     INNER JOIN 
         arcu.ARCULoanCategory lc ON ls.loantypekey = lc.LoanType
     INNER JOIN 
         [ARCUSYM000].[arcu].[ARCULoanDetailed] ld ON ls.AccountNumber = ld.AccountNumber
                                                   AND ld.ProcessDate = ls.ProcessDate
     WHERE      
         -- LoanStatus = 'Open' 
         OpenDateKey BETWEEN @StartDate AND @EndDate
         AND InterestRate != 0) x
GROUP BY
    LoanTypeKey, x.[Credit Rating], LoanTypeDescription
ORDER BY
    LoanTypeKey, [Credit Rating] 

Now my issue here is that if there are no New Cars being sold to someone with a E credit rating in the last 90 days then there is no row for E rating with new cars. I want to be able to add that row with other values if any of the credit ratings A+, A, B, C, D, E, 'No Credit Score') are not represented by a customer in the last 90 days.

Credit Rating	LoanTypeKey	Loan Type Description	90Day Average Interest rate	MinRate
A	500	New Car	4.99	1.0
B	500	New Car	4.5	1.0
C	500	New Car	4.6	1.0
D	500	New Car	4.3	1.0
E	500	New Car	4.5	1.0
No Credit Score	501	New Car	2.1	1.0
A	501	Used Car	2.1	1.0
B	501	Used Car	2.1	1.0
C	501	Used Car	2.4	1.0
D	501	Used Car	2.5	1.0
No Credit Score	501	Used Car	4.1	1.0

This is an example of my current table with a few columns taken out. You can see that Used Car does not have an E Credit Rating. I just want E to be included with all other columns blank.

For example:

Credit Rating	LoanTypeKey	Loan Type Description	90Day Average Interest rate	MinRate
E	501	Used Car

Keep in mind I have 20+ other Loan types

Answer 1

First, create a dummy table of [Credit Rating] letters by using WITH

    With letters as 
    (SELECT *
      FROM (VALUES ('A')
                 , ('B')
                 , ('C')
                 , ('D')
                 , ('E')
           ) t1 (letter))
Select letter from letters...

Then you can use CROSS JOIN to get missing values. This is the link to the answer that I found which also helped me to solve similar query couple months back.

T-SQL Cross Join to get missing values.

LEFT JOIN won't solve the issue