Solved

Duplicates in Aggregate Query

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
tools to scan a SQL server's problem 14 28
SQL R 21 29
Current Month Filter in Visual Studio 10 24
Microsoft CRM 4.0 26 23
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…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

808 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