SAR Query

[Former Member]

Hi experts,

I want a query based alert.

First i need to prepare this query and then put an alert for " If Max Sales Qty is decreased by Max Sales".

SlNo	Item No	ItemDescription	March Qty	April Qty	May Qty	June Qty	July Qty	August Qty	Avg Sales Qty	Max Sales Qty
1	I415	ItemA	76652	44645	55696	69704	51609	67753	58278	76652
2
3
4
5

Can anybody help me out on this..

With Regards

[kothandaraman_nagarajan · Accepted solution]

Hi,

Try this:

Select [I] as Item#, [N] as ItemName,[ST] as stock,  isnull([1],0) as Jan, isnull([2],0) as Feb, isnull([3],0) as Mar, isnull([4],0) as Apr, isnull([5],0) as May, isnull([6],0) as June, isnull([7],0) as July, isnull([8],0) as Aug, isnull([9],0) as Sept, isnull([10],0) as Oct, isnull([11],0) as Nov, isnull([12],0) as Dec,"AVG" = sum( isnull([1],0) + isnull([2],0)+ isnull([3],0) + isnull([4],0) + isnull([5],0) + isnull([6],0) + isnull([7],0) +  isnull([8],0) + isnull([9],0) +  isnull([10],0) + isnull([11],0) + isnull([12],0))/12,  CASE

        WHEN (isnull([1],0) >= isnull([2],0) AND isnull([1],0) >= isnull([3],0) AND isnull([1],0) >= isnull([4],0) AND isnull([1],0) >= isnull([5],0)

        AND isnull([1],0) >= isnull([6],0) AND isnull([1],0) >= isnull([7],0) AND isnull([1],0) >= isnull([8],0) AND isnull([1],0) >= isnull([9],0)

       AND isnull([1],0) >= isnull([10],0) AND isnull([1],0) >= isnull([11],0)AND isnull([1],0) >= isnull([12],0)

        ) THEN isnull([1],0)

WHEN (isnull([2],0) >= isnull([1],0) AND isnull([2],0) >= isnull([3],0) AND isnull([2],0) >= isnull([4],0) AND isnull([2],0) >= isnull([5],0)

        AND isnull([2],0) >= isnull([6],0) AND isnull([2],0) >= isnull([7],0) AND isnull([2],0) >= isnull([8],0) AND isnull([2],0) >= isnull([9],0)

       AND isnull([2],0) >= isnull([10],0) AND isnull([2],0) >= isnull([11],0)AND isnull([2],0) >= isnull([12],0)

        ) THEN isnull([2],0)

WHEN (isnull([3],0) >= isnull([1],0) AND isnull([3],0) >= isnull([2],0) AND isnull([3],0) >= isnull([4],0) AND isnull([3],0) >= isnull([5],0)

        AND isnull([3],0) >= isnull([6],0) AND isnull([3],0) >= isnull([7],0) AND isnull([3],0) >= isnull([8],0) AND isnull([3],0) >= isnull([9],0)

       AND isnull([3],0) >= isnull([10],0) AND isnull([3],0) >= isnull([11],0)AND isnull([3],0) >= isnull([12],0)

        ) THEN isnull([3],0)

WHEN (isnull([4],0) >= isnull([1],0) AND isnull([4],0) >= isnull([2],0) AND isnull([4],0) >= isnull([3],0) AND isnull([4],0) >= isnull([5],0)

        AND isnull([4],0) >= isnull([6],0) AND isnull([4],0) >= isnull([7],0) AND isnull([4],0) >= isnull([8],0) AND isnull([4],0) >= isnull([9],0)

       AND isnull([4],0) >= isnull([10],0) AND isnull([4],0) >= isnull([11],0)AND isnull([4],0) >= isnull([12],0)

        ) THEN isnull([4],0)

WHEN (isnull([5],0) >= isnull([1],0) AND isnull([5],0) >= isnull([2],0) AND isnull([5],0) >= isnull([3],0) AND isnull([5],0) >= isnull([4],0)

        AND isnull([5],0) >= isnull([6],0) AND isnull([5],0) >= isnull([7],0) AND isnull([5],0) >= isnull([8],0) AND isnull([5],0) >= isnull([9],0)

       AND isnull([5],0) >= isnull([10],0) AND isnull([5],0) >= isnull([11],0)AND isnull([5],0) >= isnull([12],0)

        ) THEN isnull([5],0)

WHEN (isnull([6],0) >= isnull([1],0) AND isnull([6],0) >= isnull([2],0) AND isnull([6],0) >= isnull([3],0) AND isnull([6],0) >= isnull([4],0)

        AND isnull([6],0) >= isnull([5],0) AND isnull([6],0) >= isnull([7],0) AND isnull([6],0) >= isnull([8],0) AND isnull([6],0) >= isnull([9],0)

       AND isnull([6],0) >= isnull([10],0) AND isnull([6],0) >= isnull([11],0)AND isnull([6],0) >= isnull([12],0)

        ) THEN isnull([6],0)

WHEN (isnull([7],0) >= isnull([1],0) AND isnull([7],0) >= isnull([2],0) AND isnull([7],0) >= isnull([3],0) AND isnull([7],0) >= isnull([4],0)

        AND isnull([7],0) >= isnull([5],0) AND isnull([7],0) >= isnull([7],0) AND isnull([7],0) >= isnull([8],0) AND isnull([7],0) >= isnull([9],0)

       AND isnull([7],0) >= isnull([10],0) AND isnull([7],0) >= isnull([11],0)AND isnull([7],0) >= isnull([12],0)

        ) THEN isnull([7],0)

