on 01-13-2015 10:11 AM
Hi All.
Here I have Submitted a Query For Month Wise GRPO QTY from Vendor.I need Month Wise PO qty Also Along with GRPO qty.
Kindly Help For This?
SELECT CardName,[ItemCode],[ItemName], [4] as Apr, [5] as May, [6] as Jun, [7] as Jul, [8] as Aug, [9] as Sep, [10] as Oct, [11] as Nov, [12] as Dec, [1] as Jan, [2] as Feb, [3] as Mar
from
( select CardName,T0.[ItemCode],T2.[ItemName],sum(T0.[Quantity]) as Quantity, month(T1.[DocDate]) as Month FROM PDN1 T0 INNER JOIN OPDN T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T0.ItemCode = T2.ItemCode and (T1.[DocDate] between '2014/04/01' and '2015/03/31') WHERE (T0.ItemCode LIKE '%%[%0]%%' OR '[%0]' = ' ')
GROUP BY CardName,T0.[ItemCode],T2.[ItemName],T1.DocDate)S
Pivot
(sum(Quantity) FOR Month IN ([4],[5],[6],[7],[8],[9],[10],[11],[12],[1],[2],[3])) P
Hi,
Try:
SELECT CardName,[ItemCode],[ItemName], [4] as 'Apr-PO', [5] as 'May-PO', [6] as 'Jun-PO', [7] as 'Jul-PO', [8] as 'Aug-PO', [9] as 'Sep-PO', [10] as 'Oct-PO', [11] as 'Nov-PO', [12] as 'Dec-PO', [1] as 'Jan-PO', [2] as 'Feb-PO', [3] as 'Mar-PO', [4] as 'Apr-GRPO', [5] as 'May-GRPO', [6] as 'Jun-GRPO', [7] as 'Jul-GRPO', [8] as 'Aug-GRPO', [9] as 'Sep-GRPO', [10] as 'Oct-GRPO', [11] as 'Nov-GRPO', [12] as 'Dec-GRPO', [1] as 'Jan-GRPO', [2] as 'Feb-GRPO', [3] as 'Mar-GRPO'
from
( select CardName,T0.[ItemCode],T2.[ItemName],sum(T0.[Quantity]) as Quantity, month(T1.[DocDate]) as Month FROM PDN1 T0 INNER JOIN OPDN T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T0.ItemCode = T2.ItemCode and (T1.[DocDate] between '2014/04/01' and '2015/03/31') WHERE (T0.ItemCode LIKE '%%[%0]%%' OR '[%0]' = ' ')
GROUP BY CardName,T0.[ItemCode],T2.[ItemName],T1.DocDate)S
Pivot
(sum(Quantity) FOR Month IN ([4],[5],[6],[7],[8],[9],[10],[11],[12],[1],[2],[3])) P
union all
SELECT CardName,[ItemCode],[ItemName], [4] as 'Apr-PO', [5] as 'May-PO', [6] as 'Jun-PO', [7] as 'Jul-PO', [8] as 'Aug-PO', [9] as 'Sep-PO', [10] as 'Oct-PO', [11] as 'Nov-PO', [12] as 'Dec-PO', [1] as 'Jan-PO', [2] as 'Feb-PO', [3] as 'Mar-PO',[4] as 'Apr-GRPO', [5] as 'May-GRPO', [6] as 'Jun-GRPO', [7] as 'Jul-GRPO', [8] as 'Aug-GRPO', [9] as 'Sep-GRPO', [10] as 'Oct-GRPO', [11] as 'Nov-GRPO', [12] as 'Dec-GRPO', [1] as 'Jan-GRPO', [2] as 'Feb-GRPO', [3] as 'Mar-GRPO'
from
( select CardName,T0.[ItemCode],T2.[ItemName],sum(T0.[Quantity]) as Quantity, month(T1.[DocDate]) as Month FROM POR1 T0 INNER JOIN OPDN T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T0.ItemCode = T2.ItemCode and (T1.[DocDate] between '2014/04/01' and '2015/03/31') WHERE (T0.ItemCode LIKE '%%[%0]%%' OR '[%0]' = ' ')
GROUP BY CardName,T0.[ItemCode],T2.[ItemName],T1.DocDate)S
Pivot
(sum(Quantity) FOR Month IN ([4],[5],[6],[7],[8],[9],[10],[11],[12],[1],[2],[3])) P
Thanks & Regards,
Nagarajan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
103 | |
12 | |
11 | |
6 | |
5 | |
4 | |
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.