Select field from next line

Asked

Viewed 528 times

0

I need to make a query bringing the second value of a field to the first line, that is, I need the value of the first field of the column "Time1" to be filled with the value of the second field of the column "Time".. I created an empty field that is the field "Tempo1" to make a case pulling the values but I’m not able to pull the value as I want.

Can someone help me?

Query:

SELECT linhas,
       ctns,
       usuario,
       hora,
       dia,
       tempo,
       CASE
           WHEN tempo1 IS NULL THEN tempo
           ELSE tempo
       END AS tempo1
FROM
  (SELECT rownum AS linhas,
          a.ctn AS ctns,
          a.usu AS usuario,
          a.hh AS hora,
          a.dia1 AS dia,
          a.dia2 AS tempo,
          ' ' AS tempo1
   FROM
     (SELECT Distinct(DLYTRN.To_subnum) AS ctn,
                                           DLYTRN.usr_id AS usu,
                                                            To_Char (DLYTRN.trndte, 'HH24') AS HH,
                                                                                               To_Char (DLYTRN.trndte, 'DD') AS dia1,
                                                                                                                                To_Char (DLYTRN.ins_dt, 'HH24:MI:SS') AS dia2
      FROM DLYTRN
      WHERE DLYTRN.trndte >= trunc (sysdate,'MM') -60
        AND DLYTRN.oprcod = 'UPK'
        AND DLYTRN.actcod IN ('CASPCK',
                              'PLAPCK',
                              'OTRPCK')
        AND DLYTRN.usr_id = 'SCRUZ'
      ORDER BY To_Char (DLYTRN.ins_dt, 'HH24:MI:SS'),
               DLYTRN.usr_id) a)
sql sql-server

  • If you need more data I can pass .... .

  • https://technet.microsoft.com/en-us/library/hh213125%28v=sql.110%29.aspx https://technet.microsoft.com/en-us/library/hh231256%28v=sql.110%29.aspx

  • Good Morning .... LEAD AND LAG give no result. I tried to use them even before posting the question.

  • You tried to use lead and lag with the internal part of your SELECT rownum AS Lin ... id) a)> ?

  • I tried .... but he does not accept the command ... :(

  • something like USE xxxx GO SELECT .... LEAD(time, 1,0) OVER (ORDER BY rownum,ctns,usuario,hour,dia1) AS Nexttempo FROM (<Sau sql>);

  • guy worked out !!!!!!!

  • thank you very much .... thank you very much .......

  • boolaaa show/

  • Motta, sorry to abuse your intelligence kkkk but let me ask you one more thing... You can put a filter together with the LEAD to raise the value to the top line only if the user is the same and if it is not the same user he will put a "zero" or leave the null field ?

Show 5 more comments

1 answer

1

  • Make an example in your answer to add value to it.

  • Motta, sorry to abuse your intelligence kkkk But let me ask you something else... or leave the field null ?

  • try with PARTITION BY

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.