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
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
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
de hecho con LEFT JOIN o con cualquien INNER me manda el mismo resultado ya lo intente
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
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’