Skip to Content
0
Former Member
Jan 14, 2013 at 08:30 PM

Query de ventas mensuales vs monto presupuestado

286 Views

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'