SQL Server - Records repeat for SSRS 2005 report

Asked By Sanjeet Koppikar on 25-Feb-13 04:32 PM
When I run a report for a purchase order, the report duplicates records for product codes.

For example the purchase order is: P000976, the report display the product code twice when it should only appear once. 45-5540 appears twice.

P000976            09-17-2012            15,040.00    15,040.00        0.00    
            45-5540       "Lordotic Cervical Spacer 10mm
Lordotic Cervical Spacer 10mm"                20    20        0    
            45-5540       "Lordotic Cervical Spacer 10mm
Lordotic Cervical Spacer 10mm"                20    20        0    

When I put the report's SQL in SQL server and run the sql by seeing where the code cause the additional product code it is this line within the SQL:

join all_product_codes_VW p on q.distpartno = p.distpartno

select q.specialrequirement
, q.distpartno
, q.toproduce
, q.prodbegindate
, q.distributor
, rc.report_category_name
, s.productperpo
, r.ebi_released
, w.ebi_in_WIP
, p.distproductname
, tp.typeprefixdetail
, tp.cost
, '1' as ReportTotals

from all_required_vw q
left join all_shipped_grafts_new_VW s on (q.distpartno = s.distpartno and q.specialrequirement = s.ponumber)
left join all_released_Grafts_VW r on q.distpartno = r.distpartno
left join all_in_WIP_VW w on q.distpartno = w.distpartno
join all_product_codes_VW p on q.distpartno = p.distpartno
join setup_tissue_prefix tp on q.typenumber = tp.typeprefix
join setup_report_category_1 rc on q.distributor = rc.report_category_id

where q.prodbegindate < @enddate
           and q.completed = '0'
            and rc.report_category_name  like  '%' + isnull(@tcustomer, '') + '%'

order by q.prodbegindate, p.distproductname

This is the SQL for the view for which the join creates the duplicate.

SELECT        COUNT_BIG(*) AS BIG, DistPartNo, DistProductName, Distributor, UMTBProductCode
FROM            dbo.Setup_Distributor_Prod
WHERE        (Distributor <> '7') OR
                         (Distributor IS NULL)
GROUP BY DistPartNo, DistProductName, Distributor, USSAProductCode