on 01-14-2013 8:30 PM
Buenas tardes
Alguien tendrá una respuesta a esto, tengo el siguiente query
que nos arroja el monto de ventas mensuales por cliente y en algunos casos por
un grupo de estos, funciona muy bien pero queremos agregarle el Budget o presupuesto
mensual asignado a dichos clientes y que nos indique el % ya sea positivo
cuando se haya cumplido la meta o negativo cuando no, la cantidad del monto
presupuestado es fija es decir no se cambia ya que son cantidades asignadas a principios
de año
Saludos y gracias a todos
Select Distinct Month(T0.DocDate) As 'Mes', IsNull((Select Sum(X.DocTotal -
X.VatSum) As Expr1 From OINV As X
Where Month(X.DocDate) = Month(T0.DocDate) And Year(X.DocDate) = 2013 And
X.CardCode = N'c0030' And X.DocType = 'I'), 0) - IsNull((Select
Sum(Y.DocTotal - Y.VatSum) As Expr1 From ORIN As Y
Where Month(Y.DocDate) = Month(T0.DocDate) And Year(Y.DocDate) = 2013 And
Y.CardCode = N'c0030' And Y.DocType = 'I'), 0) As [Sears 2013],
IsNull((Select Sum(X.DocTotal - X.VatSum) As Expr1 From OINV As X
Where Month(X.DocDate) = Month(T0.DocDate) And Year(X.DocDate) = 2013 And
X.CardCode = N'c0002' And X.DocType = 'I'), 0) - IsNull((Select
Sum(Y.DocTotal - Y.VatSum) As Expr1 From ORIN As Y
Where Month(Y.DocDate) = Month(T0.DocDate) And Year(Y.DocDate) = 2013 And
Y.CardCode = N'c0002' And Y.DocType = 'I'), 0) As [Liverpool 2013],
IsNull((Select Sum(X.DocTotal - X.VatSum) As Expr1 From OINV As X
Where Month(X.DocDate) = Month(T0.DocDate) And Year(X.DocDate) = 2013 And
X.CardCode = N'c0003' And X.DocType = 'I'), 0) - IsNull((Select
Sum(Y.DocTotal - Y.VatSum) As Expr1 From ORIN As Y
Where Month(Y.DocDate) = Month(T0.DocDate) And Year(Y.DocDate) = 2013 And
Y.CardCode = N'c0003' And Y.DocType = 'I'), 0) As [PH 2013], IsNull((Select
Sum(X.DocTotal - X.VatSum) As Expr1 From OINV As X
Where Month(X.DocDate) = Month(T0.DocDate) And Year(X.DocDate) = 2013 And
X.CardCode = N'c0004' And X.DocType = 'I'), 0) - IsNull((Select
Sum(Y.DocTotal - Y.VatSum) As Expr1 From ORIN As Y
Where Month(Y.DocDate) = Month(T0.DocDate) And Year(Y.DocDate) = 2013 And
Y.CardCode = N'c0004' And Y.DocType = 'I'), 0) As [Suburbia 2013],
IsNull((Select Sum(X.DocTotal - X.VatSum) As Expr1 From OINV As X
Where Month(X.DocDate) = Month(T0.DocDate) And Year(X.DocDate) = 2013 And
X.CardCode = N'c0006' And X.DocType = 'I'), 0) - IsNull((Select
Sum(Y.DocTotal - Y.VatSum) As Expr1 From ORIN As Y
Where Month(Y.DocDate) = Month(T0.DocDate) And Year(Y.DocDate) = 2013 And
Y.CardCode = N'c0006' And Y.DocType = 'I'), 0) As [Sanborns 2013],
IsNull((Select Sum(X.DocTotal - X.VatSum) As Expr1 From OINV As X
Where Month(X.DocDate) = Month(T0.DocDate) And Year(X.DocDate) = 2013 And
X.CardCode = N'c0095' And X.DocType = 'I'), 0) - IsNull((Select
Sum(Y.DocTotal - Y.VatSum) As Expr1 From ORIN As Y
Where Month(Y.DocDate) = Month(T0.DocDate) And Year(Y.DocDate) = 2013 And
Y.CardCode = N'c0095' And Y.DocType = 'I'), 0) As [Sephora 2013],
IsNull((Select Sum(X.DocTotal - X.VatSum) As Expr1 From OINV As X
Where Month(X.DocDate) = Month(T0.DocDate) And Year(X.DocDate) = 2013 And
Not (X.CardCode Like N'c0001') And Not (X.CardCode Like N'c0002') And
Not (X.CardCode Like N'c0003') And Not (X.CardCode Like N'c0004') And
Not (X.CardCode Like N'c0006') And Not (X.CardCode Like N'c0023') And
Not (X.CardCode Like N'c0024') And Not (X.CardCode Like N'c0030') And
Not (X.CardCode Like N'c0038') And Not (X.CardCode Like N'c0050') And
Not (X.CardCode Like N'c0071') And Not (X.CardCode Like N'c0076') And
Not (X.CardCode Like N'c0082') And Not (X.CardCode Like N'c0095') And
X.DocType = 'I'), 0) - IsNull((Select Sum(Y.DocTotal - Y.VatSum) As Expr1
From ORIN As Y
Where Month(Y.DocDate) = Month(T0.DocDate) And Year(Y.DocDate) = 2013 And
Not (Y.CardCode Like N'c0001') And Not (Y.CardCode Like N'c0002') And
Not (Y.CardCode Like N'c0003') And Not (Y.CardCode Like N'c0004') And
Not (Y.CardCode Like N'c0006') And Not (Y.CardCode Like N'c0023') And
Not (Y.CardCode Like N'c0024') And Not (Y.CardCode Like N'c0030') And
Not (Y.CardCode Like N'c0038') And Not (Y.CardCode Like N'c0050') And
Not (Y.CardCode Like N'c0071') And Not (Y.CardCode Like N'c0076') And
Not (Y.CardCode Like N'c0082') And Not (Y.CardCode Like N'c0082') And
Y.DocType = 'I'), 0) As [Independientes 2013], IsNull((Select Sum(X.DocTotal
- X.VatSum) As Expr1 From OINV As X
Where Month(X.DocDate) = Month(T0.DocDate) And Year(X.DocDate) = 2013 And
X.CardCode = N'c0050' And X.DocType = 'I'), 0) - IsNull((Select
Sum(Y.DocTotal - Y.VatSum) As Expr1 From ORIN As Y
Where Month(Y.DocDate) = Month(T0.DocDate) And Year(Y.DocDate) = 2013 And
Y.CardCode = N'c0050' And Y.DocType = 'I'), 0) As [Otros 2013]
From OINV As T0
Order By 'Mes'
Revisa este enlace
http://saptogo.blogspot.com/2010/07/sap-bo-sap-business-one-manejo-de.html
puede ser útil a tus propósitos.
Saludos
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hola Victor.
¿Cómo están registrando en SB1 el presupuesto de cada cliente?.
Podrían por ejemplo crear un campo de usuario (UDF) en los datos maestros de SN en el que podrían registrar este valor del presupuesto anual para cada cliente y luego compararlo contra las ventas del período, agregando dicho UDF en el query para comparar ambos valores.
Saludos cordiales.
Gracias por tu respuesta
Hice varias pruebas, una de ellas fue crear una tabla
definidas por el usuario para precisamente poder capturar la cuota asignada a
los clientes, por mes para de ahí tomar estos datos y poder compararlos vs la
venta actual, ya que el reporte se requiere por avance mensual, envío imagen de
ejemplo
Saludos
User | Count |
---|---|
99 | |
9 | |
8 | |
5 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.