Results 1 to 3 of 3

Thread: Using SUM renders too many records

  1. #1
    Join Date
    Mar 2009
    Posts
    32

    Question 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

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    The result set you have listed does not seem complete based on the query you have. Do you have duplicate rows without SUM OVER PARTITION statement?
    Last edited by skhanal; 04-20-2011 at 05:44 AM.

  3. #3
    Join Date
    Mar 2009
    Posts
    32
    Yes, I just listed result as it pertains to my issue.

    Table: bulk_movement
    Field: movement_id and movment_number

    Table: bulk_product
    Field: movement_id

    I need to read all records with a specific movement_number from bulk_movement table (I this case I have 3 of them with same movement_number). Each one of these have a unique movement_id that I need to join on bulk_product table and get the quantity (3 records). The quantity needs to be added together and shown as one movement_id and total quantity.

    Hope I make myself clear. Thank you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •