title: How to select the next value if the first value is NULL
body: <p>I am trying to write those Select subquery so that if the Top 1 value is NULL, it would move to the next value that is not NULL. Here is the code:</p>
  
  <pre><code>----------------- GATHERING DATE DATA FROM ALL 12 MONTH UDF'S INTO TEMP TABLE #tmpAll12MonthForms -----------------
  select e.RECORD_ID,e.FORM_INSTANCE_ID,e.FORM_NAME,e.[FIELD_VALUE] as [DATE_FORM_COMPLETED],
      row_number() over (partition by record_id order by cast([FIELD_VALUE] as date) desc) as RowNum into #tmpAll12MonthForms
      from [View_CMMode_UDFData] e
      where e.FORM_DEF_DR IN 
      ('HMFPAT12mPostEn','HMFPAT24mPostEn','HMFPAT36mPostEn'
      ,'HMFPAT48mPostEn','HMFPAT60mPostEn'
      ,'HMFPAT72mPostEn','HMFPATEnroll','PATEnroll'
      ,'HMFPATEnrollV01','SCMTmechvEnroll','HMFSCEnroll','HMFNFP12mPostEn'
      ,'HMFNFP24mPostEn','HMFNFPEnroll')
      AND e.FIELD_DEF_DR in ('PATMIECHV12mPostEn','PATMIECHV24mPostEn'
      ,'PATMIECHV12mPostEnGuideDtVstFrmComp'
      ,'PATMIECHV24mPostEnGuideDtVstFrmComp'
      ,'PATMIECHV36mPostEnGuideDtVstFrmComp'
      ,'MTmechv48mPostEnGuideDt','MTmechv60mPostEnGuideDt'
      ,'MTmechv72mPostEnGuideDt' ,'PATEnrollGuideDt','EnrollGuideDt'
      ,'FSMTmechv12mPostEn','FSMTmechv12mPostEnGuideDt'
      ,'NFPMIECHV12mUpdtGuideDt','NFPMIECHV18mUpdtGuideDt'
      ,'NFPMIECHV24mUpdtGuideDt','NFPMIECHV6mUpdtGuideDt'
      ,'NFPMIECHVEnrollGuideDt')
      group by e.record_id,e.FORM_INSTANCE_ID,FORM_NAME,[FIELD_VALUE]
  
  create unique clustered index All12MonthFormsU on #tmpAll12MonthForms (record_id,form_instance_id)
  
  ----------------- GATHERING PRENATAL ENROLLIES DATA FROM UDF INTO TEMP TABLE #tmpPrenatal -----------------
  SELECT [RECORD_ID],[FORM_INSTANCE_ID],[field_value] as 'Prenatal status' INTO #tmpPrenatal
      FROM [View_CMMode_UDFData]
      where [SECTION_DEF_DR] in ('MTmechvEnrollInfo','FP') and [FIELD_DEF_DR] in ('MTmechvEnrollInfoChldPNEnroll','FPPG')
      GROUP BY [RECORD_ID],[FORM_INSTANCE_ID],[field_value]
  
  ------------- COLLECTING CLIENT PRENATAL ENROLLMENT DATA FOR PAT --------------------
  SELECT U.RECORD_ID,u.FORM_INSTANCE_ID,u.[Prenatal status],p.RowNum into #TmpPrenatalEnrollPAT
      FROM #tmpPrenatal u inner join #tmpAll12MonthForms p 
      on p.RECORD_ID = u.RECORD_ID and p.FORM_INSTANCE_ID = u.FORM_INSTANCE_ID 
      group by U.RECORD_ID,u.FORM_INSTANCE_ID,u.[Prenatal status],p.RowNum
  
  ----------- THIS IS THE SELECT SUBQUERY I AM WORKING ON. THERE ARE MANY LIKE THIS ONE -------------
  ,(SELECT TOP 1 [Prenatal status]
  FROM #TmpPrenatalEnrollPAT as prenatalEnroll WHERE prenatalEnroll.RECORD_ID = ClientSVC.SVC_ID
  ORDER BY prenatalEnroll.RowNum asc
  ) AS PrenatalEnroll
   ............
   ............
  
   INTO #TempPrimaryCGPAT
   FROM #tmpClientServices AS ClientSVC 
   INNER JOIN #tmpClients AS Client ON Client.PER_ROWID = ClientSVC.SVC_PersonDR
  
   SELECT .....................
        ,ClientSVC.PER_PRIMARYLANGUAGE as Client_PrimaryLanguage
        ,PrenatalEnroll as [Prenatally Enroll]
        ,ClientSVC.EnrollDate
        ,.............
    into #TMPpatTable
    FROM #TempPrimaryCGPAT AS ClientSVC
    INNER JOIN E_Entity e (nolock) ON e.ID = ClientSVC.PrimaryCGID
    INNER JOIN #tmpClients AS CAREGIVER ON CAREGIVER.PER_ROWID = e.EntityHx_ID
    INNER JOIN #tmpLastHomeVisitPAT V ON V.RECORD_ID = ClientSVC.SVC_ID
  </code></pre>
  
  <p>I would need the code to move on to the next value from the Top 1. RowNum in the Order clause is a row number that was created using a partition in a previous table and as things stand it is equal to 1. This is one of many Select subqueries using this Select Top 1 method. I am using this method to get the most recent information from the query, but if needed, we could fall back on the next most recent data.</p>
  
  <p>Here is some sample output. The entire output is very large with many columns.</p>
  
  <pre><code>  Caregiver_MaritalStatus
            Married
    Not Married but Living Together with Partner
             NULL
    Not Married but Living Together with Partner
    Never Married (not including not married but living with partner)
    Not Married but Living Together with Partner
             NULL
           Married
    Not Married but Living Together with Partner
    Not Married but Living Together with Partner
    Not Married but Living Together with Partner
  </code></pre>