Query

Question

User-797751191 posted

Hi

  I have below code and i want Amount also to be shown with Quantity

SELECT [a] as Cardcode, [B] as Cardname, [c] as Item#, [D] as Descr, 
[1] [jan],
[2] [feb],
[3] [mar],
[4] [apr],
[5] [may],
[6] [jun],
[7] [jul],
[8] [aug],
[9] [sep],
[10] [oct],
[11] [nov],
[12] [dec]

from

(Select T0.[CardCode] as A , T0.[CardName] as B, T1.[ItemCode] as C, T1.[Dscription] as D,T sum(T1.[Quantity]) as t, month(T0.[docDate]) as month
FROM OINV T0
INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
WHERE T1.[docDate] between '2019/04/01' and '2020/03/31' GROUP BY T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription],T0.[DocDate] ) S
Pivot
(sum(t) For Month IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P

Thanks

Answer 1

User-719153870 posted

Hi jsshivalik,

i want Amount also to be shown with Quantity

Can you explain what is "Amount also to be shown with Quantity"? Do you want to get the total value of your T1.Quantity?

If so, i built below demo based on your query and select the total value:

create table #OINV
(DocEntry int,[CardCode] varchar(50),[CardName] varchar(50),[docDate] date)

create table #INV1
(DocEntry int,[ItemCode] varchar(50),[Dscription] varchar(50),[Quantity] int,[docDate] date)

insert into #OINV values(1,'code1','name1','2019/05/12'),(2,'code1','name1','2019/06/12'),(3,'code2','name2','2019/10/12'),
(4,'code2','name2','2020/01/12'),(5,'code3','name3','2020/02/12'),(6,'code3','name3','2020/03/12')

insert into #INV1 values(1,'itemcode1','d1',23,'2019/05/13'),(2,'itemcode1','d1',41,'2019/06/13'),(3,'itemcode2','d2',12,'2019/10/13'),
(4,'itemcode2','d2',54,'2020/01/13'),(5,'itemcode3','d3',25,'2020/02/13'),(6,'itemcode3','d3',63,'2020/03/13')

select * from #OINV
select * from #INV1

SELECT [a] as Cardcode, [B] as Cardname, [c] as Item#, [D] as Descr, 
[1] [jan],
[2] [feb],
[3] [mar],
[4] [apr],
[5] [may],
[6] [jun],
[7] [jul],
[8] [aug],
[9] [sep],
[10] [oct],
[11] [nov],
[12] [dec],
(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)) as [Total]

from

(Select T0.[CardCode] as A , T0.[CardName] as B, T1.[ItemCode] as C, T1.[Dscription] as D, sum(T1.[Quantity]) as t, month(T0.[docDate]) as month
FROM #OINV T0
INNER JOIN #INV1 T1 ON T0.DocEntry = T1.DocEntry
WHERE T1.[docDate] between '2019/04/01' and '2020/03/31' GROUP BY T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription],T0.[DocDate] ) S
Pivot
(sum(t) For Month IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P

As you can see, the solution is quite simple, you just need to sum the columns value.

Or if i misunderstood the requirement, please provide a more detailed description about what you want, your table structures and sample data, and provide an expected output if possible.

Best Regard,

Yang Shen

Answer 2

User-797751191 posted

Hi

  I want to show both Quantity & Amount of that months. 

    Jan                       Feb

Qty    Amount      Qty     Amount 

& son on

Thanks

Answer 3

User-719153870 posted

Hi jsshivalik,

Not know where the "Amout" come from, i built below demo, please check if this is what you need:

create table #OINV
(DocEntry int,[CardCode] varchar(50),[CardName] varchar(50),[docDate] date)

create table #INV1
(DocEntry int,[ItemCode] varchar(50),[Dscription] varchar(50),[Quantity] int,[docDate] date)

insert into #OINV values(1,'code1','name1','2019/05/12'),(2,'code1','name1','2019/06/12'),(3,'code2','name2','2019/10/12'),
(4,'code2','name2','2020/01/12'),(5,'code3','name3','2020/02/12'),(6,'code3','name3','2020/03/12'),(7,'code3','name3','2020/02/13'),(8,'code3','name3','2020/03/13')

insert into #INV1 values(1,'itemcode1','d1',23,'2019/05/13'),(2,'itemcode1','d1',41,'2019/06/13'),(3,'itemcode2','d2',12,'2019/10/13'),
(4,'itemcode2','d2',54,'2020/01/13'),(5,'itemcode3','d3',25,'2020/02/13'),(6,'itemcode3','d3',63,'2020/03/13'),(7,'itemcode3','d3',35,'2020/02/14'),(8,'itemcode3','d3',11,'2020/03/14')

select * from #OINV
select * from #INV1

SELECT [a] as Cardcode, [B] as Cardname, [c] as Item#, [D] as Descr, 
[1] [jan],
[2] [feb],
[3] [mar],
[4] [apr],
[5] [may],
[6] [jun],
[7] [jul],
[8] [aug],
[9] [sep],
[10] [oct],
[11] [nov],
[12] [dec]
from

(Select T0.[CardCode] as A , T0.[CardName] as B, T1.[ItemCode] as C, T1.[Dscription] as D, CONVERT(varchar(50), sum(T1.[Quantity]))+'   '+ CONVERT(varchar(50), COUNT(T0.CardName)) as t, month(T0.[docDate]) as [month]
FROM #OINV T0
INNER JOIN #INV1 T1 ON T0.DocEntry = T1.DocEntry
WHERE T1.[docDate] between '2019/04/01' and '2020/03/31' GROUP BY T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription],month(T0.[docDate])) S
Pivot
(max(t) For Month IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P

Best Regard,

Yang Shen