Solved

Comparing SUMs from Different tables.

Posted on 2006-10-19
4
539 Views
Last Modified: 2011-10-03
I would like to compare SUMs from two tables. Addtionally, I need to find the Sum from the OEOrdLin_Sql table when there is not a matching record(Item_No) in the IMLSTrx_SQL table, thus the outer join.  I am not getting the right answers with my approach.
The first table is an Order Line table. It contains Item_No and Qty_To_Ship.
It’s SUM looks like:
SELECT item_no, SUM(qty_to_ship) AS QtyToShip
FROM  OEORDLIN_SQL L
WHERE (item_no = '115-005-071')
GROUP BY item_no

The second table is a Lot Transaction table. It contains Item_No and Trx_Qty.
It’s SUM looks like:
SELECT SUM(trx_qty) AS TrxQty
FROM  IMLSTRX_SQL
WHERE (item_no = '115-005-071') AND (source = 'O') AND (ctl_no = 0)

The SUM for the OEOrdLin_SQL table is 4.
The SUM for the IMLSTrx_SQL table is 4.

Here is my attempt to compare.
SELECT L.item_no, SUM(L.qty_to_ship) AS QtyToShip, SUM(T.trx_qty) AS TrxQty
FROM  OEORDLIN_SQL L LEFT OUTER JOIN
               IMLSTRX_SQL T ON L.item_no = T.item_no
WHERE (T.source = 'O') AND (T.ctl_no = 0) AND (L.item_no = '115-005-071')
GROUP BY L.item_no

My attempt yields QtyToShip = 16 and TrxQty = 20.

What is the right approach?

Thanks,
Pat

Ultimately, I need to only return those records where QtyToShip <> TrxQty so I will need to add a conditional in the Where or Having clause.
0
Comment
Question by:mpdillon
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 17768980
try this

SELECT item_no, SUM(qty_to_ship) AS QtyToShip, sum(SELECT trx_qty AS TrxQty
FROM  IMLSTRX_SQL
WHERE (item_no = L.item_no) AND (source = 'O') AND (ctl_no = 0))
FROM  OEORDLIN_SQL L
WHERE (item_no = '115-005-071')
GROUP BY item_no


itsvtk
0
 
LVL 7

Expert Comment

by:TRACEYMARY
ID: 17768992
Use cursor..
   Get all records from Order
        Get all totals from line
        Get all totals for the transaction.

The transaction table needs to contact order from Order table else the item could not belong to correct order?
0
 
LVL 14

Accepted Solution

by:
Thandava Vallepalli earned 500 total points
ID: 17769102
Oooooops...   Try this one it works

SELECT item_no, SUM(qty_to_ship) AS QtyToShip,
                  (SELECT sum(trx_qty)
                     FROM  IMLSTRX_SQL
                     WHERE (item_no = L.item_no) AND (source = 'O') AND (ctl_no = 0)) AS TrxQty
FROM  OEORDLIN_SQL L
WHERE (item_no = '115-005-071')
GROUP BY item_no


itsvtk
0
 

Author Comment

by:mpdillon
ID: 17774339
It worked great. Here is the final query. I made it a Full Join. I need to catch any orders that do not have LotNos attached and I need to catch any LotNos that are not attached to an order.

SELECT     L.item_no, L.ord_no, L.line_no, SUM(ISNULL(L.qty_to_ship, 0)) AS Qty_TO_Ship, ISNULL
                          ((SELECT     SUM(T.Trx_Qty)
                              FROM         IMLSTrx_SQL T
                              WHERE     L.ITem_No = T.Item_No AND L.Ord_No = T.Ord_No AND L.Line_No = T.Line_No AND T.Source = 'O' AND T.Ctl_No = 0), 0) AS Trx_Qty,
                      T.ser_lot_no
FROM         OEORDLIN_SQL L FULL OUTER JOIN
                      IMLSTRX_SQL T ON L.item_no = T.item_no AND L.ord_no = T.ord_no AND L.line_no = T.line_no
WHERE     (T.source = 'O') AND (T.ctl_no = 0)
GROUP BY L.item_no, L.ord_no, L.line_no, T.ser_lot_no
HAVING      (SUM(ISNULL(L.qty_to_ship, 0)) <> ISNULL
                          ((SELECT     SUM(T.Trx_Qty)
                              FROM         IMLSTrx_SQL T
                              WHERE     L.ITem_No = T.Item_No AND L.Ord_No = T.Ord_No AND L.Line_No = T.Line_No AND T.Source = 'O' AND T.Ctl_No = 0), 0))
0

Featured Post

Turn Insights Into Action

You’ve already invested in ITSM tools, chat applications, automation utilities, and more. Fortify these solutions with intelligent communications so you can drive business processes forward.

With xMatters, you'll never miss a beat.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question