FiveTech Support Forums

     SELECT a.fecha, a.tropaano, a.tropanumero, a.correlativo, ta.descripcion,
          ROUND(NULLIF(t.kilosenpie, 0) / NULLIF(t.cabezas, 0), 2) AS kilosenpie,
          (a.kilos + b.kilos) AS KgsEn2da,
          ROUND( ((a.kilos + b.kilos) / NULLIF(t.kilosenpie / NULLIF(t.cabezas, 0), 0)) * 100, 2 ) AS 'Rend.', 
          b.kosher AS [Ritual], IIF(a.hilton = 0x54, 'SI', 'NO') AS hilton,
          IIF(a.tipodehacienda = 0, 'NO', 'SI') AS organico, t.productor AS 'No.Prod',
          prd.nombre AS 'Productor', t.consignatario AS 'No.Con.', con.nombre AS 'Consignatario',
          t.dicoseorigen, dpt.nombre AS 'Departamento',
          IIF(t.moneda = 22, 'Dólar', 'Peso U.') AS Moneda,
          IIF(t.moneda = 22, ROUND(NULLIF(fh.subtotal, 0) / NULLIF(td.kilosen2, 0), 2),
               ROUND(NULLIF(fh.subtotal, 0) / NULLIF(td.kilosen2, 0) / NULLIF(fh.cotizaciondolar, 0), 2)
          ) AS 'Precio U$S'
     FROM C10304 a WITH (NOLOCK)
     INNER JOIN C10304 b WITH (NOLOCK)
          ON a.establecimiento = b.establecimiento AND a.fecha = b.fecha AND a.correlativo = b.correlativo
          AND b.estado < 5 AND b.tipoderegistro = 4 AND b.mediares = 2
     INNER JOIN C10203 t WITH (NOLOCK) ON t.empresa = a.empresa AND t.especie = a.tropaespecie
          AND t.ano = a.tropaano AND t.numero = a.tropanumero
     INNER JOIN C10204 td WITH (NOLOCK) ON td.empresa = a.empresa AND td.especie = a.tropaespecie
          AND td.ano = a.tropaano AND td.numero = a.tropanumero AND td.ordendepesada = a.tropaorden
     LEFT JOIN P0025 prd WITH (NOLOCK) ON prd.establecimiento = a.establecimiento 
          AND prd.codigo = t.productor
     LEFT JOIN P0025 con WITH (NOLOCK) ON con.establecimiento = a.establecimiento 
          AND con.codigo = t.consignatario
     LEFT JOIN P0015 dpt WITH (NOLOCK)
          ON dpt.codigo = prd.departamento
     LEFT JOIN P0021 ta WITH (NOLOCK)
          ON ta.codigo = a.tipodeanimal
     OUTER APPLY ( 
          SELECT MIN(fc.cotizaciondolar) AS cotizaciondolar, SUM(fd.subtotal) AS subtotal
               FROM C10210 fc WITH (NOLOCK)
          INNER JOIN C10211 fd WITH (NOLOCK) ON fd.empresa = fc.empresa AND fd.pof = fc.pof
               AND fd.numero = fc.numero AND fd.tropalinea = td.ordendepesada
          WHERE fc.empresa = a.empresa AND fc.estado < 9 AND fc.pof = 'P' AND fc.tropaespecie = a.tropaespecie
               AND fc.tropaano = a.tropaano AND fc.tropanumero = a.tropanumero
          ) fh
     LEFT JOIN P0025 dio WITH (NOLOCK)
          ON dio.establecimiento = a.establecimiento 
          AND dio.dicose = t.dicoseorigen
     WHERE a.empresa = 1 AND a.estado < 5 AND a.tipoderegistro = 4 AND a.mediares = 1 
          AND a.fecha BETWEEN '20250301' AND '20250522'
     ORDER BY a.fecha, a.tropaano, a.tropanumero, a.correlativo;