WHEN (isnull([8],0) >= isnull([1],0) AND isnull([8],0) >= isnull([2],0) AND isnull([8],0) >= isnull([3],0) AND isnull([8],0) >= isnull([4],0)

        AND isnull([8],0) >= isnull([5],0) AND isnull([8],0) >= isnull([6],0) AND isnull([8],0) >= isnull([7],0) AND isnull([8],0) >= isnull([9],0)

       AND isnull([8],0) >= isnull([10],0) AND isnull([8],0) >= isnull([11],0)AND isnull([8],0) >= isnull([12],0)

        ) THEN isnull([8],0)

WHEN (isnull([9],0) >= isnull([1],0) AND isnull([9],0) >= isnull([2],0) AND isnull([9],0) >= isnull([3],0) AND isnull([9],0) >= isnull([4],0)

        AND isnull([9],0) >= isnull([5],0) AND isnull([9],0) >= isnull([6],0) AND isnull([9],0) >= isnull([7],0) AND isnull([9],0) >= isnull([8],0)

       AND isnull([9],0) >= isnull([10],0) AND isnull([9],0) >= isnull([11],0)AND isnull([9],0) >= isnull([12],0)

        ) THEN isnull([9],0)

WHEN (isnull([10],0) >= isnull([1],0) AND isnull([10],0) >= isnull([2],0) AND isnull([10],0) >= isnull([3],0) AND isnull([10],0) >= isnull([4],0)

        AND isnull([10],0) >= isnull([5],0) AND isnull([10],0) >= isnull([6],0) AND isnull([10],0) >= isnull([7],0) AND isnull([10],0) >= isnull([8],0)

       AND isnull([10],0) >= isnull([9],0) AND isnull([10],0) >= isnull([11],0)AND isnull([10],0) >= isnull([12],0)

        ) THEN isnull([10],0)

WHEN (isnull([11],0) >= isnull([1],0) AND isnull([11],0) >= isnull([2],0) AND isnull([11],0) >= isnull([3],0) AND isnull([11],0) >= isnull([4],0)

        AND isnull([11],0) >= isnull([5],0) AND isnull([11],0) >= isnull([6],0) AND isnull([11],0) >= isnull([7],0) AND isnull([11],0) >= isnull([8],0)

       AND isnull([11],0) >= isnull([9],0) AND isnull([11],0) >= isnull([10],0)AND isnull([10],0) >= isnull([12],0)

        ) THEN isnull([11],0)

WHEN (isnull([12],0) >= isnull([1],0) AND isnull([12],0) >= isnull([2],0) AND isnull([12],0) >= isnull([3],0) AND isnull([12],0) >= isnull([4],0)

        AND isnull([12],0) >= isnull([5],0) AND isnull([12],0) >= isnull([6],0) AND isnull([12],0) >= isnull([7],0) AND isnull([12],0) >= isnull([8],0)

       AND isnull([12],0) >= isnull([9],0) AND isnull([12],0) >= isnull([10],0)AND isnull([12],0) >= isnull([11],0)

        ) THEN isnull([12],0)

end as max

from(

SELECT T2.[ItemCode] as I, T2.[ItemName] as N, T2.[OnHand] as ST,sum(T1.[Quantity]) as T, month(T0.[DocDate])  as month FROM OINV T0  INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode WHERE year(T0.[DocDate]) = 2006 GROUP BY T2.[ItemCode], T2.[ItemName], T2.[OnHand],T0.[DocDate]

union all

SELECT T2.[ItemCode] as I, T2.[ItemName] as N, T2.[OnHand] as ST,-sum(T1.[Quantity]) as T, month(T0.[DocDate])  as month FROM ORIN T0  INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode WHERE year(T0.[DocDate]) = 2006 GROUP BY T2.[ItemCode], T2.[ItemName], T2.[OnHand],T0.[DocDate] )S

pivot

(sum(t) for month in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]))P

group by  [I],[N], [St],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]

Thanks & Regards,

Nagarajan

[kothandaraman_nagarajan]

Hi,

Try this query without average and maximum function. Try to add, if not let me know.

Select [I] as Item#, [N] as ItemName,[ST] as stock,  isnull([1],0) as Jan, isnull([2],0) as Feb, isnull([3],0) as Mar, isnull([4],0) as Apr, isnull([5],0) as May, isnull([6],0) as June, isnull([7],0) as July, isnull([8],0) as Aug, isnull([9],0) as Sept, isnull([10],0) as Oct, isnull([11],0) as Nov, isnull([12],0) as Dec

from(

SELECT T2.[ItemCode] as I, T2.[ItemName] as N, T2.[OnHand] as ST,sum(T1.[Quantity]) as T, month(T0.[DocDate])  as month FROM OINV T0  INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode WHERE year(T0.[DocDate]) = 2014 GROUP BY T2.[ItemCode], T2.[ItemName], T2.[OnHand],T0.[DocDate]

union all

SELECT T2.[ItemCode] as I, T2.[ItemName] as N, T2.[OnHand] as ST,-sum(T1.[Quantity]) as T, month(T0.[DocDate])  as month FROM ORIN T0  INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode WHERE year(T0.[DocDate]) = 2014 GROUP BY T2.[ItemCode], T2.[ItemName], T2.[OnHand],T0.[DocDate] )S

pivot

(sum(t) for month in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]))P

Thanks & Regards,

Nagarajan