Sql View / Query help

[firmusgrp]

Hi all and thanks for the assistance ...

I'm trying to (logically) combine to tables for a total inventory count, then subtract items from a third table.
I UNION two tables as such:

/*  Inventory Purchased Afloat    */
	SELECT		unitQty AS qtyIn, POLineID
	FROM         	dbo.tblPOLine AS po
	WHERE     (locationID = 6)

UNION
/*   Inventory Purchased At Orgin Shipped     */
	SELECT     	tr.qty AS qtyIn, tr.POLineID
	FROM         	dbo.tblTRLine AS tr LEFT OUTER JOIN
                      	dbo.tblTRLedger AS trL ON tr.TRLedgerID = trL.TRLedgerID
	WHERE     (trL.locationID = 6)

that code works well and I get the proper totals per POLineID.

I have the table that I should use for subtracting as

/*   All Landed Inventory     */
	SELECT     	tr.POLineID, SUM(tr.qty) AS qtyOut
	FROM         	dbo.tblTRLine AS tr LEFT OUTER JOIN
                      	dbo.tblTRLedger AS trL 
			ON tr.TRLedgerID = trL.TRLedgerID
	WHERE     	(trL.locationID = 3) OR (trL.locationID = 4)
	GROUP BY 	tr.POLineID

this always works on its own properly grouping by POLineID

Now I want to take the qtyIN - qtyOut for a total result. What I'm doing (full code) is:

/********************************/
/******* Inventory Afloat *******/
/********************************/
/*                              */
/*                              */
/* Inventory Purchased Afloat   */
/********************************/

WITH InvAtSea AS
(
/*   Inventory Purchased Afloat    */
	SELECT		unitQty AS qtyIn, POLineID
	FROM         	dbo.tblPOLine AS po
	WHERE     (locationID = 6)

UNION
/*   Inventory Purchased At Orgin Shipped     */
	SELECT     	tr.qty AS qtyIn, tr.POLineID
	FROM         	dbo.tblTRLine AS tr LEFT OUTER JOIN
                      	dbo.tblTRLedger AS trL ON tr.TRLedgerID = trL.TRLedgerID
	WHERE     (trL.locationID = 6)
),
/*   All Landed Inventory     */
InvLanded AS
(
	SELECT     	tr.POLineID, SUM(tr.qty) AS qtyOut
	FROM         	dbo.tblTRLine AS tr LEFT OUTER JOIN
                      	dbo.tblTRLedger AS trL 
			ON tr.TRLedgerID = trL.TRLedgerID
	WHERE     	(trL.locationID = 3) OR (trL.locationID = 4)
	GROUP BY 	tr.POLineID
)
SELECT 		ino.qtyIn - ISNULL(out.qtyOut, 0) as qtyAtSea
FROM		InvAtSea AS ino
		LEFT OUTER JOIN
		InvLanded AS out
		ON ino.POLineID = out.POLineID

but the results don't work.

It is applying the subtraction multiple times, not on the aggregate results.
Can you tell me what I'm missing? I didn't want to just through in a DISTINCT without knowing.

Many thanks,
Mark

 

[markros]

Your code seems to be perfectly OK (I will just change
trL.LocationID IN (3,4) -- to simplify.

I suggest to try using temp tables instead of CTE. e.g.

select ..
into #InvAtSea
...

select ...
into #InvLanded

....

---------------
See, if you get different results.

Also, what is your SQL Server version and are you on the latest SP for your version?

PluralSight Learning Library

 

[firmusgrp]

@markros

Thanks for your thoughts. If you'll see by the larger code I am using CTE and getting this odd result.

 

[firmusgrp]

@markos

Please ignore my last post, I misread yours and apparently we can't go back an edit.

 

[firmusgrp]

Changing the CTE end Select to Distinct as ...

SELECT DISTINCT	ino.qtyIn - ISNULL(out.qtyOut, 0) as qtyAtSea
FROM   		InvAtSea AS ino
        	LEFT OUTER JOIN
        	InvLanded AS out
        	ON ino.POLineID = out.POLineID

produces the correct results, I just am not sure why. Can anyone help me understand? I would hate to surpress things I'm not supposed to here.

Many thanks,
Mark

 

[markros]

In your In query, do you have repeated POLine (but different qty)?

May be you just need
WITH InvAtSea AS
(
/* Inventory Purchased Afloat */
SELECT unitQty AS qtyIn, POLineID
FROM dbo.tblPOLine AS po
WHERE (locationID = 6)

UNION
/* Inventory Purchased At Orgin Shipped */
SELECT tr.qty AS qtyIn, tr.POLineID
FROM dbo.tblTRLine AS tr LEFT OUTER JOIN
dbo.tblTRLedger AS trL ON tr.TRLedgerID = trL.TRLedgerID
WHERE (trL.locationID = 6)),

InvIn as (select sum(qtyIn) as QtyIn, PoLineID from InvAtSea
GROUP BY PoLineID)

This way it will work for sure. Also, perhaps you needed UNION ALL and not just UNION if you wanted to combine PO from both tables and qty from both tables.


PluralSight Learning Library

 

[firmusgrp]

@markos

Thanks again for your efforts.

Specific to your questions, on the UNION no POLineID exists in both tables so so there is no SUM needed. Each quantity per po.POLineID is a total sum as is the same with tr.POLineID. That's what made the UNION idea so clean and neat.

It seemed to subtract per number of lines in the UNION...very odd. I'll try these other variants as well as see what it produces.

 

[markros]

Use UNION ALL then - it performs quicker as it doesn't need to make sure the result is unique (since you know you have unique IDs, no need to omit ALL keyword).

Now, the problem is then indeed mysterious. I don't see a reason for wrong results unless there are some dups or you're looking in the wrong database.

PluralSight Learning Library

 

[firmusgrp]

Thanks for the tip, appreciated.

Agreed on the oddity. Definitely correct database, I emptied all data to test the query when I noticed the strangeness. In fact it is a pretty complex one and I removed all the other fields so as to get to the root of the problem.

I only have 2 result lines in the UNION and 1 line of subtraction. Very frustrating.

I see if I can script a sample for you to look at if you like

 

[markros]

Sure, if you can post a script showing the same problem, I will appreciate.

PluralSight Learning Library

 

[JonFer]

If no POLineID exists in both the TR and PO tables, why are you unioning the two tables before trying to subtract based only on the TR POLineID (from InvLanded). You won't get any PO data since no TR POLineID will match and TR is your "outer" table.


John

http://www.interautomateconsulting.com

 

[firmusgrp]

@JonFer

Hi John,
Thanks for having a look and I should clarify my remarks. When I say none existst, I meant with the same status (i.e. the WHERE condition)