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 post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...