Solved

Duplicates in Aggregate Query

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
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.

734 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