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