Skip to Content

Query, fechas en subconsultas.

Buenos días compañeros, tengo esta consulta donde quiero ingresar la fecha y que esta sea tomada en la subconsulta, pero no le doy con el chiste.

Pueden ayudarme, muchas gracias.

DECLARE @i Datetime
DECLARE @f Datetime

set @i='[%0]'
set @f='[%1]'

SELECT  "SeriesName",
"Expediciòn Factura",
"Nº Factura",
"DocDate", 
"Cedula SN", 
"Nombre SN", 
"Total Factura",
SUM("Kilos"), 
"Bodega",
"Estado Documento",
"CANCELED", 
"Factura Cancelaciòn"

FROM (SELECT DISTINCT  T2."SeriesName",
T2."Remark" AS "Expediciòn Factura",
T0."DocNum" AS "Nº Factura",
T0."DocDate", 
T5."LicTradNum" AS "Cedula SN", 
T0."CardName" AS "Nombre SN", 
T0."DocTotal" AS "Total Factura",
T1."Quantity" * T21."Weight1" AS "Kilos", 
T1."WhsCode" AS "Bodega",
CASE WHEN T0."DocStatus" = 'C' THEN 'CERRADO' ELSE 'ABIERTO' END AS "Estado Documento",
T0."CANCELED", 
T10."DocNum"  AS "Factura Cancelaciòn"

FROM OINV T0  												-- Factura deudor
INNER JOIN INV1 T1 ON T0."DocEntry" = T1."DocEntry"						-- Factura deudor Lineas
LEFT JOIN OUOM T21 ON T1."UomEntry" = T21."UomEntry"


LEFT JOIN NNM1 T2 ON T0."Series" = T2."Series" AND T0."ObjType" = T2."ObjectCode"			-- Series
LEFT JOIN OSLP T3 ON T0."SlpCode" = T3."SlpCode"							-- Tabla de vendedores
LEFT JOIN INV12 T4 ON T0."DocEntry" = T4."DocEntry"							-- Factura deudores: Extensión de impuestos							
LEFT JOIN OCRD T5 ON T0."CardCode" = T5."CardCode"							-- Socio negocio, datos maestros
LEFT JOIN OCTG A1 ON T0."GroupNum" = A1."GroupNum"							-- Condiciones de pago
LEFT JOIN ODLN T6 ON T1."BaseType" = T6."ObjType" AND T1."BaseRef" = T6."Ref1"				-- Entrega
LEFT JOIN DLN1 T7 ON T6."DocEntry" = T7."DocEntry" AND T7."TargetType" = T0."ObjType" 			-- Entrega Lineas
                                   AND T1."BaseLine" = T7."LineNum" 
LEFT JOIN ORDR T8 ON T7."BaseType" = T8."ObjType" AND T7."BaseRef" = T8."Ref1"				-- Pedido de cliente
LEFT  JOIN RDR1 T9 ON T8."DocEntry" = T9."DocEntry" AND T6."ObjType" = T9."TargetType" 			-- Pedido de cliente Lineas.
					AND T9."TrgetEntry" = T6."DocEntry"				--
LEFT JOIN OINV T10 ON T1."TargetType" = T10."ObjType" AND T1."TrgetEntry" = T10."DocEntry"		-- Factura deudor.


WHERE T0."DocDate"  BETWEEN @i  AND @f


AND T0."DocNum" = '3007420' 
) AS T10 

GROUP BY 
"SeriesName",
"Expediciòn Factura",
"Nº Factura",
"DocDate", 
"Cedula SN", 
"Nombre SN", 
"Total Factura", 
"Bodega",
"Estado Documento",
"CANCELED", 
"Factura Cancelaciòn"

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Apr 25 at 02:59 PM

    Hola

    añade antes de la consulta el siguiente código:

    /* SELECT T0.docdate FROM ORDR T0 */

    Un saludo

    Agustín

    Add comment
    10|10000 characters needed characters exceeded

    • Pude completar la consulta usando tu código. /* SELECT T0.docdate FROM ORDR T0 */

      Los otros errores los resolví quitando los campos temporales, estoy usando Sap B1 for Hana y me falta consultar mas, pero creo que en Hana no se pueden usar este tipo de tablas de esa manera...

      Muchas gracias Agustin.

      DECLARE @i Datetime
      DECLARE @f Datetime
      
      set @i='[%0]'
      set @f='[%1]'