is this even possible in SQL?

Hi i have a sql statement as follows:

SELECT
seq = (select rts.seq from sj_rts rts where lhm.oper = rts.oper and lhm.route=rts.route),
lhm.date_time,
lhm.route,
lhm.oper,
x3o.operName,
(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)

FROM
brettb.pdash2.dbo.lothistorymoves lhm,
x3oprs x3o

WHERE
lhm.lot ='S6D0IQ002A' AND
lhm.oper = x3o.oper

UNION

SELECT
rts.seq,
PROJECTEDTIME = '' -- (Contains the estimated projected time by adding the theoretical time to the date_time value in

the row above it)
rts.route,
rts.oper,
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)

FROM
Routes_X3 rts

where
rts.route=(subquery)

and rts.seq > ( subquery the returns the seq from the history)

Order By
rts.seq asc

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

UNION.

I am trying to decide if i should do this in SQL or just do it programmatically.

Thanks for any help into my problem.Stop! You are giving me a headache.

The answer to your question is "Yes".

Yes you should to it using SQL. Yes, you should do it programmatically.

Create an SQL Stored procedure to generate your recordset, and use declared variables, temporary tables, etc, liberally in order to break your task down into smaller components.sql