Buenos dias, quiero compartir una query que realice en hana para ayudar en el tema de saldos de antiguedad de clientes, me funciona correctamente pero se que se puede optimizar un poco mas, adjunto query:
(SELECT TO_DATE(T1.“TaxDate”) AS “FECHA”,TO_DATE(T1.“DocDueDate”) AS “VENCIMIENTO”,
CONCAT(‘FAC-000’,IFNULL(T1.“FolioNum”,T1.“DocNum”)) AS “N°DOCUMENTO” ,T1.“CardCode” AS “CODIGO” ,
T1.“CardName” AS “PERSONA”, (T1.“DocTotal” -IFNULL(“RC”.“V_RECON”,0) )as “TOTAL”,:CUENTA AS “CUENTA”,
(SELECT SN.“SlpCode” FROM OCRD SN WHERE SN.“CardCode” = T1.“CardCode”) AS “VENDEDOR”, T1.“Comments” AS “COMENTARIO”, T1.“DocTotal” AS “TI”
FROM JDT1 T0 INNER JOIN OINV T1 ON T0.“BaseRef” = T1.“DocNum”
LEFT JOIN (
SELECT I.“SrcObjAbs” AS “R_IDFAC”, SUM(I.“ReconSum”) AS “V_RECON”
FROM OITR O INNER JOIN ITR1 I ON O.“ReconNum” = I.“ReconNum”
WHERE I.“Account” =‘_SYS00000002911’
AND O.“ReconDate” <= :FECHA_CORTE AND O.“Canceled” = ‘N’ AND I.“SrcObjTyp” = 13
GROUP BY I.“SrcObjAbs”
ORDER BY I.“SrcObjAbs” ASC
) AS “RC” ON “RC”.“R_IDFAC” = T1.“DocEntry”
WHERE T0.“RefDate” <= :FECHA_CORTE AND T0.“Account” = ‘_SYS00000002911’ AND T0.“TransType” = 13 AND T1.“CANCELED” = ‘N’
AND (T1.“DocTotal” -IFNULL(“RC”.“V_RECON”,0) ) <> 0
ORDER BY T1.“CardName” ASC)
UNION ALL( ----VALORES A FAVOR DEL CLIENTE
SELECT TO_DATE(T1.“TaxDate”) AS “FECHA”,TO_DATE(T1.“DocDueDate”) AS “VENCIMIENTO”,
CONCAT(‘VF-000’,T1.“DocNum”) AS “N°DOCUMENTO”,T1.“CardCode” AS “CODIGO”,T1.“CardName” AS “PERSONA”,
(IFNULL(T1.“NoDocSum”,0)-1) + IFNULL(“PR”.“V”,0) - IFNULL(“OT”.“SUM”,0)+ IFNULL(“OT1”.“SUM”,0) as “TOTAL”,:CUENTA AS “CUENTA”,
(SELECT SN.“SlpCode” FROM OCRD SN WHERE SN.“CardCode” = T1.“CardCode”) AS “VENDEDOR”, T1.“Comments” AS “COMENTARIO”, T1.“NoDocSum” -1 AS “TI”
FROM JDT1 T0
INNER JOIN ORCT T1 ON T1.“DocNum” = T0.“BaseRef”
–INNER JOIN RCT3 T2 ON T1.“DocEntry” = T2.“DocNum”
LEFT JOIN (
SELECT T1.“SrcObjAbs” AS ID, SUM(T1.“ReconSum”) AS “V” FROM OITR T0
INNER JOIN ITR1 T1 ON T0.“ReconNum” = T1.“ReconNum”
WHERE T0.“ReconType” = 0 AND T0.“ReconDate” <= :FECHA_CORTE
AND T1.“SrcObjTyp” = 24 AND T1.“Account” = ‘_SYS00000002911’ AND T0.“Canceled” = ‘N’
GROUP BY T1.“SrcObjAbs”
) AS “PR” ON “PR”.“ID” = T1.“DocEntry”
----LEFT JOIN QUE REVISA QUE VALOR A FAVOR FUE PAGADO EN UN COBRO.
LEFT JOIN (
SELECT T1.“baseAbs” AS “COD”,T1.“SumApplied” AS “SUM”, T0.“DocDate”, TH.“BpAct”, T0.“CardName”
FROM ORCT T0 INNER JOIN RCT2 T1
ON T0.“DocEntry” = T1.“DocNum”
INNER JOIN ORCT TH ON TH.“DocEntry” = T1.“baseAbs”
WHERE T1.“InvType” = 24 AND T0.“DocDate” <= :FECHA_CORTE AND T0.“Canceled” = ‘N’
AND T0.“BpAct” = ‘_SYS00000002911’ AND TH.“BpAct” = ‘_SYS00000002911’
) AS “OT” ON “OT”.“COD” = T1.“DocEntry”
LEFT JOIN (----LEFT JOIN QUE REVISA QUE VALOR A FAVOR FUE PAGADO EN UN PAGO EFECTUADO.
SELECT T1.“baseAbs” AS “COD”,T1.“SumApplied” AS “SUM”, T0.“DocDate”, T0.“CardName”
FROM OVPM T0 INNER JOIN VPM2 T1
ON T0.“DocEntry” = T1.“DocNum”
WHERE T1.“InvType” = 24 AND T0.“DocDate” <= :FECHA_CORTE AND T0.“Canceled” = ‘N’
AND T0.“BpAct” = ‘_SYS00000002911’ --AND TH.“BpAct” = ‘_SYS00000002911’
) AS “OT1” ON “OT1”.“COD” = T1.“DocEntry”
WHERE T0.“RefDate” <= :FECHA_CORTE AND T0.“Account” = '_SYS00000002911’AND T0.“TransType” = 24
AND T1.“Canceled” = ‘N’ AND T1.“PayNoDoc” = ‘Y’ AND T1.“BpAct” = ‘_SYS00000002911’ --AND T2.“CreditAcct” = ‘_SYS00000004868’
AND (IFNULL(T1.“NoDocSum”,0)-1) + IFNULL(“PR”.“V”,0) - IFNULL(“OT”.“SUM”,0)+ IFNULL(“OT1”.“SUM”,0) <> 0
ORDER BY T1.“CardName” ASC
)UNION ALL(----COBROS ANULADOS
SELECT TO_DATE(T2.“TaxDate”) AS “FECHA”,TO_DATE(T2.“DocDueDate”) AS “VENCIMIENTO”,
CONCAT(‘PR-000’,T0.“BaseRef”) AS “N°DOCUMENTO”,T1.“CardCode” AS “CODIGO”,T1.“CardName” AS “PERSONA”,
SUM(T0.“Debit” - T0.“Credit”) AS “TOTAL”, :CUENTA AS “CUENTA”,
(SELECT SN.“SlpCode” FROM OCRD SN WHERE SN.“CardCode” = T1.“CardCode”) AS “VENDEDOR”,T2.“Comments” AS “COMENTARIO”
, T2.“DocTotal” AS “TI”
FROM JDT1 T0 INNER JOIN OCRD T1
ON T1.“CardCode” = T0.“ShortName”
INNER JOIN ORCT T2 ON T2.“DocNum” = T0.“BaseRef”
WHERE T0.“RefDate” <= :FECHA_CORTE
and T0.“Account” = ‘_SYS00000002911’ AND T0.“TransType” = 24 AND T2.“Canceled” = ‘Y’
GROUP BY T0.“BaseRef”,T1.“CardName”,T2.“DocDueDate”,T2.“TaxDate”,T1.“CardCode”,T2.“Comments”,T2.“DocTotal”
HAVING SUM(T0.“Debit” - T0.“Credit”) <> 0
ORDER BY T1.“CardName” ASC
)UNION ALL(—NOTAS DE CREDITOS PENDIENTES SIN FACTURA RECONCILIADA
SELECT TO_DATE(T1.“TaxDate”) AS “FECHA”,TO_DATE(T1.“DocDueDate”) AS “VENCIMIENTO”,
CONCAT(‘NC-000’,T0.“BaseRef”) AS “N°DOCUMENTO”,T1.“CardCode” AS “CODIGO”,T1.“CardName” AS “PERSONA”,
(T1.“DocTotal”-1) + IFNULL(“RCN”.“SUM”,0) AS “TOTAL”, :CUENTA AS “CUENTA”,
(SELECT SN.“SlpCode” FROM OCRD SN WHERE SN.“CardCode” = T1.“CardCode”) AS “VENDEDOR”
, T1.“Comments” AS “COMENTARIO”, T1.“DocTotal” * -1 AS “TI”
FROM JDT1 T0 INNER JOIN ORIN T1 ON T0.“BaseRef” = T1.“DocNum”
INNER JOIN RIN1 T2 ON T2.“DocEntry” = T1.“DocEntry”
LEFT JOIN ( — RECONCILIACIONES DE NC
SELECT I.“SrcObjAbs” AS “ID_R”,
(CASE WHEN SUM(I.“ReconSum”) < 0 THEN SUM(I.“ReconSum”) -1 ELSE SUM(I.“ReconSum”) END )AS “SUM”
FROM OITR O INNER JOIN ITR1 I ON O.“ReconNum” = I.“ReconNum”
WHERE O.“ReconDate” <= :FECHA_CORTE
AND I.“SrcObjTyp” = 14 AND I.“Account” = ‘_SYS00000002911’ AND O.“Canceled” = ‘N’
GROUP BY I.“SrcObjAbs”
) AS “RCN” ON “RCN”.“ID_R” = T1.“DocEntry”
WHERE T0.“RefDate” <= :FECHA_CORTE
AND T0.“Account” = ‘_SYS00000002911’ AND T0.“TransType” = 14
AND ((T1.“DocTotal”-1) + IFNULL(“RCN”.“SUM”,0)) <> 0
GROUP BY T0.“BaseRef”,T1.“CardName”, T1.“DocTotal”,T1.“TaxDate”,T1.“DocDueDate”,
T1.“CardCode”,T1.“Comments”,“RCN”.“SUM”
ORDER BY T1.“CardName”,T0.“BaseRef” ASC
)