Solved

Duplicates in Aggregate Query

Posted on 2014-01-08
3
61 Views
Last Modified: 2016-05-31
This query works perfectly, but I am getting duplicates if I have multiple max (last) transaction date (let's say I have three tickets on 1/1/14)  it will list three items.

SELECT     t1.WORKORDER_BASE_ID, t1.WORKORDER_LOT_ID, t1.WORKORDER_SPLIT_ID, t1.MIN_SEQUENCE_NO, t2.RESOURCE_ID, WORK_ORDER.PART_ID,
                      WORK_ORDER.DESIRED_QTY, WORK_ORDER.DESIRED_RLS_DATE, WORK_ORDER.DESIRED_WANT_DATE, WORK_ORDER.STATUS,
                      WORK_ORDER.ACT_MATERIAL_COST, WORK_ORDER.ACT_LABOR_COST, WORK_ORDER.ACT_BURDEN_COST, WORK_ORDER.ACT_SERVICE_COST,
                      PART.DESCRIPTION, PART.USER_2, SHOP_RESOURCE.DESCRIPTION AS Expr1, lbr.TRANSACTION_ID
FROM         (SELECT     WORKORDER_BASE_ID, WORKORDER_LOT_ID, WORKORDER_SPLIT_ID, MIN(SEQUENCE_NO) AS MIN_SEQUENCE_NO
                       FROM          OPERATION
                       GROUP BY WORKORDER_BASE_ID, WORKORDER_LOT_ID, WORKORDER_SPLIT_ID, WORKORDER_TYPE, STATUS
                       HAVING      (WORKORDER_TYPE = 'W') AND (STATUS = 'R')) AS t1
INNER JOIN
                          (SELECT     WORKORDER_BASE_ID, WORKORDER_LOT_ID, WORKORDER_SPLIT_ID, SEQUENCE_NO, RESOURCE_ID
                            FROM          OPERATION AS OPERATION_1) AS t2 ON t1.WORKORDER_BASE_ID = t2.WORKORDER_BASE_ID AND
                      t1.WORKORDER_LOT_ID = t2.WORKORDER_LOT_ID AND t1.WORKORDER_SPLIT_ID = t2.WORKORDER_SPLIT_ID AND
                      t1.MIN_SEQUENCE_NO = t2.SEQUENCE_NO
INNER JOIN
                      WORK_ORDER ON t1.WORKORDER_BASE_ID = WORK_ORDER.BASE_ID AND t1.WORKORDER_LOT_ID = WORK_ORDER.LOT_ID AND
                      t1.WORKORDER_SPLIT_ID = WORK_ORDER.SPLIT_ID
INNER JOIN      PART ON WORK_ORDER.PART_ID = PART.ID
INNER JOIN      SHOP_RESOURCE ON t2.RESOURCE_ID = SHOP_RESOURCE.ID
LEFT JOIN
                (SELECT tix.WORKORDER_BASE_ID, tix.WORKORDER_LOT_ID, tix.WORKORDER_SPLIT_ID, tix.TRANSACTION_DATE, tix.OPERATION_SEQ_NO, tix.TRANSACTION_ID
                FROM LABOR_TICKET TIX
                INNER JOIN (SELECT WORKORDER_BASE_ID,  MAX(TRANSACTION_DATE) AS TRANSACTION_DATE  
                                        FROM LABOR_TICKET TIX
                                        GROUP BY WORKORDER_BASE_ID) A ON A.WORKORDER_BASE_ID = TIX.WORKORDER_BASE_ID AND A.TRANSACTION_DATE = TIX.TRANSACTION_DATE) lbr
                ON lbr.WORKORDER_BASE_ID = WORK_ORDER.BASE_ID AND lbr.WORKORDER_LOT_ID = WORK_ORDER.LOT_ID AND lbr.WORKORDER_SPLIT_ID = WORK_ORDER.SPLIT_ID
WHERE     (WORK_ORDER.STATUS = 'R')
ORDER BY t1.WORKORDER_BASE_ID
0
Comment
Question by:CadenceAerospaceIT
[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
3 Comments
 
LVL 30

Assisted Solution

by:hnasr
hnasr earned 250 total points
ID: 39767142
Try: Add DISTINCT after SELECT in your query.

SELECT  DISTINCT  ...

Open in new window

0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 39767360
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

623 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