Consulta con sumatorias para informe [Solucionado]

[thrall]

Buen día:

Tengo una consulta que utilizo para generar un informe de pago.

Hay un campo llamado Amortiz, que es la amortización de un anticipo que se da al inicio de un contrato de obra.

El anticipo que se otorga es en este caso del 20% del monto del contrato, por lo que la amortización en cada periodo de obra es el 20% del monto del periodo.

En la columna saldo_anticipo se muestra el monto pendiente por amortizar.

Lo que requiero es que en el último periodo la amortización sea igual al penúltimo saldo del anticipo antes de que el saldo sea 0.

Como se puede ver en la imagen, la amortización debe ser de $51.54 y no de $2,466.55 que corresponderían al 20%, y que corresponde con el saldo del anticipo en el penúltimo periodo (11).

Pongo también la instrucción de SQL que utilizo para obtener esta tabla. Cabe mencionar que los datos los obtengo de otras consultas.

Código: Seleccionar todo

SELECT Tmp.Periodo, Tmp.Monto, Tmp.Anticipo, Tmp.Pct_Amortiz,

Sum(Imp_Aut) AS Imp_Per,

Imp_Per*Pct_Amortiz AS Amortiz,

(SELECT SUM(qryGen.Imp_Aut) AS Imp_Acum
FROM qryGen
WHERE qryGen.Periodo<= Tmp.Periodo) AS Imp_Acum,

SWITCH(
(SELECT SUM(qryGen.Imp_Aut)*Pct_Amortiz AS Amortiz_Acum FROM qryGen WHERE qryGen.Periodo<=Tmp.Periodo GROUP BY qryGen.Pct_Amortiz)>Tmp.Anticipo,Tmp.Anticipo,
(SELECT SUM(qryGen.Imp_Aut)*Pct_Amortiz AS Amortiz_Acum FROM qryGen WHERE qryGen.Periodo<=Tmp.Periodo GROUP BY qryGen.Pct_Amortiz)<Tmp.Anticipo,
(SELECT SUM(qryGen.Imp_Aut)*Pct_Amortiz AS Amortiz_Acum
FROM qryGen
WHERE qryGen.Periodo<=Tmp.Periodo
GROUP BY qryGen.Pct_Amortiz) ) AS Amortiz_Acum,

Monto-Imp_Acum AS Saldo,

SWITCH(
Anticipo-Amortiz_Acum<=0,0,
Anticipo-Amortiz_Acum>0,Anticipo-Amortiz_Acum) AS Saldo_Anticipo,

Imp_Per-Amortiz AS Adeudo,

Imp_Per-Amortiz AS Subtotal,

Subtotal*0.16 AS IVA,

Subtotal+IVA AS TotFactura

FROM qryGen AS Tmp
WHERE Tmp.Period=-1 AND Tmp.Periodo IS Not Null
GROUP BY Tmp.Periodo, Tmp.Monto, Tmp.Anticipo, Tmp.Pct_Amortiz;


La separación es solamente para saber como obtengo cada uno de los campos de la tabla.

Sin más por el momento, agradezco su amable atención.

Gracias

[DaniLopez1991]

Mira los orígenes de las otras consultas, la verdad es que no entiendo muy bien el problema, y el calculo que hace mal.

Lo siento, si puedes explícalo mas detallado y con algún ejemplo

[thrall]

Dani, muchas gracias por contestar.

El problema no es tan complejo como parece, puede que lo haya hecho ver así, pero no lo es, trataré de explicarlo con un ejemplo, que en principio son las tablas que están mostradas.

Tengo un contrato de obra con un monto especificado para erogar, de ese monto se le da a la empresa que va a realizar las obras un porcentaje del monto como anticipo, que para este caso fue de un 20%.

Monto del contrato (MC): $119,365.81
Porcentaje de Anticipo (Pct): 20%
Anticipo= MC*Pct
Ant =$119,365.81*0.2
Ant=$23,873.16

Cada fin de periodo la empresa debe entregar un avance y obras realizadas, así como el monto de esas obras realizadas. Aquí es donde entra la consulta desde donde se obtienen los montos del periodo, las cuales no son otra cosa sino una lista de de cada obra que realizó y el monto de cada obra.

Monto del Periodo (MP) = Suma de las obras en el periodo
MP=$7,866.01

Del monto total del periodo se necesita recuperar parte del anticipo que se le entregó al inicio del contrato (amortización), por lo que de este monto se le obtiene el 20% (coincidente con el porcentaje que se le dio de anticipo) y se le resta al monto del periodo para obtener un subtotal o adeudo.

Amortización (A)=MP*Pct
A=$7,866.01*0.2
A=$1,573.20

Subtotal o Adedudo (SB)=MP-A
SB=$7,866.01-$1,573.20
SB=$6,292.81

