user3597849 user3597849 - 1 month ago 5
SQL Question

How can I select all order numbers with operations, but limit some orders based on an operations time?

I have a stored procedure that currently pulls all order numbers and operations based on a work center.

I have it set to specifically leave out 2 operations (in WHERE statement)

AND operation NOT IN ('Test1', 'Test2')


I need to change this to include these operations, but, only if they have been there for under x amount of time. I do have a column with a start date to work with in format 'YYYY-MM-DD HH:MM:SS.0000000'

an example of the columns I am working with are:

Order operation start_date
AW1234 Test1 2016-10-02 20:18:39.0000000
WEFDO43 Test2 2016-10-02 04:18:39.0000000
WEFVO43 OP1 2016-10-02 04:18:39.0000000
WEXJO43 LITTLE 2016-10-02 04:18:39.0000000
WEBJO43 BIG 2016-10-02 04:18:39.0000000
WEWJO43 TIN 2016-10-02 04:18:39.0000000


I have tried adding it to 'WHERE' and creating a CASE, but i get lost at the end

--, (SELECT CASE WHEN w.operation like '%FAR-CMPHLD%' AND Need_Date < getdate() - 6 ....ELSE END...)


how can I add these operations' order numbers if they have been started in the last X amount of days without changing the original query?

I am using SQL server 2012

Answer

I ended up adding a UNION inside of my original code, nested code like below:

               SELECT *
               FROM
               (*Original code*
                SELECT *
                FROM Testing1 w, Testing2 so
                WHERE w.Site = 'OROR'
                AND so.shop = w.shop
                AND w.operation NOT IN ('Test1', 'Test2') 
                AND convert(Date, actual_start_date, 120) >= GETDATE() - 10

                UNION

                SELECT *
                FROM Testing1 w, Testing2 so
                WHERE w.Site = 'OROR'
                AND so.shop = w.shop
                AND w.operation Like('Test1') 
                AND convert(Date, actual_start_date, 120) >= GETDATE() - 8

                UNION

                SELECT *
                FROM Testing1 w, Testing2 so
                WHERE w.Site = 'OROR'
                AND so.shop = w.shop
                AND w.operation Like('Test2') 
                AND convert(Date, actual_start_date, 120) >= GETDATE() - 4) b

doing it this way allowed me to select these operations separately and control the amount of time they have been at that operation, since they needed to be different

would have liked a simpler version, but this one runs great