MAT MAT - 3 months ago 6
MySQL Question

SQL Query Help - Trying to locate records (work orders) in a table that have had no activity in the past weeks

I'm trying to locate records (work orders) in a table that have had no activity in the past weeks.
So, I need so select distinct work order from the table where the date field is not within the past two weeks from the current date. I can get the work orders where there are dates older than past two weeks, but not sure how to filter out the work orders where there are also dates within the past two weeks.

select distinct WorkOrder
from SMWorkOrder
where SMCo = 1 and WorkOrder in
(select WorkOrder
from SMWorkCompleted
where SMCo = 1 and Date < DATEADD(WEEK, -2, GETDATE()))


What do I need to add to this so I get work orders that have lines in this table dated older than the past two weeks and also do not have any lines within in the past weeks?

Any assistance is greatly appreciated!
MAT

Answer

Using a left outer join really works well in situations like this:

SELECT
    o.WorkOrder
FROM
    SMWorkOrder o
    LEFT JOIN SMWorkCompleted c
    ON o.WorkOrder = c.WorkOrder
    AND c.Date >= DATEADD(WEEK,-2,CAST(GETDATE() AS DATE)) AND c.Date <= GETDATE()
    AND c.SMCo = 1
WHERE
    c.WorkOrder is NULL
    AND o.SMCo = 1

But note I doubt you have SMCo on both tables perhaps you do but you should remove it from the where condition if not on the SWorkOder table and/or remove it from the ON condition of the join if on the SWorkCompleted Table

For the IN method you actually want to use NOT IN:

SELECT
    o.WorkOrder
FROM
    SWorkOrder o
WHERE
    o.SMCo = 1
    AND o.WorkOrder NOT IN (
       SELECT
          WorkOrder
       FROM
          SMWorkCompleted c
       WHERE
          c.Date >= DATEADD(WEEK,-2,CAST(GETDATE() AS DATE)) AND c.Date <= GETDATE()
          AND c.SMCo = 1
    )

NOTE DO NOT USE IN if WorkOrder could be NULL!

The NOT EXISTS method works pretty well too:

SELECT
    o.WorkOrder
FROM
    SWorkOrder o
WHERE
    o.SMCo = 1
    AND NOT EXISTS (
       SELECT
          WorkOrder
       FROM
          SMWorkCompleted c
       WHERE
          c.WorkOrder = o.WorkOrder
          AND c.Date >= DATEADD(WEEK,-2,CAST(GETDATE() AS DATE)) AND c.Date <= GETDATE()
          AND c.SMCo = 1
    )