Sum of hours

[Cayo Magno Fontana]

Good afternoon friends,

Through a web system in .NET, are inserted, in a table in Oracle, user records over the time they are logged in on certain sub-applications.
I would like to make the sum of the time fields of the records of the table.
Example:

Records in the table:



The campaign field are sub-applications, registration, obviously, are the types of users, day represents the day they logged in, and finally, the time (is standard date 01/01/0001 that I inserted next to each insert porque was not able to insert undated)

In an appointment in an X campaign, user Y, and Z day, return me more than one line. For example:

Select all

CLICK 21	847123	22/02/2008	01/01/0001 0:01:03 
CLICK 21	847123	22/02/2008	01/01/0001 0:05:27

I would like to know how I can return a single lines with the added hours, example, in this case above, would be:

Select all

CLICK 21	847123	22/02/2008	01/01/0001 0:06:30

Thanks for the attention.

[rogenaro]

Assuming that always in the Time field will appear date 01/01/0001, and that you want to group your results by enrollment, campaign and day:

Select all

select campanha 
     , matricula 
     , dia 
     , tempo_login 
     , trunc(tempo_login)||' dias ' 
     ||to_char(to_date('00010101', 'yyyymmdd') + (tempo_login), 'hh24:mi:ss') 
from 
( 
  select campanha, matricula, dia, sum(hora - trunc(hora)) tempo_login 
  from   t05 
  where  matricula = 847123 
  and    campanha  = 'CLICK 21' 
  group by campanha, matricula, dia 
); 

[rogenaro]

ops .. had a mistake in query ..

Select all

select campanha 
     , matricula 
     , dia 
     , trunc(tempo_login)||' dias ' 
     ||to_char(to_date('00010101', 'yyyymmdd') + (tempo_login), 'hh24:mi:ss') 
from 
( 
  select campanha, matricula, dia, sum(hora - to_date('00010101', 'yyyymmdd')) tempo_login 
  from   t05 
  where  matricula = 839301 
  and    campanha  = 'CLICK 21' 
  group by campanha, matricula, dia 
); 

[Cayo Magno Fontana]

Rafael,

Thank you very much for your help, however, the internal query responds correctly, but the interval is in a number type.

I understand that in External Query, you will convert to the date type, but an error occurs.

Select all

ORA-00933 - SQL command not property ended

follows adaptation I made in the table, only exchanging some names for the originals

Select all

select campanha  
     , matricula  
     , dt_dia  
     , trunc(intervalo)||' dias '  
     ||to_char(to_date('00010101', 'yyyymmdd') + (intervalo), 'hh24:mi:ss')  
from tbl_wbo_log 
(  
  select campanha, matricula, dt_dia, sum(intervalo - to_date('00010101', 'yyyymmdd')) intervalo  
  from   tbl_wbo_log  
  where  matricula = 839301  
  and    campanha  = 'CLICK 21'  
  group by campanha, matricula, dt_dia  
)

[Cayo Magno Fontana]

Rafael, forget the previous comment.

I made the following adaptations:

Select all

select campanha  
     , matricula  
     , dt_dia  
     , trunc(intervalo)||' dias '  
     ||to_char(to_date('00010101', 'yyyymmdd') + (intervalo), 'hh24:mi:ss')  
from  
(  
  select campanha, matricula, dt_dia, sum(intervalo - to_date('00010101', 'yyyymmdd')) intervalo  
  from   tbl_wbo_log  
  where  matricula = 839301  
  and    campanha  = 'CLICK 21'  
  group by campanha, matricula, dt_dia  
)  TBL_LOG

It was great! Thank you very much!

[Cayo Magno Fontana]

Rafael, I opened a new topic with the following problem:

Good evening friends,

First of all, thank you very much for the help in the previous topic. Rafael, thanks, was extremely helpful.

This problem, I believe it is similar to the previous one. In this appointment,

Select all

select  
      MATRICULA,  
      CAMPANHA,  
      SUBSTR(DT_FECHAMENTO, 0, 10) DT_FECHAMENTO 
from    
      tbl_wbo_osrep 
where 
      matricula = matricula  
      and    campanha  = campanha 
      and    status ='Fechado' 
      and    dt_fechamento = dt_fechamento

I have the following return:

Select all

