Select the value of a field depending on a date

Question

I have a table with the fields DT_FIMCALCULO, VL_DEPREREVVIDAUTILACUMULADA e CD_SEQUENCIALBEM.

And for each CD_SEQUENCIALBEM I have several DT_FIMCALCULO each with a VL_DEPREREVVIDAUTILACUMULADA different.

I want to select the VL_DEPREREVVIDAUTILACUMULADA where the DT_FIMCALCULO is equal to 30/09/2016.

But not all Goods have calculation in 30/09, some have calculation only until 31/05/2016 for example, and in this case I want the VL_DEPREREVVIDAUTILACUMULADA of this last date.

My consultation SQL was like this:

SELECT 
    MAX(DT_FIMCALCULO) AS DT_FIMCALCULO, CD_SEQUENCIALBEM, 
    VL_DEPREREVVIDAUTILACUMULADA
FROM
    TB_DEPRECIACAOSOCIETARIACAL
WHERE
    DT_FIMCALCULO <= '2016-09-30'
AND
    CD_SEQUENCIALBEM = 20
GROUP BY
    CD_SEQUENCIALBEM, VL_DEPREREVVIDAUTILACUMULADA
ORDER
    BY DT_FIMCALCULO

When selecting the field MAX(DT_FIMCALCULO) I want you to bring me the date 30/09 or else the longest date that is less than this... Type 30/08 or 31/05 for example.

But like the countryside VL_DEPREREVVIDAUTILACUMULADA has different values, the SQL continues to result in all dates less than 30/09, but I don’t want this.

I want only 1 result for each CD_SEQUENCIALBEM.
This result has to be the Date 30/09 or less than it, and the value for this date.

Answer 1

SELECT *
FROM TB_DEPRECIACAOSOCIETARIACAL A1
WHERE CD_SEQUENCIALBEM = 20
AND DT_FIMCALCULO = (SELECT MAX(DT_FIMCALCULO) AS DT_FIMCALCULO, 
                     FROM TB_DEPRECIACAOSOCIETARIACAL A2
                     WHERE A2.DT_FIMCALCULO <= '2016-09-30'
                     AND A2.CD_SEQUENCIALBEM = 20)
GROUP BY CD_SEQUENCIALBEM, VL_DEPREREVVIDAUTILACUMULADA
ORDER BY DT_FIMCALCULO

A subselect with MAX solves the problem.