locked
Query RRS feed

  • 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

    Wednesday, February 12, 2020 9:07 AM

All replies

  • 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

    Thursday, February 13, 2020 3:21 AM
  • User-797751191 posted

    Hi

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

        Jan                       Feb

    Qty    Amount      Qty     Amount 

    & son on

    Thanks

    Thursday, February 13, 2020 3:38 AM
  • 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

    Friday, February 14, 2020 1:39 AM