text into cSql
     
     SELECT a.fecha, a.tropaano, a.tropanumero, a.correlativo, ta.descripcion,
          ROUND(NULLIF(t.kilosenpie, 0) / NULLIF(t.cabezas, 0), 2) AS kilosenpie,
          (a.kilos + b.kilos) AS KgsEn2da,
          ROUND( ((a.kilos + b.kilos) / NULLIF(t.kilosenpie / NULLIF(t.cabezas, 0), 0)) * 100, 2 ) AS 'Rend.', 
          b.kosher AS [Ritual], IIF(a.hilton = 0x54, 'SI', 'NO') AS hilton,
          IIF(a.tipodehacienda = 0, 'NO', 'SI') AS organico, t.productor AS 'No.Prod',
          prd.nombre AS 'Productor', t.consignatario AS 'No.Con.', con.nombre AS 'Consignatario',
          t.dicoseorigen, dpt.nombre AS 'Departamento',
          IIF(t.moneda = 22, 'Dólar', 'Peso U.') AS Moneda,
          IIF(t.moneda = 22, ROUND(NULLIF(fh.subtotal, 0) / NULLIF(td.kilosen2, 0), 2),
               ROUND(NULLIF(fh.subtotal, 0) / NULLIF(td.kilosen2, 0) / NULLIF(fh.cotizaciondolar, 0), 2)
          ) AS 'Precio U$S'
     FROM C10304 a WITH (NOLOCK)
     INNER JOIN C10304 b WITH (NOLOCK)
          ON a.establecimiento = b.establecimiento AND a.fecha = b.fecha AND a.correlativo = b.correlativo
          AND b.estado < 5 AND b.tipoderegistro = 4 AND b.mediares = 2
     INNER JOIN C10203 t WITH (NOLOCK) ON t.empresa = a.empresa AND t.especie = a.tropaespecie
          AND t.ano = a.tropaano AND t.numero = a.tropanumero
     INNER JOIN C10204 td WITH (NOLOCK) ON td.empresa = a.empresa AND td.especie = a.tropaespecie
          AND td.ano = a.tropaano AND td.numero = a.tropanumero AND td.ordendepesada = a.tropaorden
     LEFT JOIN P0025 prd WITH (NOLOCK) ON prd.establecimiento = a.establecimiento 
          AND prd.codigo = t.productor
     LEFT JOIN P0025 con WITH (NOLOCK) ON con.establecimiento = a.establecimiento 
          AND con.codigo = t.consignatario
     LEFT JOIN P0015 dpt WITH (NOLOCK)
          ON dpt.codigo = prd.departamento
     LEFT JOIN P0021 ta WITH (NOLOCK)
          ON ta.codigo = a.tipodeanimal
     OUTER APPLY ( 
          SELECT MIN(fc.cotizaciondolar) AS cotizaciondolar, SUM(fd.subtotal) AS subtotal
               FROM C10210 fc WITH (NOLOCK)
          INNER JOIN C10211 fd WITH (NOLOCK) ON fd.empresa = fc.empresa AND fd.pof = fc.pof
               AND fd.numero = fc.numero AND fd.tropalinea = td.ordendepesada
          WHERE fc.empresa = a.empresa AND fc.estado < 9 AND fc.pof = 'P' AND fc.tropaespecie = a.tropaespecie
               AND fc.tropaano = a.tropaano AND fc.tropanumero = a.tropanumero
          ) fh
     LEFT JOIN P0025 dio WITH (NOLOCK)
          ON dio.establecimiento = a.establecimiento 
          AND dio.dicose = t.dicoseorigen
     WHERE a.empresa = 1 AND a.estado < 5 AND a.tipoderegistro = 4 AND a.mediares = 1 
          AND a.fecha BETWEEN '20250301' AND '20250522'
     ORDER BY a.fecha, a.tropaano, a.tropanumero, a.correlativo;
        
     ENDTEXT 
            
     oRs := FW_OpenRecordSet( oCn2, cSql )

     IF !oRs:Eof()
          xBrowse( oRs )
          oRs:Close()
     ELSE
          MsgAlert( 'No se pudo conectar con la base de datos' ) 
     ENDIF

     ENDTEXT