May 17, 2011 at 09:44 AM

# Help on Query

34 Views

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```