Solved

Duplicates in Aggregate Query

Posted on 2014-01-08
3
53 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

739 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