I am working with the following query.I want to use Date\_Sent\_to\_Recorder in my other calculations which follows.I am getting an error "Invalid column name 'Date\_Sent\_to\_Recorder'." People recommended using a CTE for this but i am not able to fit it in this scenerio.I have a correlated subquery which calculates Date\_Sent\_to\_Recorder.Please help.

```
 SELECT    
 C.Name Client ,
 SecONdary_Document.Primary_Document_ID ,
 SecONdary_Document.Secondary_Document_Id ,
 Primary_Document.State + ',' + GB_Counties.CountyName State ,
 Grantor.Name Grantor ,
 Loan_Number ,
 CONVERT(VARCHAR(10), Primary_Document.PIF_Date, 101) PIF_Date ,

  ( SELECT    CASE WHEN dbo.SECONDARY_DOCUMENT.Status_ID = 21
                                     THEN CONVERT(VARCHAR(10), dbo.SECONDARY_DOCUMENT.Updated_Dt, 101)
                                     ELSE ( SELECT TOP 1
                                                    CONVERT(VARCHAR(10), dbo.SECONDARY_DOCUMENT_STATUS_HISTORY.Created_Dt, 101)
                                            FROM    dbo.SECONDARY_DOCUMENT_STATUS_HISTORY
                                            WHERE   dbo.SECONDARY_DOCUMENT_STATUS_HISTORY.Secondary_Document_ID = SecONdary_Document.Secondary_Document_Id
                                                    AND SECONDARY_DOCUMENT_STATUS_HISTORY.Status_ID = 21
                                            ORDER BY dbo.SECONDARY_DOCUMENT_STATUS_HISTORY.Created_Dt DESC
                                          )
                                END
                    ) AS Date_Sent_to_Recorder ,

                    Satis_TimeFrame ,
                    CASE WHEN PIF_Date IS NULL
                              OR Date_Sent_to_Recorder IS NULL THEN NULL
                         ELSE DATEDIFF(DAY, PIF_Date,
                                       Date_Sent_to_Recorder)
                    END TotalDays ,
                    CASE WHEN PIF_Date IS NULL
                              OR Date_Sent_to_Recorder IS NULL THEN NULL
                         ELSE CASE WHEN DATEDIFF(DAY, PIF_Date,
                                                 ISNULL(Date_Sent_to_Recorder,
                                                        GETDATE())) > Satis_TimeFrame
                                   THEN 'N'
                                   ELSE 'Y'
                              END
                    END InCompliance ,
                    Loan_Name ,
                    Deal_Name ,
                    Deal.Deal_Id ,
                    Loan.Loan_Id
          FROM      Primary_Document
                    INNER JOIN SecONdary_Document ON SecONdary_Document.Primary_Document_ID = Primary_Document.Primary_Document_ID
                    INNER JOIN Status ON Status.Status_Id = SecONdary_Document.Status_Id
                    INNER JOIN GB_Counties ON GB_Counties.CountyId = Primary_Document.County_Id
                    INNER JOIN Loan ON Loan.Loan_Id = Primary_Document.Loan_Id

```

EDIT------------

```
DECLARE @Date_Sent_to_Recorder  varchar(10)
SELECT  C.Name Client ,
    SecONdary_Document.Primary_Document_ID ,
    SecONdary_Document.Secondary_Document_Id ,
    Primary_Document.State + ',' + GB_Counties.CountyName State ,
    Grantor.Name Grantor ,
    Loan_Number ,
    CONVERT(VARCHAR(10), Primary_Document.PIF_Date, 101) PIF_Date ,
                                                     --<START>
                                                    --3021,RRaghuvansi,If current status is 21 in SECONDARY_DOCUMENT then take Updated_Dt else take Created_Dt in SECONDARY_DOCUMENT_STATUS_HISTORY with status Out For Recorder                                                         
                                                    --CONVERT(VARCHAR(20), Recording_Date, 101) AS Recording_Date ,

    @Date_Sent_to_Recorder=[dbo].[GET_RecordingDate](SecONdary_Document.Secondary_Document_Id),                                             --<END> 
    Satis_TimeFrame ,

    Loan_Name ,
    Deal_Name ,
    Deal.Deal_Id ,
    Loan.Loan_Id
 FROM Primary_Document

```