oracle - 查询最接近查询的上一个日期
问题描述
表记录:
process date total
------------ -----
31/12/2018 433
30/09/2018 433
09/07/2018 1873
查询日期时:09/07/2018
结果是:
process date total
------------ -----
09/07/2018 1873
查询日期时:30/09/2018
结果是:
process date total
------------ -----
30/09/2018 433
查询日期时:10/08/2018
结果是:
process date total
------------ -----
09/07/2018 1873
它应该带来的是:
process date total
------------ -----
30/09/2018 433
正确的结果应该是09/07/2018
全面咨询:链接。
SELECT 'POR SECTOR ECONOMICO' AS VCH_RUBRO,
UPPER(CHR_SECECO) AS VCH_CODGRUPO,
UPPER(VC_SECECO) AS VCH_DESGRUPO,
SUM(INT_NROPRESTAMO) AS INT_NROPRESTAMO,
SUM(INTO_BENEFICIARIOS) AS INTO_BENEFICIARIOS,
SUM(DEC_SALCON) AS DEC_SALCON
FROM (
SELECT
CHR_SECECO,
VC_SECECO,
COUNT(*) AS INT_NROPRESTAMO,
0 AS INTO_BENEFICIARIOS,
SUM(DEC_SALCON) AS DEC_SALCON
FROM (
SELECT
INT_IDPRESTAMODET,
DTE_FECPRO,
CHR_SECECO,
VC_SECECO,
DEC_SALCON,
RANK() OVER ( ORDER BY DIFERENCIA ) AS ROWNUMBER
FROM (
SELECT
PREDET.INT_IDPRESTAMODET,
PREDET.DTE_FECPRO,
(PREDET.DEC_IMPSAL) AS DEC_SALCON,
ABS(PREDET.DTE_FECPRO - TO_DATE('30/09/2018', 'DD/MM/YY')) AS DIFERENCIA,
AGRUP.VC_CODDET AS CHR_SECECO,
AGRUP.VC_NOMDET AS VC_SECECO
FROM TB_JSI_PRESTAMO_DETALLE PREDET
INNER JOIN TB_JSI_PRESTAMO PRE ON PRE.INT_IDPRESTAMO=PREDET.INT_IDPRESTAMO
INNER JOIN TB_JSI_BENEFICIARIO_IFI BENIFI ON BENIFI.INT_IDBENEIFI=PRE.INT_IDBENEIFI
INNER JOIN TB_JSI_OPERACION OPE ON PRE.INT_IDOPE = OPE.INT_IDOPE AND OPE.DT_FECVEN>TO_DATE('30/09/2018', 'DD/MM/YY')
INNER JOIN TB_JSI_CIIU CIIU ON PRE.INT_IDACT = CIIU.INT_IDACT AND PRE.INT_IDSEC=CIIU.INT_IDCLAS --26
INNER JOIN TB_JSI_TABLA_DET SECECO ON SECECO.INT_IDDET=CIIU.INT_IDCLAS
INNER JOIN TB_JSI_AGRUPA_SECTOR AGRSEC ON SECECO.INT_IDDET = AGRSEC.INT_IDSEC
INNER JOIN TB_JSI_TABLA_DET AGRUP ON AGRSEC.INT_IDAGRU = AGRUP.INT_IDDET
INNER JOIN TB_JSI_TABLA_DET MON ON OPE.INT_IDMON = MON.INT_IDDET
WHERE (NULL IS NULL OR OPE.INT_IDTIPPRO = 2)
AND (NULL IS NULL OR OPE.INT_IDMON = 364)
AND (NULL IS NULL OR OPE.INT_IDIFI=72)
AND PREDET.DEC_SALDOL>0
)
)
WHERE ROWNUMBER = 1 GROUP BY CHR_SECECO,VC_SECECO
UNION ALL
SELECT
CHR_SECECO,
VC_SECECO,
0 AS INT_NROPRESTAMO,
COUNT(*) AS INTO_BENEFICIARIOS,
0 AS DEC_SALCON
FROM (
SELECT
DISTINCT INT_IDBENEIFI,
DTE_FECPRO,
CHR_SECECO,
VC_SECECO,
RANK() OVER ( ORDER BY DIFERENCIA ) AS ROWNUMBER
FROM (
SELECT
ABS(PREDET.DTE_FECPRO - TO_DATE('30/09/2018', 'DD/MM/YY')) AS DIFERENCIA,
PRE.INT_IDBENEIFI,
PREDET.DTE_FECPRO,
AGRUP.VC_CODDET AS CHR_SECECO,
AGRUP.VC_NOMDET AS VC_SECECO
FROM TB_JSI_PRESTAMO_DETALLE PREDET
INNER JOIN TB_JSI_PRESTAMO PRE ON PRE.INT_IDPRESTAMO=PREDET.INT_IDPRESTAMO
INNER JOIN TB_JSI_BENEFICIARIO_IFI BENIFI ON BENIFI.INT_IDBENEIFI=PRE.INT_IDBENEIFI
INNER JOIN TB_JSI_CIIU CIIU ON PRE.INT_IDACT = CIIU.INT_IDACT AND PRE.INT_IDSEC=CIIU.INT_IDCLAS
INNER JOIN TB_JSI_TABLA_DET SECECO ON CIIU.INT_IDCLAS = SECECO.INT_IDDET
INNER JOIN TB_JSI_AGRUPA_SECTOR AGRSEC ON SECECO.INT_IDDET = AGRSEC.INT_IDSEC
INNER JOIN TB_JSI_TABLA_DET AGRUP ON AGRSEC.INT_IDAGRU = AGRUP.INT_IDDET
INNER JOIN TB_JSI_OPERACION OPE ON PRE.INT_IDOPE = OPE.INT_IDOPE AND OPE.DT_FECVEN>TRUNC(TO_DATE('30/09/2018', 'DD/MM/YY'))
INNER JOIN TB_JSI_TABLA_DET MON ON OPE.INT_IDMON = MON.INT_IDDET
INNER JOIN TB_JSI_IFI IFI ON OPE.INT_IDIFI = IFI.INT_IDIFI
WHERE (NULL IS NULL OR OPE.INT_IDTIPPRO = 2)
AND (NULL IS NULL OR OPE.INT_IDMON = 364)
AND (NULL IS NULL OR OPE.INT_IDIFI=72)
AND PREDET.DEC_SALDOL>0
)
)
WHERE ROWNUMBER = 1 GROUP BY CHR_SECECO,VC_SECECO
) PREST
GROUP BY CHR_SECECO, VC_SECECO
ORDER BY DEC_SALCON DESC, VC_SECECO;
解决方案
如果您删除ABS
未来的日期将是积极的;所以我认为您可以将它们过滤掉:
...
RANK() OVER ( ORDER BY DIFERENCIA ) AS ROWNUMBER
FROM (
SELECT
-- remove the ABS() call
-- ABS(PREDET.DTE_FECPRO - TO_DATE('30/09/2018', 'DD/MM/YY')) AS DIFERENCIA,
PREDET.DTE_FECPRO - TO_DATE('30/09/2018', 'DD/MM/YY') AS DIFERENCIA,
PRE.INT_IDBENEIFI,
PREDET.DTE_FECPRO,
AGRUP.VC_CODDET AS CHR_SECECO,
AGRUP.VC_NOMDET AS VC_SECECO
FROM TB_JSI_PRESTAMO_DETALLE PREDET
INNER JOIN TB_JSI_PRESTAMO PRE ON PRE.INT_IDPRESTAMO=PREDET.INT_IDPRESTAMO
INNER JOIN TB_JSI_BENEFICIARIO_IFI BENIFI ON BENIFI.INT_IDBENEIFI=PRE.INT_IDBENEIFI
INNER JOIN TB_JSI_CIIU CIIU ON PRE.INT_IDACT = CIIU.INT_IDACT AND PRE.INT_IDSEC=CIIU.INT_IDCLAS
INNER JOIN TB_JSI_TABLA_DET SECECO ON CIIU.INT_IDCLAS = SECECO.INT_IDDET
INNER JOIN TB_JSI_AGRUPA_SECTOR AGRSEC ON SECECO.INT_IDDET = AGRSEC.INT_IDSEC
INNER JOIN TB_JSI_TABLA_DET AGRUP ON AGRSEC.INT_IDAGRU = AGRUP.INT_IDDET
INNER JOIN TB_JSI_OPERACION OPE ON PRE.INT_IDOPE = OPE.INT_IDOPE AND OPE.DT_FECVEN>TRUNC(TO_DATE('30/09/2018', 'DD/MM/YY'))
INNER JOIN TB_JSI_TABLA_DET MON ON OPE.INT_IDMON = MON.INT_IDDET
INNER JOIN TB_JSI_IFI IFI ON OPE.INT_IDIFI = IFI.INT_IDIFI
WHERE (NULL IS NULL OR OPE.INT_IDTIPPRO = 2)
AND (NULL IS NULL OR OPE.INT_IDMON = 364)
AND (NULL IS NULL OR OPE.INT_IDIFI=72)
AND PREDET.DEC_SALDOL>0
)
-- add a filter
WHERE DIFERENCIA <= 0
)
WHERE ROWNUMBER = 1 GROUP BY CHR_SECECO,VC_SECECO
...
我保留了您的原始格式;如果您正确缩进,将更容易看到发生了什么。我已经包含了对这两个更改的评论。显然我没有你的表格或数据未经测试......
推荐阅读
- bash - 如何从 mpirun 在 gnome-terminal 中运行 gdb?
- azure - Azure 消费 API:Azure 使用详情列表中使用的计量费率
- java - 我的应用程序注销时出现问题。应用程序不断崩溃
- gitlab - 如何在 gitlab 中将我的网站托管为 myname.gitlab.io
- sql - 选择聚合中的第一行和最后一行,并在 SQL 中创建 2 列
- c# - 如何计算列表中的整数总数?
- text-to-speech - 我如何在其他背景音乐应用程序 Android 上增加文本到语音的音量
- php - 如何在二维码中添加个人信息链接
- css - 使用 for 循环或每个循环时,字符串从 SCSS 中的变量编译后删除
- lua - 试图理解“Lua 编程”一书中的例子