Select the value of a field depending on a date

Question

Select the value of a field depending on a date

Navigation

#1 by (0 votes)

0

I have a table with the fields DT_FIMCALCULO, VL_DEPREREVVIDAUTILACUMULADA and CD_SEQUENCIALBEM. And for each CD_SEQUENCIALBEM I have several DT_FIMCALCULO each with a different VL_DEPREREVVIDAUTILACUMULADA.

I want to select the VL_DEPREREVVIDAUTILACUMULADA where DT_FIMCALCULO is equal to 09/30/2016. But not all the Goods have a calculation on 30/09, some have a calculation only until 05/31/2016 for example, and in this case I want the VL_DEPREREVVIDAUTILACUMULADA of this last date.

SQL looks 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 biggest date that is smaller than this ... Type 30/08 or 31/05 for example.

However since the VL_DEPREREVVIDAUTILACUMULADA field has different values, SQL continues to result in ALL dates less than 30/09, but I do not want this. I only want 1 result for each CD_SEQUENCIALBEM.
This result has to be the Date 30/09 or smaller than it, and the value referring to this date.

mysql sql postgresql pl-sql

asked by anonymous 19.12.2017 / 13:19

1 answer

Get insert ID after pg_query () I need a script (JQUERY) to go to another page and save the data. [closed]

score 0 · Accepted Answer

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.