Hola, si alguien puede ayudarme a construir una funcion por favor, nececito realizar un kardex pero no se como realizar una funcion que me de el saldo acumulado tengo las consultas de saldo inicial y la consulta que me describe entradas y salidas
--CALCULO DE SALDO INICIAL
SELECT
SUM(CASE WHEN A.TRXQTY>0 THEN A.TRXQTY*QTYBSUOM ELSE 0 END)-abs(SUM(CASE WHEN A.TRXQTY<0 OR A.DOCTYPE='3' THEN ABS(A.TRXQTY)*QTYBSUOM ELSE 0 END)) AS SALDO_UNIDADES
FROM IV30300 A, IV00101 B
WHERE A.ITEMNMBR BETWEEN '01-01-019' AND '01-01-019'
AND A.DOCDATE < '2008-12-01'
AND A.ITEMNMBR = B.ITEMNMBR
--CALCULO DE ENTRADA Y SALIDA DE TRANSACCIONES
SELECT A.DOCNUMBR,EXTDCOST,DOCDATE,A.ITEMNMBR,TRXQTY,UNITCOST,TRXLOCTN,TRNSTLOC,A.DEX_ROW_ID,DOCTYPE,
(CASE WHEN TRXQTY>0 THEN TRXQTY*QTYBSUOM ELSE 0 END) AS ENTRADA,
(CASE WHEN TRXQTY>0 THEN (TRXQTY*QTYBSUOM)*UNITCOST ELSE 0 END) AS COSTO_ENTRADA,
(CASE WHEN TRXQTY<0 OR DOCTYPE='3' THEN ABS(TRXQTY)*QTYBSUOM ELSE 0 END)AS SALIDA,
(CASE WHEN TRXQTY<0 OR DOCTYPE='3'THEN (ABS(TRXQTY)*QTYBSUOM)*UNITCOST ELSE 0 END)AS COSTO_SALIDA
FROM IV30300 A
WHERE A.ITEMNMBR BETWEEN '01-01-002' AND '01-01-007'
AND A.DOCDATE BETWEEN '2008-12-01' AND '2008-12-15'
AND TRXLOCTN BETWEEN '01' AND '11'
AND DOCNUMBR='TR-08-0001650'
UNION
SELECT DISTINCT A.DOCNUMBR,EXTDCOST,DOCDATE,A.ITEMNMBR,TRXQTY,UNITCOST,TRXLOCTN,TRNSTLOC,A.DEX_ROW_ID,DOCTYPE,
(CASE WHEN TRXQTY>0 AND DOCTYPE <> '3' THEN TRXQTY*QTYBSUOM ELSE 0 END) AS ENTRADA,
(CASE WHEN TRXQTY>0 THEN (TRXQTY*QTYBSUOM)*UNITCOST ELSE 0 END) AS COSTO_ENTRADA,
(CASE WHEN TRXQTY<0 OR DOCTYPE='3' THEN ABS(TRXQTY)*QTYBSUOM ELSE 0 END)AS SALIDA,
(CASE WHEN DOCTYPE='3' THEN (ABS(TRXQTY)*QTYBSUOM)*UNITCOST ELSE 0 END)AS COSTO_SALIDA
FROM IV30300 A
WHERE A.ITEMNMBR BETWEEN '01-01-002' AND '01-01-007'
AND A.DOCDATE BETWEEN '2008-12-01' AND '2008-12-15'
AND TRXLOCTN BETWEEN '01' AND '11'
AND DOCNUMBR='TR-08-0001650'
GROUP BY A.DOCNUMBR,EXTDCOST,DOCDATE,A.ITEMNMBR,TRXQTY,UNITCOST,TRXLOCTN,TRNSTLOC,A.DEX_ROW_ID,DOCTYPE,QTYBSUOM
ORDER BY A.DEX_ROW_ID,DOCDATE
