Forum Stats

  • 3,758,433 Users
  • 2,251,387 Discussions
  • 7,870,189 Comments

Discussions

get data in report

User_AC3QE
User_AC3QE Member Posts: 34 Red Ribbon
Hi
I need to create a report that displays from the dt_lanc field controlling by fields (seq, attend ) repeat the data until you have a new seq for a more recent date.

--create table
CREATE TABLE dados (
seq    number(10) not null,
atend number(10) not null,
pac varchar(255),
dt_lanc date,
dados varchar(255));

--insert
Insert into dados (seq,atend,pac,dt_lanc,dados) values (1,10,'dados',sysdate,'Manha');
Insert into dados (seq,atend,pac,dt_lanc,dados) values (2,10,'dados',sysdate+4,'Tarde');

--data
select seq, 
       atend, 
       pac, 
       to_char(dt_lanc,'dd/mm/yyyy') as dt,
       dados 
from dados;


I need you to repeat the data from the first row per day until the next seq that has a new date


Example:

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,047 Red Diamond
    Accepted Answer

    Hi, @User_AC3QE

    expected outcome:

    /* 1           10        dados   18/06/21 15:20:02        Manha – Hiding this line because it has the same date as line 2 and is less recent*/

    2            10          dados   18/06/21 16:20:02          Tarde

    2            10          dados   19/06/21 16:20:02          Tarde

    2            10          dados   20/06/21 16:20:02          Tarde

    2           10         dados  21/06/21 16:20:02          Tarde – including this line up to the date of the new seq

    3            10          dados   22/06/21 15:20:02          Noite

    Do you want 5 rows of output or 6? If you want 5 rows of output, it would be clearer and simpler if you just posted 5 rows in the expected output. If you want to explain why another row is not included, do that either before or after the expected results.

    I'm guessing you want 5 rows of output, like this

       SEQ   ATEND  PAC        DT_LANC             DADOS
    ------- ------- ---------- ------------------- ----------
          2      10 dados      18/06/2021 16:21:38 Tarde
          2      10 dados      19/06/2021 16:21:38 Tarde
          2      10 dados      20/06/2021 16:21:38 Tarde
          2      10 dados      21/06/2021 16:21:38 Tarde
          3      10 dados      22/06/2021 15:21:38 Noite
    

    Here's one way to get those results:

     WITH  got_n_days  AS
    (
      SELECT    dados.*
      ,	    LEAD ( dt_lanc
      	  	 , 1
    		 , dt_lanc + 1
    		 ) OVER ( ORDER BY dt_lanc
    		  	, seq
    			) - TRUNC (dt_lanc) AS n_days
      ,	    ROWNUM	  	 	    AS rn
      FROM      dados
    )
    SELECT   seq, atend, pac
    , 	 dt_lanc + LEVEL - 1 AS dt_lanc
    , 	 dados
    FROM   got_n_days
    START WITH  n_days >= 1
    CONNECT BY  rn = PRIOR rn
    	AND LEVEL <= n_days
    	AND PRIOR SYS_GUID () IS NOT NULL
    ORDER BY dt_lanc
    ;
    


    User_AC3QE
