January 14, 2015 at 10:16 am
Some assistance is needed. Hopefully, I can explain what I'm trying to do and someone can help me write an update query. It's got me a bit cross-eyed.
My goal is to update the "PriorInsert" field with the "DateInserted" from the previously inserted record where the WorkOrder, MachineNo, and Operator are all in the same group.
While trying to get to the correct previous record, I wrote the query below. I'm "almost" too embarrassed to post it; but I really need help on this one. Thanks in advance for your help.
P.S. The attached .txt file includes a create and insert tbl_tmp sampling.
select top 1
a.ID,
a.WorkOrder,
a.MachineNo,
a.Operator,
a.PriorInsert,
a.DateInserted,
c.ID,
d.DateInserted -- Need to update a.PriorInsert with this record
from tbl_tmp a
left outer join
(
select MIN(ID) as ID,
workorder,
machineno,
operator
from tbl_tmp
group by workorder,
machineno,
operator
) c on c.workorder = a.workorder
and c.machineno = a.machineno
and c.operator = a.operator
left outer join (
select ID,
DateInserted
from tbl_tmp
) d on d.ID = c.ID
January 14, 2015 at 2:51 pm
Skip (1/14/2015)
Some assistance is needed. Hopefully, I can explain what I'm trying to do and someone can help me write an update query. It's got me a bit cross-eyed.My goal is to update the "PriorInsert" field with the "DateInserted" from the previously inserted record where the WorkOrder, MachineNo, and Operator are all in the same group.
While trying to get to the correct previous record, I wrote the query below. I'm "almost" too embarrassed to post it; but I really need help on this one. Thanks in advance for your help.
P.S. The attached .txt file includes a create and insert tbl_tmp sampling.
select top 1
a.ID,
a.WorkOrder,
a.MachineNo,
a.Operator,
a.PriorInsert,
a.DateInserted,
c.ID,
d.DateInserted -- Need to update a.PriorInsert with this record
from tbl_tmp a
left outer join
(
select MIN(ID) as ID,
workorder,
machineno,
operator
from tbl_tmp
group by workorder,
machineno,
operator
) c on c.workorder = a.workorder
and c.machineno = a.machineno
and c.operator = a.operator
left outer join (
select ID,
DateInserted
from tbl_tmp
) d on d.ID = c.ID
Sometimes it's easier to do it in a multi-step process.
I frequently will use a temp table to get the intermediate results and then update from there.
For example
SELECT WorkOrder, MachineNo, Operator, MAX(DateInserted) as LastDateInserted
INTO #tmp
FROM tbl_tmp
WHERE PriorInsert IS NOT NULL
GROUP BY WorkOrder, MachineNo, Operator
Then you can update your tbl_tmp by joining to the temp table setting the PriorInserted to the LastDateInserted
Obviously, this strategy only works as long as your PriorInserted is getting filled in. If you already have a bunch of data and this is a new field that needs to be updated retroactively, then it's a bit more work.
In a situation like this, it can be easy to write code that has a triangular join in it. Someone smarter than me can probably write the update query without making a triangular join, but doing it this way has the advantage of readability for me.
January 14, 2015 at 6:26 pm
Thank you so very much for such a great idea. I used to use #tmp tables all the time, but haven't in a while and didn't even consider it.
The query had to run it about 150 times because each workorder has so many records. It did the trick though. Again thank you very much. Below is the working code.
declare @i as int
set @i = 150
while @i > 0 begin
drop table #tmp
SELECT WorkOrder, MachineNo, Operator, MAX(DateInserted) as LastDateInserted
INTO #tmp
FROM tbl_tmp
WHERE PriorInsert IS NOT NULL
AND SHIRRTYPE IN ('GS', 'PS', 'BS')
GROUP BY WorkOrder, MachineNo, Operator
update tbl_tmp
set priorinsert = LastDateInserted
from tbl_tmp a
left outer join #tmp b on b.workorder = a.workorder and b.operator = a.operator and b.machineno = a.machineno
left outer join (
select min(ID) as ID,
a.WorkOrder,
a.MachineNo,
a.Operator
from tbl_tmp a
where PriorInsert is null
group by
a.WorkOrder,
a.MachineNo,
a.Operator
) c on c.ID = a.ID
where c.ID is not null
set @i = @i - 1
end
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply