status of a record in a given date range

Asked

Viewed 27 times

0

Greetings! I came across a problem and I’d like some help if that’s possible. I am developing a financial report and in this report I need to get the status of a title in a certain period. For example. Let’s say I have a title to the sale of a property that was held on the month of February, and the same has been paid now in November. I need that if I do the research in the report and put a date before the low period of my title it appears open and if it passes from a date after the low date of this title it comes with the original status. This financial chart is not linked to any history table so I kind of need to search for it in a "manual" way. My last attempt was to create a CASE verifying that if the negotiation date was less than the date of the drop it would appear the value with the DHBAIXA (date of the drop) empty, otherwise it would bring me the current state of the title, but it did not work. In the example below the title had its low on 04-11-2020. In that interval there below it appears null, however, if I change the date to the month 12 it should appear downloaded already, however, it still appears null. Could someone help me? I’m a beginner in comics yet, so I don’t know all the functions. Thanks for the attention

SELECT
    FIN.NUFIN ,
    FIN.CODEMP || ' - ' || EMP.NOMEFANTASIA AS EMPRESA ,
    FIN.CODPARC || ' - ' || PAR.NOMEPARC AS PARCEIRO ,
    FIN.DTNEG ,
    FIN.DTVENC ,
    FIN.DHBAIXA ,
    FIN.VLRDESDOB ,
    FIN.VLRBAIXA ,
    FIN.RECDESP ,
CASE 
    WHEN FIN.DTNEG < FIN.DHBAIXA OR FIN.DHBAIXA IS NULL AND FIN.DTNEG BETWEEN  DATE '2020-01-01' AND  DATE '2020-11-01'  THEN NULL ELSE FIN.DHBAIXA END AS DHBAIXA_TESTE      
FROM
TGFFIN FIN
    JOIN TSIEMP EMP ON  FIN.CODEMP = EMP.CODEMP
    JOIN TGFPAR PAR ON FIN.CODPARC = PAR.CODPARC
WHERE
    FIN.NUFIN = 242381
AND FIN.DTNEG BETWEEN DATE '2020-01-01' AND DATE '2020-11-01'
tsql oracle12c

  • "given period" and whether it makes a search in almost a year ... for me it became unclear. What is dtneg ?

  • dtneg would be the trading date or when the security entered the system. As it was made a system change are checking whether the financial value matches the closing of accounting and then are asking for this report. So let’s say I paid for that title in November, so the date of the discharge would be filled. And if in the parameters inform a date for example from January to October, it has to appear that it is still open. And if it is something parceled should appear the paid installments only referring to the months of the parameters.

  • Tried WHEN FIN.DHBAIXA IS NULL AND FIN.DTNEG BETWEEN DATE '2020-01-01' AND DATE '2020-11-01' THEN NULL ELSE FIN.DHBAIXA END AS DHBAIXA_TESTE FROM

  • Yes, yes. But the problem is that for example if I put the period now in November it is downloaded. But if I filter from January to October it needs to appear open without the DHBAIXA field filled.

  • tip , list the cases and try to assemble the case , maybe you have to make a Function

  • I tried to mount the code case but it didn’t work yet. I’ll see how to mount a function and try to mount one. I’m still new in BD but it’s being very good. I appreciate the tip, friend. I will study on and will comment on the result here.

  • Mota, thank you so much for your tips. I’m managing to evolve using case. As soon as the report gets ready I put here. Can help more people.

Show 2 more comments

1 answer

0

Good morning, people. I managed to solve the problem. I am posting here the solution if someone has the same question in the future. I thank you all for your help.

    SELECT
    FIN.NUFIN ,
    FIN.NUNOTA ,
    --FIN.NUMNOTA ,
/* VERIFICAR SE O NUMERO DA NOTA É DO SISTEMA ANTERIOR OU DO SISTEMA ATUAL*/
    CASE WHEN FIN.NUMNOTA = 0 THEN FIN.AD_NUM_PROTHEUS ELSE TO_CHAR(FIN.NUMNOTA,'999999999999999999999') END AS NUMNOTA_TESTE,
/*FINAL VERIFICAÇÃO */
'Valor Referente a NF - '||/*INICIOPRIMEIROCASE*/CASE WHEN FIN.NUMNOTA = 0 THEN FIN.AD_NUM_PROTHEUS ELSE TO_CHAR(FIN.NUMNOTA,'999999999999999999999') END /*FIMPRIMEIROCASE*/ || ' - '||CASE WHEN 
    (FIN.DTENTSAI BETWEEN :P_PERIODO.INI AND :P_PERIODO.FIN) AND (FIN.DHBAIXA BETWEEN :P_PERIODO.INI AND  :P_PERIODO.FIN)
    THEN FIN.DHBAIXA END AS HISTORICO,
    FIN.DESDOBRAMENTO ,
    FIN.CODEMP || ' - ' || EMP.NOMEFANTASIA AS EMPRESA ,
    FIN.CODPARC || ' - ' || PAR.NOMEPARC AS PARCEIRO ,
    FIN.DTNEG ,
    FIN.DTENTSAI ,
    FIN.DTVENC ,
    FIN.VLRDESDOB , 
    NAT.DESCRNAT,
