Skip to Content
author's profile photo Former Member
Former Member

Actualizar Query 02

Hola colegas, tengo la siguiente Query

 SELECT
T3.Slpname AS 'Vendedor',
T1.DocNum AS 'Número Pago',
T0.DocNum AS 'Número de Factura', 
T0.CardName AS 'Nombre de deudor/acreedor', 
T1.DocDate AS 'Fecha Pago',
T0.TaxDate AS 'F.Emision Factura', 
T0.DocDueDate AS 'F.Vcto Factura.', 
DATEADD(day, 60,T0.TaxDate) AS 'Fecha Vence Pago', 
DATEDIFF(day, DATEADD(day, 60,T0.TaxDate), T1.DocDate) AS Numdias,
T4.SumApplied AS 'Monto Pagado',
T0.DocTotal AS 'Total factura', 
(T0.DocTotal-T0.VatSum) AS 'Neto Factura',
T7.ItemCode AS 'Código Item',
T7.Dscription AS 'Nombre Item',
T7.Quantity AS 'Cantidad'

FROM
[dbo].[OINV] T0 
INNER JOIN [dbo].[RCT2] T4 ON T4.DocEntry = T0.DocEntry
INNER JOIN [dbo].[ORCT] T1 ON T4.DocNum = T1.DocNum 
INNER JOIN [dbo].[OSLP] T3 ON T0.SlpCode = T3.SlpCode
INNER JOIN [dbo].[INV1] T7 ON T0.DocEntry = T7.DocENtry

WHERE ( T1.DocDate >='[%0]' and T1.DocDate <='[%1]' ) and T1.CheckSum = 0


UNION ALL


SELECT
T3.Slpname as 'Vendedor',
T1.DocNum AS 'Número Pago',
T0.DocNum AS 'Número de Factura',
T0.CardName AS 'Nombre de deudor/acreedor',
T5.DueDate AS 'Fecha Pago',
T0.TaxDate AS 'F.Emision Factura',
T0.DocDueDate AS 'F.Vcto Factura.',
DATEADD(day, 60,T0.TaxDate) AS 'Fecha Vence Pago',
DATEDIFF(day, DATEADD(day, 60,T0.TaxDate), T5.DueDate) AS Numdias,
T5.CheckSum AS 'Abonado a la fecha',
T0.DocTotal AS 'Total factura',
(T0.DocTotal-T0.VatSum) AS 'Neto Factura',
'',
'',
0

FROM  [dbo].[OINV] T0
INNER JOIN [dbo].[RCT2] T4 ON T0.DocEntry = T4.DocEntry 
INNER JOIN [dbo].[ORCT] T1 ON T4.DocNum = T1.DocNum 
INNER JOIN [dbo].[RCT1] T5 ON T1.DocNum = T5.DocNum 
INNER JOIN [dbo].[OSLP] T3 ON T0.SlpCode = T3.SlpCode
 
WHERE ( T5.DueDate >='[%0]' and T5.DueDate <='[%1]' )

A esta Query me piden agregarle los campos que aparecen en la consulta Analisis de ventas por articulos (Anual) con las siguientes opciones marcadas: Informe anual, Facturas, Visualización y Total por empleado depto. ventas.

Los campos a agregar son los siguientes: Cantidad, Importe de ventas, Ganancia bruta y % de ganancia bruta.

El campo Cantidad lo saqué de la tabla INV1, pero los otros campos no los encuentro, y no se como crearlos.

Espero su ayuda, saludos.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Feb 25, 2009 at 07:01 PM

    Los campos a agregar son los siguientes:

    Cantidad= quantity

    Importe de ventas=(quantity*price) - descuento global

    Ganancia bruta = (quantityprice)-(quantityavgprice "(costo)")

    % Ganancia bruta=Ganancia bruta/Importe de ventas

    Tienes harto trabajo jaja.

    Revisa si esa es el campo del costo o puede ser "stockprice" , todos los datos estan en la inv1.

    att,

    Manuel Lazcano

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Feb 25, 2009 at 09:20 PM

    Importe de ventas=(quantity*price) - descuento global

    Porqué un descuento, si el precio ya viene con el descuento o no?

    Ahí quedé perdido.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Feb 26, 2009 at 04:07 PM

    Para:

    Ganancia Bruta es: grssprofit en INV1

    Importe de Ventas es: Linetotal en INV1

    Cantidad es: Quantity en INV1

    Y si como % de Ganancia Bruta te refieres al Margen yo uso:

    ( grssprofit/Linetotal ) * 100

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.