SQL - Get fall back values if no data is available

Question

I got a table TBL_A like:

   CLASS_ID  PERIOD_DT   MAX_DT
  -----------------------------------
   358614  2018-09-30  2018-09-30
   358614  2017-09-30  2018-09-30
   358614  2016-09-30  2018-09-30
   358614  2015-09-30  2018-09-30
   358614  2014-09-30  2018-09-30
   358614  2013-09-30  2018-09-30

and TBL_B LIKE:

    CLASS_ID  CLASS_DT   
   ----------------------
    358614  2018-09-30  
    358614  2017-09-30  
    358614  2016-09-30 

I'm trying to get the result set with CLASS_DT for the past six years and some classes doesn't have enough information. If dates are not available, I need to use previous available data that is close to the current period date. so my final result should look like

Expected Result:

 PERIOD_DT   FALLBACK_CLASS_DT
-------------------------------
 2018-09-30  2018-09-30 
 2017-09-30  2017-09-30
 2016-09-30  2016-09-30  
 2015-09-30  2016-09-30 // fallback dates, since they are not available
 2014-09-30  2016-09-30
 2013-09-30  2016-09-30          

And the query that I'm trying is:

 SELECT A.PERIOD_DT,
   (SELECT TOP 1 CAST(B.CLASS_DT AS DATE)
    FROM TBL_B AS B
    WHERE B.CLASS_ID = A.CLASS_ID
      AND CAST(B.CLASS_DT AS DATE) <= CAST(A.PERIOD_DT AS DATE) 
      AND CAST(A.PERIOD_DT AS DATE) <= CAST(A.MAX_DT AS DATE)
    ORDER BY B.CLASS_DT DESC) AS FALLBACK_CLASS_DT
FROM TBL_A AS A
WHERE A.CLASS_ID = 358614  
ORDER BY A.PERIOD_DT DESC;

The result that I'm getting is:

PERIOD_DT   FALLBACK_CLASS_DT
-------------------------------
 2018-09-30  2018-09-30 
 2017-09-30  2017-09-30
 2016-09-30  2016-09-30  
 2015-09-30  NULL
 2014-09-30  NULL
 2013-09-30  NULL 

Can anyone let me know how can I get this result?

Answer 1

Here is the query that worked for me

  SELECT 
    A.CLASS_ID,
    A.PERIOD_DT,
    ISNULL((SELECT TOP 1 CAST(B.CLASS_DT AS DATE)
    FROM TBL_B AS B
    WHERE B.CLASS_ID = A.CLASS_ID
          AND CAST(B.CLASS_DT AS DATE) <= CAST(A.PERIOD_DT AS DATE) 
          AND CAST(A.PERIOD_DT AS DATE) <= CAST(A.MAX_DT AS DATE)
    ORDER BY B.CLASS_DT DESC
    ), B2.CLASS_DT) AS FALLBACK_CLASS_DT
FROM TBL_A AS A
LEFT JOIN (SELECT B.CLASS_ID, MIN(B.CLASS_DT) AS CLASS_DT
           FROM TBL_B AS B
           GROUP BY B.CLASS_ID) AS B2 ON B2.CLASS_ID = A.CLASS_ID
WHERE A.CLASS_ID = 358614  
ORDER BY A.PERIOD_DT DESC;