Jump to content

LAG type query in SQL 2008 R2


Recommended Posts

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;

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...