CODEWITHSUNDEEP
sqljoinleft-join
Gonzalez
Gonzalez

Reputation: 7

SQL join without duplicates

I need to return a single line per "WO #" (VIEW_SPS_WO_OPERATION. SI_NUMBER as "WO #")

The fields being returned show the same values in more than one row so my join might not be correct Except for the following: one single “WO #” can have more than one NEXT_DELIVERY_DATE from “VIEW_WOBOM.NEXT_DELIVERY_DATE”, I only need the entry with latest date (max?).

I hope I’m being clear. Thanks in advance for your help.

   SELECT 
    VIEW_SPS_WO_OPERATION.COMPANY_NAME as Customer, 
    VIEW_SPS_WO_OPERATION. SI_NUMBER as "WO #",
    VIEW_SPS_WO_OPERATION. PN as "Part Number",
    VIEW_SPS_WO_OPERATION. DEPT_NAME as "Department",
    VIEW_SPS_WO_OPERATION. TOTAL_PARTS as "Extended Price",
    VIEW_SPS_WO_OPERATION.DESCRIPTION as Description,
    VIEW_WO_SUB.WORK_TYPE as "Work Req.", 
    VIEW_SPS_WO_OPERATION.STATUS_DESC as Task, 
    VIEW_SPS_WO_OPERATION.ENTRY_DATE as "Rec Date", 
    VIEW_SPS_WO_OPERATION.DUE_DATE as "Qtd Date", 
    VIEW_SPS_WO_OPERATION.EST_COMP_DATE as "App. Date", 
    VIEW_WO_TASK_STATISTICS.SKILLS_EST_HOURS  as "Est. Labor Hrs.", 
    VIEW_WO_TASK_STATISTICS.LABOR_HOURS as "Act. Labor Hrs.",
    VIEW_WOBOM.NEXT_DELIVERY_DATE

  FROM 
    GATCRGQCTL.VIEW_SPS_WO_OPERATION VIEW_SPS_WO_OPERATION,
    GATCRGQCTL.VIEW_WO_SUB VIEW_WO_SUB,
    GATCRGQCTL.VIEW_WO_TASK_STATISTICS VIEW_WO_TASK_STATISTICS,
    GATCRGQCTL.VIEW_WIP_VALUATION VIEW_WIP_VALUATION, 
    GATCRGQCTL.WO_BOM WO_BOM,
    GATCRGQCTL.VIEW_WOBOM VIEW_WOBOM
   WHERE 
    VIEW_SPS_WO_OPERATION.SI_NUMBER = VIEW_WO_SUB. SI_NUMBER 
     AND
    VIEW_WIP_VALUATION.WOO_AUTO_KEY = WO_BOM. WOO_AUTO_KEY AND
    VIEW_SPS_WO_OPERATION.WOO_AUTO_KEY = VIEW_WIP_VALUATION.WOO_AUTO_KEY AND
    WO_BOM.WOT_AUTO_KEY = VIEW_WO_TASK_STATISTICS.WOT_AUTO_KEY
     AND
    WO_BOM.WOT_AUTO_KEY = VIEW_WOBOM.WOT_AUTO_KEY

Upvotes: 0

Views: 209

Answers (1)

Declan_K
Declan_K

Reputation: 6826

You need the MIN(VIEW_WOBOM.NEXT_DELIVERY_DATE) and in your WHERE clause you need to limit the results to those where VIEW_WOBOM.NEXT_DELIVERY_DATE >= Now(). How you calcualte NOW will depend on your RDBMS.

Upvotes: 1

Related Questions