Skip to Content
0
J S
May 18, 2020 at 04:32 AM

Query not working

277 Views

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