Del subtotal se obtiene el monto por el IVA (Impuesto al Valor Agregado) siendo este del 16% y finalmente se suman el subtotal y el IVA para obtener el monto de la factura.

IVA=SB*0.16
IVA=$6,292.81*0.16
IVA=$1,006.85

Factura (F)=SB+IVA
F=$6,292.81+$1,006.85
F=$7,299.66

Lo campos Monto Acumulado (MA) y Amortización Acumulada (AA) son la suma de los Montos de los periodos o de amortización hasta el periodo actual, para el caso el periodo 12.

Los campos Saldo y Saldo_Anticipo se refieren a los montos restantes del contrato y del Anticipo, los cuales se obtienen se la siguiente manera.
Saldo (SC)=MC-MA
SC=$7,299.66-$1,573.20
SC=$111,499.80

Saldo Anticipo (SA)=Ant-AA
SA=$23,873.16-$1,573.20
SA=$22,299.96

Una vez que se recupera el anticipo otorgado, el SA debe ser siempre 0, esto como se puede ver en las tablas ya lo tengo, pues hago uso de la función SWITCH donde evalúo si el SA es menor a 0 entonces lo pongo como 0 y si es mayor a 0, entonces es el valor de SA.

De igual forma, la amortización acumulada nunca puede ser mayor al anticipo otorgado, por lo que al igual que con el SA, utilizo la función SWITCH para evaluar si AA es mayor que el anticipo, de ser así, reemplazo cualquier valor con el monto del anticipo, si AA es menor que el anticipo, el valor de AA se queda en el campo.

Hasta aquí creo que los cálculos realizados quedan claros.

El problema se da en el campo A, pues este siempre calcula el 20% del MP sin embargo, la Amortización del último periodo debería ser el Saldo del Anticipo del penúltimo periodo pues ya no se puede amortizar más de lo que tengo de saldo.

Por lo tanto, lo único que requiero es una manera de colocar en el campo de amortización del último periodo el valor del saldo del anticipo del penúltimo periodo.

Espero haya quedado más claro el problema.

Gracias

[DaniLopez1991]

En este switch:

SWITCH(
Anticipo-Amortiz_Acum<=0,0,
Anticipo-Amortiz_Acum>0,Anticipo-Amortiz_Acum) AS Saldo_Anticipo,

EL valor [Anticipo-Amortiz_Acum] lo puedes depurar y mirar que cantidad sale? porque si sale el valor que pone, deberías mirar donde esta haciendo el calculo mal o porque te da ese resultado

Saludos

[thrall]

Gracias nuevamente Dani,

El valor que muestra el switch en comento ya es evaluando la condición pues de lo contrario, el Saldo_Anticipo se vuelve un valor negativo, lo cual no puede ser.

Adicionalmente el valor mostrado por el switch de Amortiz_Acum también ya está evaluado pues de lo contrario, la Amortización Acumulada sería superior al Anticipo, lo cual tampoco puede ser.

Dejo una tabla con los valores que arrojan los campos Saldo_Anticipo y Amortiz_Acum antes de colocar el Switch.

Aprovecho para comentar también que los cálculos están realizados de acuerdo a archivos de Excel mostrados para la realización de este informe.

Para la obtención de la cuota de amortización no se tiene ni plazo ni intereses, solo es la multiplicación del porcentaje de amortización (que coincide con el porcentaje de anticipo) por el monto del periodo, por lo que el plazo en el cual se amortice el anticipo dependerá única y exclusivamente del monto del periodo.

Expuesto esto, llegará un periodo en el cual el saldo del anticipo sea menor al 20% del monto del periodo, como se puede ver en el periodo 11, y es en este momento en el que requiero que ese saldo sea entonces el monto a amortizar en el siguiente periodo. Tal vez en ese momento, los switch anteriores los pudiera eliminar.

Saludos y gracias.

[thrall]

Investigando más, finalmente pude hacer una función en VBA para utilizarla en la consulta de SQL.

Como parámetros le paso el importe del periodo, el porcentaje de amortización y el saldo del anticipo. Dentro de la función, obtengo la amortización correspondiente a cada periodo siempre y cuando el saldo del anticipo sea mayor a 0, en caso de que el saldo del anticipo sea menor, entonces como amortización pongo saldo del anticipo del periodo anterior, el cual previamente lo tengo ya guardado.

Dejo la función por si le llega a servir a alguien.

Código: Seleccionar todo

Public Function Amort(ImpPer, Pct, Saldo) As Currency
    Static SaldoAnterior As Double
    Static Amortizacion As Double
    If IsNull(Saldo) Then
        SaldoAnterior = 0
        Exit Function
    End If
    If Saldo > 0 Then
        Amortizacion = ImpPer * Pct
        Amort = Amortizacion
        SaldoAnterior = Saldo
    ElseIf Saldo < 0 Then
        Amortizacion = SaldoAnterior
        Amort = Amortizacion
    End If
End Function


Saludos.