Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Posts
    14

    Unanswered: 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.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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