asked
Get prior values with SQL 2008 R2
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? The current select is shown below. 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 Now what I need is to get the three values of the prior jo.operation code, sequencer, and ld.clockindate. As mentioned, I tried using subquiries in the select to go after each prior column. It works but it takes way too much time. Thanks for any help.
Hi John. Thanks for responding. Sorry, new to this site, I may have left off the where clause. I redacted a little too much. I will post the entire script below in showing what works but way too slow. By slow, I had to kill the search after 20 minutes. use REP_Reporting select jh.partnum,jo.duedate, jo.oprseq,jo.opcode,jh.plant,jh.jobnum,jo.qtycompleted,jo.runqty,jo.estprodhours,jo.actprodhours,ld.ClockInDate as 'CurrClockIn', ---below is to get the prior job operation key (select Max(joo.OprSeq) from V_JobOper joo where joo.JobNum = jo.JobNum and joo.OprSeq < jo.OprSeq and joo.DueDate between '10/10/2015' and '10/31/2015') as 'Prior Seq', ---below is to get the prior Operation Code (select Distinct(jooo.opcode) from V_JobOper jooo where jooo.OprSeq = (select Max(joooo.OprSeq) from V_JobOper joooo where joooo.OprSeq < jo.Oprseq and joooo.JobNum = jo.JobNum and joooo.DueDate between '10/10/2015' and '10/31/2015')and jooo.JobNum = jo.JobNum) as 'Prior Operation', ---below is to get the prior clockin Date (Select MAX(ldd.ClockInDate) from V_LaborDtl ldd where ldd.JobNum = jo.jobnum and ldd.OprSeq = (select Max(jooooo.OprSeq) from V_JobOper jooooo where jooooo.OprSeq < jo.Oprseq and jooooo.JobNum = jo.JobNum and jooooo.DueDate between '10/10/2015' and '10/31/2015' )and ld.JobNum = jo.JobNum and ldd.LaborType = 'P') as 'PriorClockIn' from v_jobhead jh inner join V_JobProd jp on jh.company = jp.company and jh.JobNum = jp.JobNum inner join v_joboper jo on jh.company = jo.company and jh.jobnum = jo.jobnum inner join V_LaborDtl ld on jo.company = ld.company and jo.jobnum = ld.jobnum and jo.oprseq = ld.OprSeq and jo.OpCode = ld.OpCode where jo.DueDate between '10/10/2015' and '10/31/2015' order by jh.jobnum
23 People are following this question.