CASE WHEN  FIN.RECDESP = 1 THEN 'Receita' Else 'Despesa' END AS RECDESP_CASE  ,
CASE WHEN (FIN.DTENTSAI BETWEEN :P_PERIODO.INI AND :P_PERIODO.FIN) AND (FIN.DHBAIXA BETWEEN :P_PERIODO.INI AND :P_PERIODO.FIN)THEN FIN.DHBAIXA END AS DHBAIXA_TESTE  ,
CASE WHEN (FIN.DTENTSAI BETWEEN :P_PERIODO.INI AND :P_PERIODO.FIN) AND (FIN.DHBAIXA BETWEEN :P_PERIODO.INI AND :P_PERIODO.FIN )THEN FIN.VLRBAIXA END AS VLRBAIXA_TESTE,
CASE WHEN (FIN.DTENTSAI BETWEEN :P_PERIODO.INI AND :P_PERIODO.FIN) THEN FIN.NURENEG END AS NURENEG_TESTE,
CASE WHEN (FIN.DTENTSAI BETWEEN :P_PERIODO.INI AND :P_PERIODO.FIN) AND (REN.DHALTER BETWEEN :P_PERIODO.INI AND :P_PERIODO.FIN )THEN REN.DHALTER END AS DTRENEG_TESTE,
CASE WHEN (FIN.DTENTSAI BETWEEN :P_PERIODO.INI AND :P_PERIODO.FIN) THEN FIN.NUCOMPENS END AS NUCOMPENS_TESTE,
CASE WHEN (FIN.DTENTSAI BETWEEN :P_PERIODO.INI AND :P_PERIODO.FIN) AND (MBC.DHCONCILIACAO BETWEEN :P_PERIODO.INI AND :P_PERIODO.FIN )THEN MBC.DHCONCILIACAO END AS DHCONSC_TESTE,
TOP.CODTIPOPER || ' - '|| TOP.DESCROPER  AS MOVIMENTO,
-- INICIO SALDO 
CASE WHEN (FIN.DTENTSAI BETWEEN :P_PERIODO.INI AND :P_PERIODO.FIN) AND (FIN.DHBAIXA BETWEEN :P_PERIODO.INI AND :P_PERIODO.FIN ) THEN TRUNC((SELECT 
SUM(VLRDESDOB ) 
FROM TGFFIN FIN
JOIN TGFTOP TOP ON FIN.CODTIPOPER = TOP.CODTIPOPER AND FIN.DHTIPOPER = TOP.DHALTER 
JOIN TGFNAT NAT ON FIN.CODNAT = NAT.CODNAT 
WHERE CODEMP IN :P_EMPRESA
AND  ((:P_RECDESP = 'R' AND FIN.RECDESP = 1 ) OR (:P_RECDESP = 'D' AND FIN.RECDESP = -1 ))  
AND (FIN.CODPARC = :P_PARCEIRO OR :P_PARCEIRO IS NULL )
AND FIN.DTENTSAI BETWEEN :P_PERIODO.INI AND :P_PERIODO.FIN
AND FIN.CODNAT IN :P_NATUREZA 
AND TOP.TIPMOV  NOT IN ('P','O')) - FIN.VLRBAIXA,2) END AS TOTALDESDOB,
-- FIM SALDO
-- INICIO NOVAS ALTERAÇÕES
CASE WHEN (FIN.DTENTSAI BETWEEN :P_PERIODO.INI AND :P_PERIODO.FIN) AND (FIN.DHBAIXA BETWEEN :P_PERIODO.INI AND :P_PERIODO.FIN ) THEN TRUNC((SELECT 
SUM(VLRDESDOB ) 
FROM TGFFIN FIN
JOIN TGFTOP TOP ON FIN.CODTIPOPER = TOP.CODTIPOPER AND FIN.DHTIPOPER = TOP.DHALTER
JOIN TGFNAT NAT ON FIN.CODNAT = NAT.CODNAT 
WHERE CODEMP IN :P_EMPRESA
AND ((:P_RECDESP = 'R' AND FIN.RECDESP = 1 ) OR (:P_RECDESP = 'D' AND FIN.RECDESP = -1 ))
AND (FIN.CODPARC = :P_PARCEIRO OR :P_PARCEIRO IS NULL )
AND FIN.DTENTSAI BETWEEN :P_PERIODO.INI AND :P_PERIODO.FIN
AND FIN.CODNAT IN :P_NATUREZA
AND TOP.TIPMOV  NOT IN ('P','O')
AND FIN.DHBAIXA IS NOT  NULL) - (FIN.VLRDESDOB),2) END AS DIF 
--FIM NOVAS ALTERAÇÕES 
FROM
TGFFIN FIN
    JOIN TSIEMP EMP ON FIN.CODEMP = EMP.CODEMP
    JOIN TGFPAR PAR ON FIN.CODPARC = PAR.CODPARC
    JOIN TGFTOP TOP ON FIN.CODTIPOPER = TOP.CODTIPOPER AND FIN.DHTIPOPER = TOP.DHALTER 
    JOIN TGFNAT NAT ON FIN.CODNAT = NAT.CODNAT 
LEFT JOIN TGFREN REN ON FIN.NUFIN = REN.NUFIN 
LEFT JOIN TGFMBC MBC ON FIN.NUBCO = MBC.NUBCO 
WHERE   
FIN.CODEMP IN :P_EMPRESA 
AND (FIN.CODPARC = :P_PARCEIRO OR :P_PARCEIRO IS NULL )
AND ((:P_RECDESP = 'R' AND FIN.RECDESP = 1 ) OR (:P_RECDESP = 'D' AND FIN.RECDESP = -1 ))
AND (FIN.CODTIPOPER = :P_TOP OR :P_TOP IS NULL )
AND (FIN.VLRDESDOB = :P_VALOR OR :P_VALOR IS NULL )
AND FIN.DTENTSAI BETWEEN :P_PERIODO.INI AND :P_PERIODO.FIN
AND TOP.TIPMOV NOT IN ('P','O')
ORDER BY FIN.CODPARC, FIN.NUMNOTA, FIN.DTENTSAI
´´´

Browser other questions tagged tsql oracle12c

You are not signed in. Login or sign up in order to post.