MATRICULA  CAMPANHA            DT_FECHAMENTO 
 
847123	   CLICK 21	21/02/2008 
847123	   CLICK 21	21/02/2008 
847123	   CLICK 21	21/02/2008 
847123	   CLICK 21	21/02/2008 
847123	   CLICK 21	21/02/2008 
847123	   CLICK 21	21/02/2008 
847123	   CLICK 21	21/02/2008 
847123	   CLICK 21	22/02/2008 
847123	   CLICK 21	22/02/2008 
839301	   CLICK 21	22/02/2008 
847123	   CLICK 21	22/02/2008 
847123	   CLICK 21	22/02/2008 
839301	   CLICK 21	22/02/2008

How can we Note, there are several repeated lines.

I wish, in this appointment, an additional column was returned, and in this new query, no line repeat. But in this additional column, it had the number of equal records found in the query. Following the example above, it would be like this:

Select all

MATRICULA  CAMPANHA    DT_FECHAMENTO	QUANTIDADE 
 
847123	   CLICK 21	21/02/2008	    7 
847123	   CLICK 21	22/02/2008	    4 
839301	   CLICK 21	22/02/2008	    2

From now, I leave mine thank you again to the previous question.

could give me a help again?

[ruevers]

Child, first ... that if you were the admin of the forum, I would have changed your topic, should be in SQL and not PL / SQL.
another thing ... again srs ... Sorry for the way ... but I'll talk to him ... sometimes that pulls the ear wants the good ...
already tried to search .
Have you tried other things.
Have you entered Google ?? and typed on the theme ...
I want it to be good ... but let's try something and think before posting something in the forum ... .
I'vê seen your messages ... Every pebble finding, put something here ... Try to turn yourself alone, this is what makes other DBA or other people's system analyst. I bet you can and will see the pleasure that is to solve a problem of these alone, if I can not, I am here to give you the direction and not the answer.
But if you continue like this, give up the computer and will look for another profession.

[Cayo Magno Fontana]

Dear Anderson,

I will give a brief answer to your repression my doubts nor should I because I am super busy but you are the kind of guy you deserve;).

First place: I am not a DBA or I do not intend to, I am a developer in C # and in this directive I am very well, I am in the 8th period of computer science. And "child" as you insist on talking to me at all topics ... hahaha .. well you would like to be one.

Second place: I work in a large telecommunications company, and I'm sure, depending on your area, you are a our customer, and maybe do not even know. I am the only one in the development, design and bank. Therefore "great master" I do not have time to search for the internet my doubts. If I had time, I would have undoubtedly already done a long time ago. I turn to this forum whenever I have doubts and have always had excellent clarifications.

Sometimes weekends, I enter into .NET forums as MSDN-BR. Shot doubts, many of them quite "primary", as it may have been for you, when I posted here. But I never repressed someone for it.

I do not need life lesson to learn about Oracle, "Great Master" keep your advice as much as aggressive to your children, if you have / or have. But if it comes with constructive advice, I will always be at your disposal.

to all of this forum, excuse me for the words, and I apologize for the citizen who began with the aggressions. You always helped me a lot. To you, thank you very much. And this topic continues to open !!

A big hug

[dr_gori]

This is one thing that we moderators always talk about:
* This is a place to learn and teach. It is a place mainly of answers, even if the answer is just a link.

Resets as "Google Search", usually do not help anyone. We ask everyone to ignore the above reviews and return to the main subject of the topic. If this does not occur, we will have to block the topic, and this is a pitiful thing.

Guys, let's take it easy ... If you find some stupid comment, just ignore it! Let's not mess up for nothing, okay?

back to the topic !!!

Try to make a lot by simple!

Select all

select MATRICULA ,CAMPANHA ,DT_FECHAMENTO, count(*) 
from sua_tabela 
group by MATRICULA CAMPANHA DT_FECHAMENTO 

The topic will be moved to the SQL forum!

[Cayo Magno Fontana]

Good afternoon thomas,

before all, thanks for the attention and excuse me for the incident, on my part, this never happened before and I would never repeat the episode here. Lets go to what matters.

I did this query using the group by thus Thomas:

