Consulta sql de ventas

SAP Business One
1

Buenos dias foro estor realizando la siguinete consulta

declare @i datetime, @f datetime
set @i=(/*select top 1 A.DocDate from OINV A where A.DocDate=*/'[%0]')    
set @f=(/*select top 1 A.DocDate from OINV A where A.DocDate=*/'[%1]')
SELECT T0.CardCode AS 'customer code',
T0.CardName AS 'Client name',CASE T0.DocType
When 'S'
then 'invoice service'
when 'I'
then 'Article invoice'
else 'NULL'
end AS 'Purchase Type',
CASE T0.U_TIPO_VENTA
When '1'
then 'invoice Injection'
when '2'
then 'Extrusion invoice'
else 'NULL'
end AS 'Purchase Type',
ISNULL(SUM(T0.doctotalfc)-SUM(T0.VatSumFC),0) as 'TOTAL SIN IVA',ISNULL(SUM(T0.VatSumFC),0) as 'IVA',ISNULL(SUM(T0.doctotalfc),0) as 'TOTAL FACTURA',
Isnull ((SELECT sum(X.DocTotalFC)-SUM(X.VatSumFC) FROM ORIN X WHERE X.Canceled='N' and X.CardCode = T0.CardCode and
 X.DocDate = T0.DocDate),0) As 'Credit notes',
ISNULL(SUM(T0.doctotalfc)-SUM(T0.VatSumFC),0)-ISNULL((SELECT sum(X.DocTotalFC)-SUM(X.VatSumFC) FROM ORIN X WHERE X.Canceled='N' and X.CardCode = T0.CardCode and
 X.DocDate = T0.DocDate),0)'TOTAL VENTA',
T0.DocCur,T0.DocDate
FROM OINV T0 
WHERE T0.DocStatus = 'O' AND T0.SERIES = 4 and T0.DocDate between @i and @f
GROUP BY T0.CardCode,T0.CardName,T0.DocCur,T0.DocType,U_TIPO_VENTA,T0.DocDate,T0.DocStatus
ORDER BY T0.DocDate,T0.CardCode
FOR BROWSE

la cual me trae mis ventas por dia agrupadas por tipo de venta y cliente, lo malo es que solo me trae algunas fecha no me las tre todas. supongo que es por que compara las fechas de movimientos (X.DocDate = T0.DocDate) pero si no realizo esta comparacion de igual manera no me toma en cuenta algunos movimientos de notas de credito.

como puedo hacer mi afectacion si algunas notas de credito no afectan directa mente a la factura

2

Hola Antonio, he visto que tienes otras consultas sin embargo no tienes una presentación, por favor realizala a través de este enlace: #presentaciones.

Aqui no nos gusta ayudar a desconocidos.

Saludos.

Pd muevo este tema a SAP B1

4

Para que te muestre las Notas de Credito que tengan fechas no coincidentes con una factura de su misma fecha y SN y viceversa debes usar el FULL OUTER JOIN de esta manera:

    declare @i datetime, @f datetime
    set @i=(/*select top 1 A.DocDate from OINV A where A.DocDate=*/'[%0]')    
    set @f=(/*select top 1 A.DocDate from OINV A where A.DocDate=*/'[%1]')
    SELECT T0.CardCode AS 'customer code',
    T0.CardName AS 'Client name',CASE T0.DocType
    When 'S'
    then 'invoice service'
    when 'I'
    then 'Article invoice'
    else 'NULL'
    end AS 'Purchase Type',
    CASE T0.U_TIPO_VENTA
    When '1'
    then 'invoice Injection'
    when '2'
    then 'Extrusion invoice'
    else 'NULL'
    end AS 'Purchase Type',
    ISNULL(SUM(T0.doctotalfc)-SUM(T0.VatSumFC),0) as 'TOTAL SIN IVA',ISNULL(SUM(T0.VatSumFC),0) as 'IVA',ISNULL(SUM(T0.doctotalfc),0) as 'TOTAL FACTURA',
    ISNULL(SUM(T1.DocTotalFC)-SUM(T1.VatSumFC) ,0) As 'Credit notes',
   ISNULL(SUM(T0.doctotalfc)-SUM(T0.VatSumFC),0)- ISNULL(SUM(T1.DocTotalFC)-SUM(T1.VatSumFC) ,0) as 'TOTAL VENTA',
    T0.DocCur,T0.DocDate
    FROM OINV T0 FULL OUTER JOIN ORIN T1 ON T0.DocDate = T1.DocDate AND T0.CardCode = T1.CardCode AND  T1.Canceled='N'
    WHERE T0.DocStatus = 'O' AND T0.SERIES = 4 and T0.DocDate between @i and @f
    AND T0.Canceled='N' 
    GROUP BY T0.CardCode,T0.CardName,T0.DocCur,T0.DocType,T0.DocDate,T0.DocStatus
    ORDER BY T0.DocDate,T0.CardCode
    FOR BROWSE

Saludos.

1 me gusta
5

de hecho con LEFT JOIN o con cualquien INNER me manda el mismo resultado ya lo intente

6

Ok, ya vi el error , ciertamente , en LEFT, RIGHT, OUTER las condiciones implicitas para cada tabla hay que colocarlas en el ON de esta manera:

    declare @i datetime, @f datetime
    set @i=(/*select top 1 A.DocDate from OINV A where A.DocDate=*/'[%0]')    
    set @f=(/*select top 1 A.DocDate from OINV A where A.DocDate=*/'[%1]')
    SELECT T0.CardCode AS 'customer code',
    T0.CardName AS 'Client name',CASE T0.DocType
    When 'S'
    then 'invoice service'
    when 'I'
    then 'Article invoice'
    else 'NULL'
    end AS 'Purchase Type',
    CASE T0.U_TIPO_VENTA
    When '1'
    then 'invoice Injection'
    when '2'
    then 'Extrusion invoice'
    else 'NULL'
    end AS 'Purchase Type',
    ISNULL(SUM(T0.doctotalfc)-SUM(T0.VatSumFC),0) as 'TOTAL SIN IVA',ISNULL(SUM(T0.VatSumFC),0) as 'IVA',ISNULL(SUM(T0.doctotalfc),0) as 'TOTAL FACTURA',
    ISNULL(SUM(T1.DocTotalFC)-SUM(T1.VatSumFC) ,0) As 'Credit notes',
    ISNULL(SUM(T0.doctotalfc)-SUM(T0.VatSumFC),0)- ISNULL(SUM(T1.DocTotalFC)-SUM(T1.VatSumFC) ,0) as 'TOTAL VENTA',
    T0.DocCur,T0.DocDate
    FROM OINV T0 FULL OUTER JOIN ORIN T1 
    ON T0.DocDate = T1.DocDate AND T0.CardCode = T1.CardCode AND  T1.Canceled='N'
    AND T0.Canceled='N'  AND T0.SERIES = 4 
    WHERE  T0.DocDate between @i and @f
    GROUP BY T0.CardCode,T0.CardName,T0.DocCur,T0.DocType,T0.DocDate,T0.DocStatus
    ORDER BY T0.DocDate,T0.CardCode
    FOR BROWSE
7

estoy revisando tu consulta pero no me manda nada, y tengo dudas sobre su estructura ya que por lo que veo ocupas FULL OUTER JOIN para unir la tabra de las facturas con las notas de credito.

ya lo coreji esta mal el query T0.DocStatus = ‘O’