locked
Query RRS feed

  • Question

  • User-797751191 posted

    Hi

      I have below data 

    1	30	05/04/19	C	7	05/04/19	ABC 	200	27400
    2	31	05/04/19	C	12	05/04/19	XYZ	193	28950
    3	32	05/04/19	C	14	05/04/19	YYY     2000	26620
    3	32	05/04/19	C	14	05/04/19	YYY     4000    10000
    
    I want that it should be displayed like below using Rollup or some other way
    
    1	30	05/04/19	C	7	05/04/19	ABC 	200	27400
    2	31	05/04/19	C	12	05/04/19	XYZ	193	28950
    3	32	05/04/19	C	14	05/04/19	YYY     2000	26620
                                                                            4000    10000
    

    Thanks

    Thursday, May 14, 2020 5:50 AM

Answers

  • User-719153870 posted

    Hi jsshivalik,

    It's hard to say where we need to change since this code is not complete.

    Assume the result of this query is the 1st dataset you provided at the beginning, then i will suggest you to select it(the result of this query) into a temp table, please refer to SQL SELECT INTO Examples for more information.

    In your case, it should be like below(didn't test but it shall work):

    select * into #AAA from (SELECT M.DocNum AS 'AP Inv. #', M.DocDate as 'Date',M.DocStatus,M.NumAtCard as 'Bill No. & Dt.', M.TaxDate as 'Document Date',
    M.CardName as 'Vendor Name',A.GSTRegnNo'GST No',P.[Location] 'Location',
    Sum(L.Quantity) as 'Quantity',
    Sum(L.LineTotal) as 'Base Amt.(Rs.)',Max(L.VatPrcnt)'Tax Code'
    
    FROM Test0 M LEFT OUTER JOIN Test1 L on L.DocEntry=M.DocEntry
    LEFT JOIN Test2 A on M.CardCode=A.CardCode 
    INNER JOIN Test3 P on L.[LocCode]=P.
     
    
    WHERE (M.DocDate >= '2019/04/05' AND M.DocDate <= '2019/04/05'  )
    
    GROUP BY
    M.DocEntry,M.DocNum,M.DocDate,M.DocStatus,M.NumAtCard,M.TaxDate,M.CardName,A.GSTRegnNo,P.[Location],L.TaxCode)a

    After that, use the query i provided and change the table name and clomun names in it:

    SELECT
    	CASE WHEN RR = 1 THEN id ELSE null END id,
    	CASE WHEN RR = 1 THEN age ELSE null END age,
    	CASE WHEN RR = 1 THEN sdate ELSE null END sdate,
    	CASE WHEN RR = 1 THEN itype ELSE null END itype,
    	CASE WHEN RR = 1 THEN anum ELSE null END anum,
    	CASE WHEN RR = 1 THEN edate ELSE null END edate,
    	CASE WHEN RR = 1 THEN name ELSE null END name,
    	num,
    	snum
    FROM
    	(SELECT
    	(ROW_NUMBER() OVER(PARTITION BY id ORDER BY id)) [RR], *
    	FROM #AAA) SUBQUERY1

    PS. why not use where M.DocDate = '2019/04/05' instead of WHERE (M.DocDate >= '2019/04/05' AND M.DocDate <= '2019/04/05' ).

    Best Regard,

    Yang Shen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 15, 2020 1:47 AM

All replies

  • User-719153870 posted

    Hi jsshivalik,

    AFAIK, the ROLLUP clause is not for this kind of requirement.

    Please refer to below demo:

    create table AAA(id int,age int,sdate date,itype varchar(10),anum int,edate date,name varchar(20),num int,snum int)
    insert into AAA values(1,30,'2019-05-04','C',7,'2019-05-04','ABC',200,27400),
    (2,31,'2019-05-04','C',12,'2019-05-04','XYZ',193,28950),(3,32,'2019-05-04','C',14,'2019-05-04','YYY',2000,26620),
    (3,32,'2019-05-04','C',14,'2019-05-04','YYY',4000,10000)
    
    select * from AAA
    SELECT
    	CASE WHEN RR = 1 THEN id ELSE null END id,
    	CASE WHEN RR = 1 THEN age ELSE null END age,
    	CASE WHEN RR = 1 THEN sdate ELSE null END sdate,
    	CASE WHEN RR = 1 THEN itype ELSE null END itype,
    	CASE WHEN RR = 1 THEN anum ELSE null END anum,
    	CASE WHEN RR = 1 THEN edate ELSE null END edate,
    	CASE WHEN RR = 1 THEN name ELSE null END name,
    	num,
    	snum
    FROM
    	(SELECT
    	(ROW_NUMBER() OVER(PARTITION BY id ORDER BY id)) [RR], *
    	FROM AAA) SUBQUERY1
    drop table AAA
    

    Below is the result of this demo:

    Here's a similar question from MSDN.

    In addition, if you are interested about PARTITION BY, please check SQL PARTITION BY Clause overview.

    Best Regard,

    Yang Shen

    Thursday, May 14, 2020 7:57 AM
  • User-797751191 posted

    Hi Yand

       Wht changes will be reqd to be done in the below code

    SELECT M.DocNum AS 'AP Inv. #', M.DocDate as 'Date',M.DocStatus,M.NumAtCard as 'Bill No. & Dt.', M.TaxDate as 'Document Date',
    M.CardName as 'Vendor Name',A.GSTRegnNo'GST No',P.[Location] 'Location',
    Sum(L.Quantity) as 'Quantity',
    Sum(L.LineTotal) as 'Base Amt.(Rs.)',Max(L.VatPrcnt)'Tax Code'
    
    FROM Test0 M LEFT OUTER JOIN Test1 L on L.DocEntry=M.DocEntry
    LEFT JOIN Test2 A on M.CardCode=A.CardCode 
    INNER JOIN Test3 P on L.[LocCode]=P.
     
    
    WHERE (M.DocDate >= '2019/04/05' AND M.DocDate <= '2019/04/05'  )
    
    GROUP BY
    M.DocEntry,M.DocNum,M.DocDate,M.DocStatus,M.NumAtCard,M.TaxDate,M.CardName,A.GSTRegnNo,P.[Location],L.TaxCode
    

    Thanks

    Thursday, May 14, 2020 5:03 PM
  • User-719153870 posted

    Hi jsshivalik,

    It's hard to say where we need to change since this code is not complete.

    Assume the result of this query is the 1st dataset you provided at the beginning, then i will suggest you to select it(the result of this query) into a temp table, please refer to SQL SELECT INTO Examples for more information.

    In your case, it should be like below(didn't test but it shall work):

    select * into #AAA from (SELECT M.DocNum AS 'AP Inv. #', M.DocDate as 'Date',M.DocStatus,M.NumAtCard as 'Bill No. & Dt.', M.TaxDate as 'Document Date',
    M.CardName as 'Vendor Name',A.GSTRegnNo'GST No',P.[Location] 'Location',
    Sum(L.Quantity) as 'Quantity',
    Sum(L.LineTotal) as 'Base Amt.(Rs.)',Max(L.VatPrcnt)'Tax Code'
    
    FROM Test0 M LEFT OUTER JOIN Test1 L on L.DocEntry=M.DocEntry
    LEFT JOIN Test2 A on M.CardCode=A.CardCode 
    INNER JOIN Test3 P on L.[LocCode]=P.
     
    
    WHERE (M.DocDate >= '2019/04/05' AND M.DocDate <= '2019/04/05'  )
    
    GROUP BY
    M.DocEntry,M.DocNum,M.DocDate,M.DocStatus,M.NumAtCard,M.TaxDate,M.CardName,A.GSTRegnNo,P.[Location],L.TaxCode)a

    After that, use the query i provided and change the table name and clomun names in it:

    SELECT
    	CASE WHEN RR = 1 THEN id ELSE null END id,
    	CASE WHEN RR = 1 THEN age ELSE null END age,
    	CASE WHEN RR = 1 THEN sdate ELSE null END sdate,
    	CASE WHEN RR = 1 THEN itype ELSE null END itype,
    	CASE WHEN RR = 1 THEN anum ELSE null END anum,
    	CASE WHEN RR = 1 THEN edate ELSE null END edate,
    	CASE WHEN RR = 1 THEN name ELSE null END name,
    	num,
    	snum
    FROM
    	(SELECT
    	(ROW_NUMBER() OVER(PARTITION BY id ORDER BY id)) [RR], *
    	FROM #AAA) SUBQUERY1

    PS. why not use where M.DocDate = '2019/04/05' instead of WHERE (M.DocDate >= '2019/04/05' AND M.DocDate <= '2019/04/05' ).

    Best Regard,

    Yang Shen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 15, 2020 1:47 AM
  • User-797751191 posted

    Hi Yang

      Why we need to write a at the end

    M.DocEntry,M.DocNum,M.DocDate,M.DocStatus,M.NumAtCard,M.TaxDate,M.CardName,A.GSTRegnNo,P.[Location],L.TaxCode)a

    Thanks
    Friday, May 15, 2020 9:01 AM