«1

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,047 Red Diamond
    edited Jun 18, 2021 2:45PM

    Hi, @User_AC3QE

    I'm not sure I understand your requirements. For example, I don't know what

    controlling by fields (seq, attend )

    means.

    This gets the results you requested from the sample data:

    WITH  got_n_days  AS
    (
      SELECT  dados.*
      ,	  LEAD (dt_lanc) OVER (ORDER BY dt_lanc) - dt_lanc AS n_days
      FROM    dados
    )
    SELECT   n.seq, n.atend, n.pac
    , 	 n.dt_lanc + c.n - 1 AS dt_lanc
    , 	 dados
    FROM     got_n_days n
    CROSS APPLY (
       	        SELECT  LEVEL AS n
    		FROM	dual
    		CONNECT BY LEVEL <= n.n_days
       	    )	   c
    ORDER BY dt_lanc  -- or whatever you want
    ;
    

    What results do you want if dt_lanc is not unique? What if the difference between one dt_lanc and the next is not an integer number of days?

  • User_AC3QE
    User_AC3QE Member Posts: 34 Red Ribbon
    thank you very much for understanding the question.
    
    current database and oracle 11G does not run CROSS APPLY
    
    
    when dt_lanc doesn't stop only the seq will be different,
    should display the most current dt_lanc which would be the most recent seq of the day.
    
    so there will always be a difference of 1 day
    


  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,047 Red Diamond

    Hi, @User_AC3QE

    current database and oracle 11G does not run CROSS APPLY
    

    The query below should work in Oracle 9.1 and up:

    WITH  got_n_days  AS
    (
      SELECT  dados.*
      ,	  LEAD (dt_lanc) OVER (ORDER BY dt_lanc, seq) - dt_lanc   AS n_days
      ,	  ROWNUM	  	    	 	  	 	  AS rn
      FROM    dados
    )
    SELECT   seq, atend, pac
    , 	 dt_lanc + LEVEL - 1 AS dt_lanc
    , 	 dados
    FROM     got_n_days
    CONNECT BY  rn = PRIOR rn
    	AND LEVEL <= n_days
    	AND PRIOR SYS_GUID () IS NOT NULL
    ORDER BY dt_lanc
    ;
    


    when dt_lanc doesn't stop only the seq will be different,

    should display the most current dt_lanc which would be the most recent seq of the day.

    Sorry, I don't understand what it means for dt_lanc to stop. (It can be very handy to make up terms, like "stop", for a particular problem, but you need to define them.) If dt_lanc is not unique, then post some sample data where two (or more) rows have the same value of dt_lanc and the results you want from that sample data. Explain why you want those results.

  • User_AC3QE
    User_AC3QE Member Posts: 34 Red Ribbon


    Oops I'll try to explain better
    it may have a record with the same dt_lanc but with another seq code.
    
    in this case the most current dt_lanc of the day should appear.
    
    and follow the other days until the new seq
    


    would have to display like this
    


  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,047 Red Diamond

    Hi,

    it may have a record with the same dt_lanc but with another seq code.


    in this case the most current dt_lanc of the day should appear.

    So, some rows in the table may not appear in the results at all, is that right? You may need to add START WITH n_days > 0 to the query above.

    Once again, include examples in your sample data. Always include examples of special cases you need to handle in your sample data, desired results and explanation.

  • User_AC3QE
    User_AC3QE Member Posts: 34 Red Ribbon


    exactly why the new data entry will replace the other, not wanting to display the other line of data.

    how would you put START WITH n_days > 0 in the script

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,047 Red Diamond

    Hi,

    exactly why the new data entry will replace the other, not wanting to display the other line of data.

    Sorry I don't understand.

    Once again, post some sample data (e.g., some additional INSERT statements) that include examples of all special cases you need to handle (e.g., duplicate dt_lancs) and the exact results you want from the complete sample data.

  • User_AC3QE
    User_AC3QE Member Posts: 34 Red Ribbon

    Sorry,


    drop table dados;

     

    CREATE TABLE dados (

    seq   number(10) not null,

    atend number(10) not null,

    pac varchar(255),

    dt_lanc date,

    dados varchar(255));

     

    Insert into dados (seq,atend,pac,dt_lanc,dados) values (1,10,'dados',sysdate,'Manha');

    /

    Insert into dados (seq,atend,pac,dt_lanc,dados) values (2,10,'dados',sysdate+1/24,'Tarde');

    /

    Insert into dados (seq,atend,pac,dt_lanc,dados) values (3,10,'dados',sysdate+4,'Noite');

    commit;

    /

    expected outcome:

    /* 1           10        dados   18/06/21 15:20:02        Manha – Hiding this line because it has the same date as line 2 and is less recent*/

    2            10          dados   18/06/21 16:20:02          Tarde

    2            10          dados   19/06/21 16:20:02          Tarde

    2            10          dados   20/06/21 16:20:02          Tarde

    2           10          dados  21/06/21 16:20:02          Tarde – including this line up to the date of the new seq

    3            10          dados   22/06/21 15:20:02          Noite

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,047 Red Diamond
    Accepted Answer

    Hi, @User_AC3QE

    expected outcome:

    /* 1           10        dados   18/06/21 15:20:02        Manha – Hiding this line because it has the same date as line 2 and is less recent*/

    2            10          dados   18/06/21 16:20:02          Tarde

    2            10          dados   19/06/21 16:20:02          Tarde

    2            10          dados   20/06/21 16:20:02          Tarde

    2           10         dados  21/06/21 16:20:02          Tarde – including this line up to the date of the new seq

    3            10          dados   22/06/21 15:20:02          Noite

    Do you want 5 rows of output or 6? If you want 5 rows of output, it would be clearer and simpler if you just posted 5 rows in the expected output. If you want to explain why another row is not included, do that either before or after the expected results.

    I'm guessing you want 5 rows of output, like this

       SEQ   ATEND  PAC        DT_LANC             DADOS
    ------- ------- ---------- ------------------- ----------
          2      10 dados      18/06/2021 16:21:38 Tarde
          2      10 dados      19/06/2021 16:21:38 Tarde
          2      10 dados      20/06/2021 16:21:38 Tarde
          2      10 dados      21/06/2021 16:21:38 Tarde
          3      10 dados      22/06/2021 15:21:38 Noite
    

    Here's one way to get those results:

     WITH  got_n_days  AS
    (
      SELECT    dados.*
      ,	    LEAD ( dt_lanc
      	  	 , 1
    		 , dt_lanc + 1
    		 ) OVER ( ORDER BY dt_lanc
    		  	, seq
    			) - TRUNC (dt_lanc) AS n_days
      ,	    ROWNUM	  	 	    AS rn
      FROM      dados
    )
    SELECT   seq, atend, pac
    , 	 dt_lanc + LEVEL - 1 AS dt_lanc
    , 	 dados
    FROM   got_n_days
    START WITH  n_days >= 1
    CONNECT BY  rn = PRIOR rn
    	AND LEVEL <= n_days
    	AND PRIOR SYS_GUID () IS NOT NULL
    ORDER BY dt_lanc
    ;
    


    User_AC3QE
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,047 Red Diamond

    By the way:

    Insert into dados (seq,atend,pac,dt_lanc,dados) values (1,10,'dados',sysdate,'Manha');

    /

    Insert into dados (seq,atend,pac,dt_lanc,dados) values (2,10,'dados',sysdate+1/24,'Tarde');

    /

    Insert into dados (seq,atend,pac,dt_lanc,dados) values (3,10,'dados',sysdate+4,'Noite');

    commit;

    /

    You're INSERTing 5 rows. A semicolon (;) or a slash (/) alone ends a SQL command. A slash right after an issued SQL statement means "run the last statement again". In this problem, it doesn't matter because, when there are multiple rows with the same dt_lanc, all but one of them are ignored.