Have following query that works except for that more than one record is rendered if table has multiple entries. Been looking at this but cannot figure out where the problem is.

The variables in questions are qty_gross and qty_net. the Quantiies are however correct as it i adding up (minus and plus) divided by 42.

Any help will be appreciated. Thank you.



Code:
SELECT		CONVERT(CHAR(1), bm.type) AS record_id,  
		COALESCE(sp.petroex_company_code, ' ') AS supplier_code,  
		COALESCE(CONVERT(CHAR(2), MONTH(bm.movement_date)), '  ') AS movement_month,
		COALESCE(CONVERT(CHAR(4), YEAR(bm.movement_date)), '    ') AS movement_year, 
		COALESCE(REPLACE(CONVERT(CHAR(10), bm.movement_date, 101), '/', ''), '          ') AS movement_date,
		COALESCE(bm.remarks, ' ') AS trans_descr,
		COALESCE(bm.reference_number, ' ') AS ticket_no,
           	COALESCE(tp.petroex_product_code, ' ') AS petroex_code, 
          	COALESCE(bp.tank_code, ' ') AS tank_code,                                                                                
		COALESCE(STR(100E * SUM(bp.gross_quantity / 42E) OVER (PARTITION BY bm.movement_number), 11, 0), '          ') AS qty_gross,
		COALESCE(STR(100E * SUM(bp.net_quantity / 42E) OVER (PARTITION BY bm.movement_number), 11, 0), '           ') AS qty_net,
           	COALESCE(bm.batch_voyage_number, ' ' ) mode_transport,
            	COALESCE(ca.scac_code, ' ') as scac,
            	coalesce(left(case when substring(petroex_fein,3,1) = '-' then stuff(petroex_fein,3,1,'') else petroex_fein end,9),'')
           
FROM		[VEMA-FACS1].FUELFACS.dbo.bulk_movement AS bm
INNER JOIN	[VEMA-FACS1].FUELFACS.dbo.bulk_product AS bp ON bp.bulk_movement_id = bm.bulk_movement_id
INNER JOIN	[VEMA-FACS1].FUELFACS.dbo.terminal_product AS tp ON tp.terminal_product_number = bp.terminal_product_number
INNER JOIN	[VEMA-FACS1].FUELFACS.dbo.supplier AS sp ON sp.supplier_number = bp.supplier_number
LEFT JOIN       [VEMA-FACS1].FUELFACS.dbo.carrier as ca ON ca.carrier_number = case when isnumeric(bm.vessel)=1 then convert(int,bm.vessel) end

WHERE		(@DateFrom IS NULL OR bm.movement_date >= @DateFrom) and
		    (@DateTo IS NULL OR bm.movement_date <= @DateTo) and
                tp.product_group_code <> 'ADD' and
		bm.type IN (1, 2) and
		bm.status > 4 and 
                bm.reference_number <> ''
order by        bm.movement_date, bm.reference_number
Code:
Result:

ticket_no	movement_no	petroex_code	qty_gross	qty_net
	
MC 01 497	2191		D87	  	883100	    	859700
MC 01 497	2191		D87	    	883100	       	859700
MC 01 497	2191		D87		831000	    	859700

They all have same movement number but listed 3 times


bulk_movement table:

movement_id	movement_no

54332		2191	 
54355		2191	 
54356		2191	 


bulk_product table:

movement_id	movement_no		gross_qty	net_qty

54332		TK_17	1		 8831		 8597
54355		TK_17	1		-8831		-8597
54356		TK_17	1		370902		361074