seq = (select rts.seq from sj_rts rts where lhm.oper = rts.oper and lhm.route=rts.route),
(lhm.date_time - (SELECT max(lhm1.date_time) FROM brettb.pdash2.dbo.lothistorymoves lhm1, x3oprs x3o1 WHERE lhm1.lot
= 'S6D0IQ002A' AND lhm1.oper = x3o1.oper and lhm1.date_time < lhm.date_time)) as ActualTime,
theoreticalTime = (subquery that returns theoretical time for particular row depending on value of oper and route)
lhm.lot ='S6D0IQ002A' AND
lhm.oper = x3o.oper
PROJECTEDTIME = '' -- (Contains the estimated projected time by adding the theoretical time to the date_time value in
the row above it)
rts.name AS operName,
ActualTime = '', -- Blank since this is the future
theoreticalTime = ( subquery that returns theoretical time for particular row depending on value of oper and route)
and rts.seq > ( subquery the returns the seq from the history)
The first sql statement is the history for a particular item and gets its data from a history table. the second query
is the next steps that item needs to go through and gets its data from another table that just lists all the steps
according to its seq number. Each row in the whole UNION has a related theoretical time that is specific to the route
and the oper values.
What I am trying to do is create a column called projectedTime in the second query that will take the LAST date_time
from the first query (i can do this with a max(date_time) ) and add the theoretical time to produce the projected
date_time for the current row in the second query. THen for the next row, i want to add its theoretical time to the
projectedtime of the row above to get the next projected time. i want to do this for all the rows in the next steps
query (the second query). Essentially what i would get is a report detailing the steps already completed and the
projected completion dates for the next steps.
The issue is that I have to add the theoretical time for the second query's row to something. For the first row of
query2 its easy, just add the theoretical time to the last row of the first query (i can use a subquery to get the
date) to create the projected time. However, then for the next row, adding the theoretical time to the last row of
the first query won't give me the projected time, instead i need to add the theoretical time to the projected time of
the first row and then continue doing this to get the rest of the projected times.
I'm not sure how i can accomplish this in SQL or if its even possible. If i could somehow either create another
column on the second query that adds together the theoretical times of that row and that of the rows above it and add
the sume to the last date_time from the first query, i could get what i need. Or if i could somehow use a CURSOR to
bring back the date_time from the row above and add the theoretical time to that datetime and stick it in the column,
it could work. However, I read somewhere that you cant use cursors with more than one select statement, such as my
I am trying to decide if i should do this in SQL or just do it programmatically.