Skip to Content
0
Former Member
Jan 17, 2011 at 01:02 PM

Document is repeated

29 Views

In below Query the report is repeating bcoz of U_BPVAT & TaxID11 Fields

SELECT distinct

T0.[DocDate],T0.[DocNum], T0.[NumAtCard],T0.[CardName],T5.[TaxId11] as 'TIN No of Vendor',

T0.[Address2] as "Shiping To Address with State", T4.[U_BPVAT] as 'TIN No of Shipping to Place',

T0.[DocTotal] as 'Total Invoice Amount',max(CASE WHEN T2.StaCode = 'CST' AND T2.[TaxSum]>0 THEN

T2.[TaxSum] end) AS "CST Tax Amt" , T3.[FormNo] as "C Form No",T0.[TaxDate]

FROM OPCH T0 INNER JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN PCH4 T2 ON T0.DocEntry = T2.DocEntry

INNER JOIN PCH12 T3 ON T0.DocEntry = T3.DocEntry

INNER JOIN CRD1 T4 ON T0.[CardCode] = T4.[CardCode]

INNER JOIN CRD7 T5 ON T4.[CardCode] = T5.[CardCode] and t5.[TaxId11]

is not null

WHERE (T1.[TaxCode] Like '%%_cst%%')and (((T0.[DocDate] >=[%0]) and

(T0.[DocDate] <=[%1])) or (('[%0]' = ' ') and ('[%1]' = ' ')))

and T4.AdresType = 'B'

--and T5.AddrType = 'B'

Group By T0.[DocNum], T0.[DocDate],T0.[CardCode],T0.[CardName],T0.[NumAtCard],T0.[Address2],

T0.[TaxDate],T0.[DocTotal],T1.[VatSum],T3.[TransCat],T3.[FormNo],T4.[U_BPVAT],T5.[TaxId11]