Salas Analysis Query including Credit Memo

[Former Member]

Hi Experts,

Can anyone help me out in preparing the query for Sales Analysis Report.

I need Itemwise Sales Analysis Report by inculding Credit Memos.

Below i tried but not including Credit Memo Quantity from Forum

Declare @Year Numeric

Set @Year='2014'

SELECT Distinct @Year,T0.ItemCode, T0.ItemName, T0.OnHand,

                        sum(Case DATENAME(month,T2.DocDate) when 'January' then T1.Quantity   else 0 end) as [January Qty],

                        sum(Case DATENAME(month,T2.DocDate) when 'February' then T1.Quantity   else 0 end) as [February Qty],

                        sum(Case DATENAME(month,T2.DocDate) when 'March' then T1.Quantity   else 0 end) as [March Qty],

                        sum(Case DATENAME(month,T2.DocDate) when 'April' then T1.Quantity   else 0 end) as [April Qty],

                        sum(Case DATENAME(month,T2.DocDate) when 'May' then T1.Quantity   else 0 end) as [May Qty],

                        sum(Case DATENAME(month,T2.DocDate) when 'June' then T1.Quantity   else 0 end) as [June Qty],

                        sum(Case DATENAME(month,T2.DocDate) when 'July' then T1.Quantity   else 0 end) as [July Qty],

                        sum(Case DATENAME(month,T2.DocDate) when 'August' then T1.Quantity   else 0 end) as [August Qty],

                        sum(Case DATENAME(month,T2.DocDate) when 'September' then T1.Quantity   else 0 end) as [September Qty],

                        sum(Case DATENAME(month,T2.DocDate) when 'October' then T1.Quantity   else 0 end) as [October Qty],

                        sum(Case DATENAME(month,T2.DocDate) when 'November' then T1.Quantity   else 0 end) as [November Qty],

                        sum(Case DATENAME(month,T2.DocDate) when 'December' then T1.Quantity   else 0 end) as [December Qty],

                        'Max Sales'

FROM dbo.OITM  T0

      INNER JOIN INV1 T1 ON T0.ItemCode = T1.ItemCode

      INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry

WHERE DATENAME(YEAR ,T1.DocDate )=@Year

GROUP BY T0.ItemCode, T0.ItemName, T0.OnHand

After Month December I need Highest Quantity sold among 12 months . Suppose in March 1000 sold and April 1200 Sold then 1200 should come in the Max Sales Column

Thanks

[kothandaraman_nagarajan]

Hi,

Try this query:

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

[Former Member]

Thanks Nagarajan,

I need Max Sales Column IN THE SAME QUERYalso..

Suppose

JAN - 100 QTY

FEB - 200 QTY

MAR - 100 QTY

APR - 500 QTY

MAY - 600 QTY

JUN - 700 QTY

JUL - 500 QTY

AUG - 500 QTY

SEP - 600 QTY

OCT - 0.0 QTY

NOV - 0.0 QTY

DEC - 0.0 QTY

THEN IN MAX SALES COLUMN I NEED 700QTY.

PLZ HELP ME OUT ON THIS.

THANKS

[Former Member]

Hi,

I tried below query, but it is not giving any result. Please let me know the solution.

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,

(SELECT

    CASE

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

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

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

        )THEN isnull([1],0)

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

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

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

        THEN isnull([2],0)

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

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

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

  )THEN isnull([3],0)

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

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

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

  )THEN isnull([4],0)

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

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

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

  )THEN isnull([5],0)

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

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

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

  )THEN isnull([6],0)

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

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

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

  )THEN isnull([7],0)

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

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

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

  )THEN isnull([8],0)

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

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

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

  )THEN isnull([9],0)

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

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

  --AND isnull([8],0) > isnull([9],0) AND isnull([9],0) > isnull([11],0)AND isnull([11],0) > isnull([12],0)

  )THEN isnull([10],0)

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

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

  --AND isnull([8],0) > isnull([9],0) AND isnull([9],0) > isnull([10],0)AND isnull([10],0) > isnull([12],0)

  )THEN isnull([11],0)

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

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

  --AND isnull([8],0) > isnull([9],0) AND isnull([9],0) > isnull([10],0)AND isnull([10],0) > isnull([11],0)

  )THEN isnull([12],0)                            

  END)

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  AND T2.ItemCode = 'AT13S' 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 AND T2.ItemCode = 'AT13S' 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

[kothandaraman_nagarajan]

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

[Former Member]

Thanks for reply.

Your query is very helpful for me.

And need to add purchase invoices column for the resultant items of below query. i.e,

After UoM column i need ReceivedInv No and ReceivedInv Qty for resultant items of below query.

Select [I] as Item#, [N] as ItemName, [GC] as [Group Name],

((SELECT 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)*2.5) as [Target Qty], (((SELECT 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)*2))/15 as [Per-Container Qty], UoM

from(

SELECT T2.[ItemCode] as I, T2.[ItemName] as N, T2.[OnHand] as ST, T2.[OnOrder] as TQTY, T2.SalUnitMsr as UoM,

(Select B.ItmsGrpNam from OITB B Where B.ItmsGrpCod = T2.ItmsGrpCod) as [GC],

(Select A.OnHand from OITW A Where A.ItemCode = T2.ItemCode and A.WhsCode = 'CSS') as [SS],

(T2.OnHand - (Select A.OnHand from OITW A Where A.ItemCode = T2.ItemCode and A.WhsCode = 'CSS')) as [WS],

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 DATEDIFF(MONTH, T0.[DocDate], GETDATE()) <= 3 

AND T2.[QryGroup1] = 'Y'

GROUP BY T2.[ItemCode], T2.[ItemName], T2.[OnHand],T2.[OnOrder] ,T0.[DocDate], T2.ItmsGrpCod, T2.[QryGroup1], T2.SalUnitMsr

union all

SELECT T2.[ItemCode] as I, T2.[ItemName] as N, T2.[OnHand] as ST, T2.[OnOrder] as TQTY, T2.SalUnitMsr as UoM,

(Select B.ItmsGrpNam from OITB B Where B.ItmsGrpCod = T2.ItmsGrpCod) as [GC],

(Select A.OnHand from OITW A Where A.ItemCode = T2.ItemCode and A.WhsCode = 'CSS') as [SS],

(T2.OnHand - (Select A.OnHand from OITW A Where A.ItemCode = T2.ItemCode and A.WhsCode = 'CSS')) as [WS],

-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 DATEDIFF(MONTH, T0.[DocDate], GETDATE()) <= 3 

AND T2.[QryGroup1] = 'Y'

GROUP BY T2.[ItemCode], T2.[ItemName], T2.[OnHand], T2.[OnOrder] ,T0.[DocDate], T2.ItmsGrpCod, T2.[QryGroup1], T2.SalUnitMsr)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],[TQTY],[WS],[SS], [GC],[UoM]

ORDER BY [Target Qty] Desc

Thanks

[kothandaraman_nagarajan]

Hi,

Better new query for AP invoice as same above. There is no link between AP invoice with AR invoice.

Thanks & Regards,

Nagarajan