• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 85
  • Last Modified:

Duplicates in Aggregate Query

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
CadenceAerospaceIT
Asked:
CadenceAerospaceIT
2 Solutions
 
hnasrCommented:
Try: Add DISTINCT after SELECT in your query.

SELECT  DISTINCT  ...

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now