asked
Subqueries for getting prior values
I have been asked to enhance a listing for a report. The request is to provide prior values for Operation_code, operation_sequencer, and Labor_clockindate. I have tried using sub queries (in the select) for each of these three new columns. It works but takes way too long to run. Is there a better way to get the prior values? select jh.partnum, jo.duedate, jo.operation_code, jo.operation.sequencer, jh.jobnum, ld.ClockInDate from jobhead jh inner join joboperation jo on jh.jobnum = jo.jobnum inner join labordetail ld on jo.jobnum = ld.jobnum Thanks for any suggestions
Is the [LAG][1] function what you need? [1]:
https://msdn.microsoft.com/en-GB/library/hh231256.aspx
We don't have enough information yet to answer your question completely, but assuming you have SQL Server 2012 or higher, the following will get you started: select jh.partnum ,jo.duedate ,jo.operation_code ,jo.operation.sequencer ,jh.jobnum ,ld.ClockInDate ,LAG(jo.operation_code) OVER(PARTITION BY ORDER BY ) as previous_operation_code ,LAG(jo.operation.sequencer) OVER(PARTITION BY ORDER BY ) as previous_operation_sequencer ,LAG(ld.ClockInDate) OVER(PARTITION BY ORDER BY ) as previous_clock_in_date from jobhead jh inner join joboperation jo on jh.jobnum = jo.jobnum inner join labordetail ld on jo.jobnum = ld.jobnum ; If you're using SQL Server 2005 to (but not including) 2012, let us know. You can mimic the above behaviour using row_number() but it will require an extra pass through the data. The information we're missing, is how you define what the "prior" row is. What column(s) would you order by to get the right "prior" value? You'll also need to think about whether you want the output partitioned. For example, it might be meaningless to compare times for different operation_codes. If so, you would partition by that column.
Thanks for the response and I apologize as I forgot to mention that we are running SQL 2008 R2 and therefore Lag is not available. By Prior row I mean getting the highest operation sequence number but less than the current sequence number. yes, Lag would be great as it has exactly what I needed. You mentioned two passes if I do not have 2012? What would that look like? Since reading the DB twice, what is performance like? Thanks for the input
20 People are following this question.
