CEST x MVA - função para encontrar MVA por CEST

[rochinha]

Amiguinhos,

Função simples para retornar o MVA por faixa de CEST:

Código: Selecionar todos

FUNCTION CEST2MVA( _cest_ ) // _cest_ - parametro inteiro
   **************************************************************
   * CEST Inicial 1
   * CEST Final 2
   * MVA          3
   **************************************************************
   local aCEST2MVA := {}
   IF _cest_ <> 0
      aadd( aCEST2MVA, { 100100, 199900,65.29} )
      aadd( aCEST2MVA, { 200100, 299900,67.49} )
      aadd( aCEST2MVA, { 300100, 302500,73.63} )
      aadd( aCEST2MVA, { 500100, 500100,29.94} )
      aadd( aCEST2MVA, { 400100, 400200,94.70} )
      aadd( aCEST2MVA, { 800100, 801900,55.61} )
      aadd( aCEST2MVA, { 802000, 802300,55.61} )
      aadd( aCEST2MVA, { 801901, 801901,65.29} )
      aadd( aCEST2MVA, { 900100, 900200,83.24} )
      aadd( aCEST2MVA, { 900300, 900400,108.84} )
      aadd( aCEST2MVA, { 900500, 900500,64.06} )
      aadd( aCEST2MVA, {1000100,1008000,74.75} )
      aadd( aCEST2MVA, {1100100,1101200,75.80} )
      aadd( aCEST2MVA, {1200100,1200900,63.28} )
      aadd( aCEST2MVA, {1300100,1301600,60.35} )
      aadd( aCEST2MVA, {1400100,1401300,66.52} )
      aadd( aCEST2MVA, {1600100,1600900,78.79} )
      aadd( aCEST2MVA, {1700100,1711500,59.75} )
      aadd( aCEST2MVA, {1900100,1903300,73.90} )
      aadd( aCEST2MVA, {2000100,2006400,75.80} )
      aadd( aCEST2MVA, {2100100,2112600,53.86} )
      aadd( aCEST2MVA, {2200100,2200100,68.57} )
      aadd( aCEST2MVA, {2300100,2300200,61.78} )
      aadd( aCEST2MVA, {2400100,2400300,72.12} )
      nCEST2MVA := ascan( aCEST2MVA, { |x| _cest_>=x[1] .AND. _cest_<=x[2] } )
      IF nCEST2MVA <> 0
         return aCEST2MVA[ nCEST2MVA ][ 3 ]
      ENDIF
   ENDIF
   RETURN 0

Melhorem na se quiserem.

[JoséQuintas]

Código: Selecionar todos

FUNCTION Cest2MVA( nCest )

   LOCAL nPos, aList := { ;   // 1=inicial, 2=final, 3=MVA
      {  100100,  199900, 65.29 }, ;
      {  200100,  299900, 67.49 }, ;
      {  300100,  302500, 73.63 }, ;
      {  500100,  500100, 29.94 }, ;
      {  400100,  400200, 94.70 }, ;
      {  800100,  801900, 55.61 }, ;
      {  802000,  802300, 55.61 }, ;
      {  801901,  801901, 65.29 }, ;
      {  900100,  900200, 83.24 }, ;
      {  900300,  900400, 108.84 }, ;
      {  900500,  900500, 64.06 }, ;
      { 1000100, 1008000, 74.75 }, ;
      { 1100100, 1101200, 75.80 }, ;
      { 1200100, 1200900, 63.28 }, ;
      { 1300100, 1301600, 60.35 }, ;
      { 1400100, 1401300, 66.52 }, ;
      { 1600100, 1600900, 78.79 }, ;
      { 1700100, 1711500, 59.75 }, ;
      { 1900100, 1903300, 73.90 }, ;
      { 2000100, 2006400, 75.80 }, ;
      { 2100100, 2112600, 53.86 }, ;
      { 2200100, 2200100, 68.57 }, ;
      { 2300100, 2300200, 61.78 }, ;
      { 2400100, 2400300, 72.12 } }
      
   IF nCest != 0   
      nPos := AScan( aList, { | Item | nCest >= Item[ 1 ] .AND. nCest <= Item[ 2 ] } )
      IF nPos != 0
         RETURN aList[ nPos, 3 ]
      ENDIF
   ENDIF
   
   RETURN 0

Um pouco mais enxuta.

[Kapiaba]

Tarde Rochinhas, KD o banco de dados meu rei, para eu brincar aqui?

Obg. abs.

Regards, saludos.

[rochinha]

Amiguinhos,

kapiaba
Tá no vetor da função. Você passa o CEST limpo de pontuação, se for encontrado no range dos elementos ela devolve o percentual.

BNB

[JoséQuintas]

Agora em versão pra SQL

Código: Selecionar todos

CREATE FUNCTION ze_IVAFromCest( nCest INT(11) )
RETURNS DECIMAL(6,2)

BEGIN

DECLARE nPercentual DECIMAL(6,2);

SET nPercentual :=
CASE
   WHEN nCest BETWEEN  100100 AND  199900 THEN 65.29
   WHEN nCest BETWEEN  200100 AND  299900 THEN 67.49
   WHEN nCest BETWEEN  300100 AND  302500 THEN 73.63
   WHEN nCest BETWEEN  500100 AND  500100 THEN 29.94
   WHEN nCest BETWEEN  400100 AND  400200 THEN 94.70
   WHEN nCest BETWEEN  800100 AND  801900 THEN 55.61
   WHEN nCest BETWEEN  802000 AND  802300 THEN 55.61
   WHEN nCest BETWEEN  801901 AND  801901 THEN 65.29
   WHEN nCest BETWEEN  900100 AND  900200 THEN 83.24
   WHEN nCest BETWEEN  900300 AND  900400 THEN 108.84
   WHEN nCest BETWEEN  900500 AND  900500 THEN 64.06
   WHEN nCest BETWEEN 1000100 AND 1008000 THEN 74.75
   WHEN nCest BETWEEN 1100100 AND 1101200 THEN 75.80
   WHEN nCest BETWEEN 1200100 AND 1200900 THEN 63.28
   WHEN nCest BETWEEN 1300100 AND 1301600 THEN 60.35
   WHEN nCest BETWEEN 1400100 AND 1401300 THEN 66.52
   WHEN nCest BETWEEN 1600100 AND 1600900 THEN 78.79
   WHEN nCest BETWEEN 1700100 AND 1711500 THEN 59.75
   WHEN nCest BETWEEN 1900100 AND 1903300 THEN 73.90
   WHEN nCest BETWEEN 2000100 AND 2006400 THEN 75.80
   WHEN nCest BETWEEN 2100100 AND 2112600 THEN 53.86
   WHEN nCest BETWEEN 2200100 AND 2200100 THEN 68.57
   WHEN nCest BETWEEN 2300100 AND 2300200 THEN 61.78
   WHEN nCest BETWEEN 2400100 AND 2400300 THEN 72.12
   ELSE 0
END;

RETURN nPercentual;

END