|
rosshughes
Starting Member
16 Posts |
 Posted - 2010-09-15 : 17:23:59
|
| Hi All,I have a stored procedure that accumulates a labor quantity and then selects only the records where the final labor quantity is greater or equal to the production quantity.It works great except I am getting multiple records that are greater than the production quantity. For example, production quantity is 100, then when the 3rd labor transaction fulfills the 100 it displays. Then, if another labor transaction occurs and brings it to 110 that will display as well.How can I only select the first record that fulfills this condition?Any help is appreciated.Thanks,RossMy Select statement is below:-------------------------------------------SELECT counter,jcdept,resourcegrpid,jobnum,prodqty,partnum,oprseq,opdescription,duedate,laborqty,payrolldate,assemblyseq,ontime,late,finalqtycmp FROM(SELECT t.counter,t.Company,t.plant,t.jcdept,t.resourcegrpid,t.jobnum,t.prodqty,t.partnum,t.oprseq,t.opdescription,t.duedate, t.laborqty,t.payrolldate,t.labortype,t.employeenum,t.labordtlseq,t.laborentrymethod,t.jobclosed,t.number20,t.assemblyseq, t.ontime,t.late,t.laborqty+COALESCE(t1.TotalComplete,0) AS FinalQtyCmpFROM @OperationsTable tOUTER APPLY (SELECT SUM(laborqty) AS TotalComplete FROM @OperationsTable WHERE jobnum=t.jobnum AND oprseq=t.oprseq AND counter < t.counter AND counter > t.counter - 100)t1) AS SumBaseTableWHERE FinalQtyCmp > prodqty |
|
|
rosshughes
Starting Member
16 Posts |
Posted - 2010-09-15 : 17:37:03
|
| Hi Tara,Thanks for the response.TOP 1 only returns the 1st record of the entire summed data set where I am looking at returning the first record that fulfills the FinalQtyComp > prodqty condition for EACH operation.I just added TOP 1 to my select statement. Maybe I am doing something wrong. Do I need to add grouping for it to work correctly?Thanks,Ross |
 |
|
Select only First Valid Summed Row