We are excited to announce that the ASP.NET Forums are moving to the new Microsoft Q&A experience. Learn more >

## 3 replies

Last post Feb 14, 2020 01:39 AM by Yang Shen

• jsshivalik

Member

140 Points

516 Posts

### Query

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

• Yang Shen

Contributor

3140 Points

983 Posts

### Re: Query

Feb 13, 2020 03:21 AM|Yang Shen|LINK

Hi jsshivalik,

#### 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

• jsshivalik

Member

140 Points

516 Posts

### Re: Query

Hi

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

Jan                       Feb

Qty    Amount      Qty     Amount

& son on

Thanks

• Yang Shen

Contributor

3140 Points

983 Posts

### Re: Query

Feb 14, 2020 01:39 AM|Yang Shen|LINK

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