Search the Community

I need a query that will return the "Next" lines (the lines that come after the lines I specify). I'm told the LAG function works in SQL 2012, but I'm running 2008 and LAG is not recognized.Here's what I'm looking for:Tables: WORK_ORDER, OPERATIONMy OPERATION table contains all of the operations (sequenced (10,20,30,40...)) associated with each work order. My work orders each have 10 linear operations, meaning that only one is worked on at a time, and the next operation does not start until the current operation is completed. The OPERATION table shows sequence, qty_completed, and scheduled start date. I need a query that will show me ALL of the work orders and their current operation (the operation after the last completed operation, or the operation with the earliest "scheduled start date" with 0 qty_completed). Here's something similar to what I need (but for only one workorder and using the LAG function): SELECT WORKORDER_SUB_ID, SEQUENCE_NO, COMPLETED_QTY, COALESCE( LAG(COMPLETED_QTY,1) OVER ( PARTITION BY WORKORDER_BASE_ID, WORKORDER_LOT_ID, WORKORDER_SPLIT_ID, WORKORDER_SUB_ID ORDER BY WORKORDER_SUB_ID, SEQUENCE_NO) -COMPLETED_QTY ,0) AS AVAILABLE_QTYFROM OPERATIONWHERE WORKORDER_TYPE='W' AND WORKORDER_BASE_ID='17411' AND WORKORDER_LOT_ID='65' AND WORKORDER_SPLIT_ID='0'ORDER BY WORKORDER_SUB_ID, SEQUENCE_NO;