April 19, 2011 at 7:22 am
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.
SELECTCONVERT(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
Result:
ticket_nomovement_nopetroex_codeqty_grossqty_net
MC 01 4972191D87 883100 859700
MC 01 4972191D87 883100 859700
MC 01 4972191D87831000 859700
They all have same movement number but listed 3 times
bulk_movement table:
movement_idmovement_no
543322191
543552191
543562191
bulk_product table:
movement_idgross_qtynet_qty
54332 8831 8597
54355-8831-8597
54356370902361074
April 19, 2011 at 7:37 am
Comment out the joins to the tables apart from bm and bp:
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
Comment out any other reference to these tables in the output list.
Run the query - if your supplied information is correct, then the "dupes" should be eliminated.
Introduce (uncomment) the other tables one at a time, running the query and checking the output with each one.
This will identify which table has multiple rows per single row in BM / BP.
A common way to solve this problem having identified which table is screwing up the cardinality is to aggregate it in a derived table. However it's not the only way and may not give correct results. Describe carefully what you find when you perform these steps.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 19, 2011 at 8:00 am
I commented out all joins except for
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
and result is (same as before)
201103302011 MC 01 497883100 859700
201103302011 MC 01 497883100 859700
201103302011 MC 01 497883100 859700
Now, if I do this
sum(bp.gross_quantity / 42.0) as qty_gross,
sum(bp.net_quantity / 42.0) as net_qty
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
group by bm.reference_number
I get just one record:
MC 01 4978831.0000008597.000000
Not sure what is going on.
April 19, 2011 at 8:43 am
snufse1 (4/19/2011)
I commented out all joins except for
FROM[VEMA-FACS1].FUELFACS.dbo.bulk_movement AS bmINNER JOIN[VEMA-FACS1].FUELFACS.dbo.bulk_product AS bp ON bp.bulk_movement_id = bm.bulk_movement_id
and result is (same as before)
201103302011 MC 01 497883100 859700201103302011 MC 01 497883100 859700
201103302011 MC 01 497883100 859700
Now, if I do this
sum(bp.gross_quantity / 42.0) as qty_gross,sum(bp.net_quantity / 42.0) as net_qty
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
group by bm.reference_number
I get just one record:
MC 01 4978831.0000008597.000000Not sure what is going on.
Then take a closer look at the data. Add a WHERE clause to the following to return some rows which fall into the category you're interested in:
SELECT bm.*, '#', bp.*
FROM [VEMA-FACS1].FUELFACS.dbo.bulk_movement AS bm
INNER JOIN [VEMA-FACS1].FUELFACS.dbo.bulk_product AS bp
ON bp.bulk_movement_id
WHERE bm.bulk_movement_id = [a useful value]
The # in the SELECT list is there as a visual aid to show where columns from bm end and columns from bp begin.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 19, 2011 at 10:55 am
Did like this (these are tho one's I'm having issue with)
SELECT bm.*, '#', bp.*
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
WHERE bm.bulk_movement_id = '54332' or bm.bulk_movement_id = '54355' or bm.bulk_movement_id = '54356'
Result:
543322191MC 01 497 #54332 8831 8597
543552191MC 01 497 #54355 -8831 -8597
543562191MC 01 497 #54356 370902 361074
This looks fine and there movement_id exists in both tables for movement_number 2191.
The strange this is that in my original query, all the lines have same quantity of 8831, which is 8831 + (-8831) + 370902 / 42.0 = 8831
April 20, 2011 at 1:18 am
Hi
Is this issue resolved now?
If not, there are a couple of lines of enquiry to follow;
Check if bp.movement_number = 2191 appears in rows other than those where bm.bulk_movement_id IN ('54332','54355','54356'). Here's the query:
SELECT bm.*, '#', bp.*
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
WHERE bm.bulk_movement_id IN ('54332','54355','54356')
OR (bm.movement_number = 2191 or bp.movement_number = 2191)
Check which of the two columns bm.reference_number and bm.movement_number should be used for the aggregation. If you're not sure, you could probably figure it out from the data.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 20, 2011 at 6:17 am
Hello,
Not been solved and I am in trouble still.
Table: bulk_movement
Field: movement_id and movment_number
Table: bulk_product
Field: movement_id
bulk_movement table:
movement_id movement_no
54332 2191
54355 2191
54356 2191
bulk_product table:
movement_id gross_qty net_qty
54332 8831 8597
54355 -8831 -8597
54356 370902 361074
Summary:
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 2191).
Each one of these have their unique movement_id (54332, 54355, 54356) that I need to join on bulk_product table on (bp.movement_id) and get the quantity (for each one of the 3 records).
The quantity needs to be added together and shown as one movement_id with total quantity.
With respect your latest query suggestion, the table bulk_product does not have the field movement_number, just the movement_id
SELECT bm.*, '#', bp.*
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
WHERE bm.bulk_movement_id IN ('54332','54355','54356')
OR (bm.movement_number = 2191)
Hope I make myself clear. Thank you. Sorry, I am not an expert in sp.
April 20, 2011 at 7:53 am
Going back to your earlier post,
sum(bp.gross_quantity / 42.0) as qty_gross,sum(bp.net_quantity / 42.0) as net_qty
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
group by bm.reference_number
If you group by bm.movement_number instead of bm.reference_number, do you get the result you're looking for? If you do, then it would appear that you should be using GROUP BY in your query rather than OVER().
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply