Using SUM renders too many records
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