Is this even possible in SQL?

Results 1 to 2 of 2

Thread: Is this even possible in SQL?

  1. #1
    Join Date
    Dec 1969

    Default Is this even possible in SQL?

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

  2. #2
    Join Date
    Dec 1969
    Indianapolis, IN

    Default RE: Is this even possible in SQL?

    I think you need to build a stored procedure and store some of this data in temp tables. It seems like you understand what you&#039;re trying to do, you&#039;re just having problems putting it into code. Instead of using UNION, get everything you need in the first query in temp table A. Then use temp table A for your second query to render temp table B. Then put the two together for your final result. You may not even need to use a CURSOR depending on how complex your logic really is.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts