Hi
create table Tmp0(DocEntry int,DocNum varchar(10),DocDate date,DocStatus varchar(1),NumAtCard varchar(100),
CardName varchar(100), GstRegNo varchar(50),Location varchar(100), Quantity decimal(18,2), BaseAmt decimal(18,2), TaxPercent decimal(18,2),
Cgst decimal(18,2),Sgst decimal(18,2),Igst decimal(18,2),UTGst decimal(18,2),Others decimal(18,2))
Select * into #Tmp0 from (SELECT M.DocEntry,M.DocNum, M.DocDate,M.DocStatus,M.NumAtCard,
M.CardName,A.GSTRegnNo,P.Location,
Sum(L.Quantity) as 'Quantity',
Sum(L.LineTotal) as BaseAmt,Max(L.VatPrcnt)'TaxPercent',
(SELECT Isnull(Sum(TaxSum),0) FROM PCH4 where statype=-100 and DocEntry=M.DocEntry and StcCode = L.TaxCode) as 'CGST',
(SELECT Isnull(Sum(TaxSum),0) FROM PCH4 where statype=-110 and DocEntry=M.DocEntry and StcCode = L.TaxCode) as 'SGST',
(SELECT Isnull(Sum(TaxSum),0) FROM PCH4 where statype=-120 and DocEntry=M.DocEntry and StcCode = L.TaxCode) as 'IGST',
(SELECT Isnull(Sum(TaxSum),0) FROM PCH4 where statype= -150 and DocEntry=M.DocEntry and StcCode = L.TaxCode) as 'UTGST',
Isnull(Sum(M.WTSum+M.RoundDif+M.TotalExpns),0) as Others
FROM OPCH M Inner JOIN PCH1 L on L.DocEntry=M.DocEntry
LEFT JOIN CRD1 A on M.CardCode=A.CardCode and M.PayToCode=A.Address and A.AdresType='S'
INNER JOIN OLCT P on L.[LocCode]=P.[Code]
WHERE (M.DocDate >= '[%0]' AND M.DocDate <= '[%1]' AND M.DocType='I'
AND P.Location in ('Ludhiana-Unit I','Ludhiana-Unit II'))
AND M.[CANCELED] Not in ('Y','C') and M.InvntSttus <> 'C'
GROUP BY
M.DocEntry,M.DocNum,M.DocDate,M.DocStatus,M.NumAtCard,M.CardName,A.GSTRegnNo,P.[Location],L.TaxCode )a
ORDER BY
a.DocEntry,a.DocNum,a.DocDate,a.DocStatus,a.NumAtCard,a.CardName,a.GSTRegnNo,a.[Location]
SELECT *
FROM
(SELECT
(ROW_NUMBER() OVER(PARTITION BY DocEntry ORDER BY DocEntry)) [RR], *
FROM #Tmp0) SUBQUERY1
drop table #Tmp0
Thanks