Select all

 
SELECT T1.MATRICULA, T1.CAMPANHA, MAX(T1.DT_INICIA) DT_INICIA, T1.LOGADO 
FROM TBL_WBO_LOGONLINE T1 
GROUP BY T1.MATRICULA, T1.CAMPANHA, T1.LOGADO 

returned this:

Select all

 
839301	CLICK 21	25/02/2008 18:32:43	0 
839301	CLICK 21	25/02/2008 18:32:30	1 
847123	Mundo21	25/02/2008 16:50:34	0 
847123	Mundo21	25/02/2008 21:49:04	1 
847123	CLICK 21	25/02/2008 21:51:12	0 
847123	CLICK 21	25/02/2008 21:50:34	1 

only I need to return me only:

Select all

 
847123	CLICK 21	25/02/2008 21:51:12	0 
839301	CLICK 21	25/02/2008 18:32:43	0 

That is, to achieve these 2 records, Query would be

Select all

 
SELECT T1.MATRICULA, MAX(T1.DT_INICIA) DT_INICIA 
FROM TBL_WBO_LOGONLINE T1 
GROUP BY T1.MATRICULA 

but this way, I do not have the campaign and logged information, as it had in the Previous Query.

How could this be?

[dr_gori]

I did not try, but this should work:

Select all

select x.*, (select CAMPANHA,LOGADO  
             from TBL_WBO_LOGONLINE y  
             where y.matricula=x.matricula  
               and y.dt_inicia=x.dt_inicia 
               and rownum=1) 
from  
  ( 
  SELECT T1.MATRICULA, MAX(T1.DT_INICIA) DT_INICIA  
  FROM TBL_WBO_LOGONLINE T1 
  GROUP BY T1.MATRICULA 
  ) x

I put a sub-select in the select clause that searches the fields you are looking for, based on time!

[Cayo Magno Fontana]

Thomas,

I tried what you guided, I even had something like that, but without the rownum I do not understand what it is for.

also doing its way, returns an error or-00913, which from what I understand, says that the subquery returns more columns than the main query.

But if we set only the other 2 fields, I could not understand.

[dr_gori]

Rownum serves to limit the number of rows in 1. You can not return more than one line.

I think the error is that it was placed more than one field in the top select. Try to put only 1.

[Cayo Magno Fontana]

I can not do anything Thomas, I also tried to do this:

Select all

select x.*, (select MATRICULA, LOGADO 
             from TBL_WBO_LOGONLINE y  
             where y.matricula in (select x.matricula from x) 
               and y.dt_inicia in (select x.dt_inicia from x)  
               AND ROWNUM=1 
               )  
from  
  (  
  SELECT T1.MATRICULA, MAX(T1.DT_INICIA) DT_INICIA  
  FROM TBL_WBO_LOGONLINE T1  
  GROUP BY T1.MATRICULA  
  ) x

Would it be interesting to use an inner join? What do you think?

[Cayo Magno Fontana]

Friends, I got it!

Thomas, thank you very much for the help, I will post the solution if you are interested in someone later.
First, I created a view with the primary SELECT:

Select all

CREATE VIEW VW_AUX_LOGONLINE 
AS 
SELECT T1.MATRICULA, MAX(T1.DT_INICIA) DT_INICIA  
FROM TBL_WBO_LOGONLINE T1  
GROUP BY T1.MATRICULA

later, I set up an inner join of the view with the original table, where we needed the other fields. As well as:

Select all

SELECT V1.MATRICULA, T1.CAMPANHA, V1.DT_INICIA, T1.LOGADO 
FROM TBL_WBO_LOGONLINE T1  
INNER JOIN VW_AUX_LOGONLINE V1  
ON T1.MATRICULA = V1.MATRICULA AND T1.DT_INICIA = V1.DT_INICIA

A hug to all!

[rogenaro]

Cayo,

An alternative solution (in this case, if the same enrollment has two records with the same DT_Initium, only one of them will be returned):

Select all

 
select t1.matricula 
     , t1.campanha 
     , t1.dt_inicia 
     , t1.logado  
from 
( 
  select t1.matricula 
       , t1.campanha 
       , t1.dt_inicia 
       , t1.logado  
       , row_number() over (partition by t1.matricula order by t1.dt_inicia desc) rnbr 
  from   tbl_wbo_logonline t1 
) 
where rnbr = 1 
;