Help on Query

[former_member205766]

Hi Xperts

This Query is working fine. But my User creates Two Invoices in Series - Primary 11 (Service) Bill It not came in the query. Can Any body help me regarding this issue.

declare @todate as datetime
declare @enddate as datetime 
SELECT @todate= /*min(T0.DocDate) FROM OINM T0 WHERE T0.DocDate=*/ '[%0]'
SELECT @enddate= /*max(T0.DocDate) FROM OINM T0 WHERE T0.DocDate=*/'[%1]'
select DISTINCT a.Docnum as "SAP Invoice No" ,a.docdate AS " SAP Invoice Date" ,a.Numatcard AS "Vendor Bill No" ,a.U_Date AS " Vendor Bill Date" ,a.cardname as "Name of the Supplier" ,(Select max (T2.TaxId2) from CRD7 T2 where T2.CardCode = a.CardCode) [Tin No], (SELECT SUM(Quantity) FROM PCH1 where docentry =a.docentry)as "Quantity" ,d.[TaxCode],
(select sum(LineTotal) from PCH1 where docentry = a.docentry)as "Basic Value" ,(Select sum(taxsum) FROM PCH4 where docentry = a.docentry and statype=-90) as "BED" ,
(Select SUM(taxsum) FROM PCH4 where docentry = a.docentry and statype=-60) as "Cess" ,(Select SUM(taxsum) FROM PCH4 where docentry = a.docentry and statype=7) as "HeCess" ,(Select SUM(taxsum) FROM PCH4 where docentry = a.docentry and statype=1) as "VAT" ,
(Select SUM(taxsum) FROM PCH4 where docentry = a.docentry and statype=4) as "CST" ,
 A.VatSum as "Total Tax" ,(select sum(linetotal) from PCH3 where docentry = a.docentry) as 'Freight' , 
A.DocTotal as "Net Value" , A.[U_Category] as "Category", A.[U_CC] as "Commodity No." 
FROM OPCH A left OUTER JOIN PCH3 B ON A.DOCENTRY = B.DOCENTRY left OUTER JOIN PCH4 C ON A.DOCENTRY = C.DOCENTRY left outer join PCH3 h on A.DocEntry = h.DocEntry left outer join PCH1 d on a.docentry = d.docentry left outer join crd7 e on a.cardcode = e.cardcode, oitm g left outer join ochp f on f.absentry= g.chapterid where a.Docdate >=@todate and a.Docdate <=@enddate and a.DocEntry <> '8187' and a.vatsum >= '1' and g.itemcode=d.itemcode ORDER BY A.DOCNUM

Thanks in Advance

Balaji Sampath

[former_member206488]

Hi Balaji,

Try this:

declare @todate as datetime
declare @enddate as datetime 
SELECT @todate= /*min(T0.DocDate) FROM OINM T0 WHERE T0.DocDate=*/ '[%0]'
SELECT @enddate= /*max(T0.DocDate) FROM OINM T0 WHERE T0.DocDate=*/'[%1]'
select DISTINCT a.Docnum as "SAP Invoice No" ,a.docdate AS " SAP Invoice Date" ,a.Numatcard AS "Vendor Bill No",a.U_Date AS " Vendor Bill Date" ,
        a.cardname as "Name of the Supplier" ,
        (Select max (T2.TaxId2) from CRD7 T2 where T2.CardCode = a.CardCode) [Tin No],
        (SELECT SUM(Quantity) FROM PCH1 where docentry =a.docentry)as "Quantity" ,d.[TaxCode],
        (select sum(LineTotal) from PCH1 where docentry = a.docentry)as "Basic Value" ,
        (Select sum(taxsum) FROM PCH4 where docentry = a.docentry and statype=-90) as "BED" ,
		(Select SUM(taxsum) FROM PCH4 where docentry = a.docentry and statype=-60) as "Cess" ,
		(Select SUM(taxsum) FROM PCH4 where docentry = a.docentry and statype=7) as "HeCess" ,
		(Select SUM(taxsum) FROM PCH4 where docentry = a.docentry and statype=1) as "VAT" ,
		(Select SUM(taxsum) FROM PCH4 where docentry = a.docentry and statype=4) as "CST" ,
         A.VatSum as "Total Tax" ,
        (select sum(linetotal) from PCH3 where docentry = a.docentry) as 'Freight' ,
        A.DocTotal as "Net Value", A.[U_Category] as "Category", A.[U_CC] as "Commodity No." 
        
FROM OPCH A 
     left OUTER JOIN PCH3 B ON A.DOCENTRY = B.DOCENTRY 
     left OUTER JOIN PCH4 C ON A.DOCENTRY = C.DOCENTRY 
     left outer join PCH3 h on A.DocEntry = h.DocEntry 
     left outer join PCH1 d on a.docentry = d.docentry 
     left outer join crd7 e on a.cardcode = e.cardcode
     LEFT OUTER Join OITM T on D.ItemCode =t.ItemCode 
     left outer join ochp f on f.absentry= t.chapterid 

where a.Docdate >=@todate and a.Docdate <=@enddate and a.DocEntry <> '8187' and a.vatsum >= '1' and t.itemcode=d.itemcode ORDER BY A.DOCNUM

Thanks,

neetu

[former_member205766]

Hi Neetu

This query is not working.

thanks

with regards

Balaji