Sum of hours

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Fri, 22 Feb 2008 5:42 pm

Good afternoon friends, through a web system in .NET, are inserted in a table in oracle, user records over time they are logged under certain sub-aplicações.
I would like to make the sum of the time fields from the records in the table.
Example: records in the table: LICENSE PLATE CAMPAIGN DAY HOUR CLICK 21 01/01/847123 2/21/2008 00:01:37 839301 2/22/2008 Mundo21 0001 01/01/0001 00:01:23 CLICK 839301 2/22/2008 01/21/01 00:01:16 847123 2/22/2008 Mundo21 0001 01/01/0001 00:02:41 CLICK 847123 2/22/2008 01/21/01 0001 00:01:03 839301 21 2/22/2008 01 CLICK/01/0001 00:01:48 CLICK 847123 2/22/2008 01/21/01 0001 00:05:27 the field campaign are the sub-aplicações, registration, obviously, are the users ' registration day represents the day they were logged in, and finally the hour (is default date 01/01 com/0001 to insert next to each insert porque não was able to insert undated) in a query in a campaign X, user Y, and Z-day, I return more than one row. For example: CLICK 847123 01 2/22/2008 21/01/0001 00:01:03 CLICK 847123 2/22/2008 01/21/01 0001 00:05:27 would like to know how do I return a single rows with the hours together, example, in this case above, would be: 847123 2/22/2008 01/21 CLICK 01/0001 00:06:30 I thank attention.
Cayo Magno Fontana
Location: ES

Poston Fri, 22 Feb 2008 6:33 pm

Assuming that always in the field time will appear on the date 01/01/0001, and that you want to group your results by Registration, campaign and Day:
Code: 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
Location: Londrina - PR

Rafael O. Genaro

Poston Fri, 22 Feb 2008 6:35 pm

ops ... had a minor error in the query..

Code: 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
);
rogenaro
Location: Londrina - PR

Rafael O. Genaro

Poston Fri, 22 Feb 2008 7:08 pm

Rafael, thank you very much for your help, however, the inner query responds correctly, but the range is under type NUMBER.

I understand that in the outer query, will do the conversion for the DATA type, an error occurs.

ORA-00933-SQL command not property ended.

Follow adaptation that did in the table, just changing a few names to select campaign documents, registration, dt_dia, trunc (range) || ' days ' ||to_char (to_date (' 00010101 ', ' yyyymmdd ') + (range), ' hh24: mi: ss ') from tbl_wbo_log (select campaign, registration, dt_dia, sum (range-to_date (' 00010101 ', ' yyyymmdd ')) range from tbl_wbo_log where registration = 839301 and = campaign ' CLICK ' group by 21 campaign, license plate, dt_dia)
Cayo Magno Fontana
Location: ES

Poston Fri, 22 Feb 2008 7:13 pm

Rafael, forget the previous comment.

I made the following adjustments: select campaign, registration, dt_dia, trunc (range) || ' days ' ||to_char (to_date (' 00010101 ', ' yyyymmdd ') + (range), ' hh24: mi: ss ') from (select campaign, registration, dt_dia, sum (range-to_date (' 00010101 ', ' yyyymmdd ')) range from tbl_wbo_log where registration = 839301 and = campaign ' CLICK ' group by 21 campaign, license plate, dt_dia) TBL_LOG came out great! Thank you so much!
Cayo Magno Fontana
Location: ES

Poston Fri, 22 Feb 2008 9:25 pm

Rafael, open a new topic with the following problem: good evening friends, first of all, thank you very much for your help in the previous topic. Rafael, thanks a lot, was extremely useful.

This problem I believe is similar to the previous one. In this query, select REGISTRATION, campaign, SUBSTR (DT_FECHAMENTO, 0, 10) DT_FECHAMENTO from tbl_wbo_osrep where registration = registration and campaign = campaign and status = ' Closed ' and dt_fechamento = dt_fechamento have the following return: DT_FECHAMENTO CAMPAIGN 847123 REGISTRATION 2/21/2008 847123 CLICK 21 CLICK 21 CLICK 21 847123 2/21/2008 2/21/2008 847123 2/21/2008 847123 CLICK 21 CLICK 21 CLICK 21 847123 2/21/2008 2/21/2008 847123 CLICK 2/21/2008 21
847123 CLICK 2/22/2008 CLICK 847123 2/22/2008 21 21 839301 21 847123 2/22/2008 CLICK CLICK CLICK 847123 2/22/2008 21 2/22/2008 21 839301 CLICK 2/22/2008 21 As we can notice, there are several repeated lines.

I wish, in this query, it returned an additional column, and this new query, no rows from repeating itself. However this additional column, had the same number of records found in the query. Following the example above would look like this: DT_FECHAMENTO 847123 QUANTITY CAMPAIGN REGISTRATION CLICK 21 CLICK 21 847123 7 2/21/2008 2/22/2008 4 839301 21 2/22/2008 2 CLICK right away, leave my thanks again to the previous question.

Could you give me a help again?
Cayo Magno Fontana
Location: ES

Poston Mon, 25 Feb 2008 10:39 pm

Child, first ... that if I were the admin of the forum, I would have swapped their topic, should be in SQL and PL/SQL.
Another thing ... again SRs ...sorry the way ... but I'm going to talk to him ... sometimes pulling the ear mean well ...
I tried to search.
have you tried other things.
I entered in google??and typed on the topic ...
I want it to be good ... but let's try something and pensquisar before you post something on the forum ... Let's go up the level.
I have seen your posts ... every Pebble, post something here ... try for yourself, this is what makes it different a DBA or system analyst from other people. I bet you can and will see what a pleasure it is to solve a problem like this alone, if I can't, I'm here to give you the direction and not the answer.
But if it continues like this, give up the computer and go find another profession.
ruevers
Location: sp


Poston Tue, 26 Feb 2008 1:42 pm

Dear Anderson, I will give a brief answer to your repression my doubts shouldn't because I'm super busy but you're the kind of guy who deserves;).

First: I'm not a DBA and I don't intend to, I'm developer in c # and in this policy I'm very well, I'm in the 8th period of computer science. And ' child ' as insists on talking to me in all topics ... hahaha ... well I'd like to go back to being a.

Second place: work at a large telecommunications company, and I'm sure that, depending on your region, you are a client of ours, and they don't even know. I'm the only one in the part of design and Development Bank. So \"Grand Master\" I don't have time to search the internet my doubts. If I had more time, I would have, no doubt, already done a long time ago. Turn to this forum whenever I have questions and I always have had excellent clarification.

Sometimes, weekends, go into .NET forums like the MSDN-Br. Shot questions, many of them quite \"primary\", as it could have been you, pa when I posted here. But NEVER repress someone for it.

I don't need a life lesson for how to learn about oracle, \"Grand Master\" save your advice as much as aggressive to their children, should have/or has. But if you come with constructive advice, I will always be available.

TO EVERYONE ON THIS FORUM, I'M SORRY FOR THE WORDS, AND I APOLOGIZE FOR THE CITIZEN THAT BEGAN WITH THE ATTACKS. YOU'vê HELPED ME A LOT. TO YOU MY THANKS. AND THIS THREAD IS STILL OPEN!!

A BIG HUG
Cayo Magno Fontana
Location: ES

Poston Tue, 26 Feb 2008 3:43 pm

This is something we moderators always talked among ourselves: * This is a place to learn and teach. Is a place mainly REPLIES, even if the answer is only one LINK.

Answers like \"search in google\", typically do not help anyone. We ask everyone to ignore the above comments and get back to the main subject of the topic. If that doesn't occur, we're going to have to lock the topic, and this is something regrettable.

Guys, let's just relax ... If you find some stupid comments, just ignore him! Let's not make trouble for nothing.

BACK TO THE TOPIC!

try doing a simple group.
Code: 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!
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Tue, 26 Feb 2008 3:58 pm

Hello Thomas, first of all, thank you for your attention and sorry for the incident, on my part, it never happened before and I would like that ever we repeat the episode here. Let's cut to the Chase.

Did this query using group by so Thomas:
Code: 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
called me back this:
Code: 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 need to return only:
Code: Select all
847123   CLICK 21   25/02/2008 21:51:12   0
839301   CLICK 21   25/02/2008 18:32:43   0
IE, to get these 2 records, the query would be
Code: Select all
SELECT T1.MATRICULA, MAX(T1.DT_INICIA) DT_INICIA
FROM TBL_WBO_LOGONLINE T1
GROUP BY T1.MATRICULA
but this way, I don't have the information CAMPAIGN and logged in, as it had in the previous query.

How could it?
Cayo Magno Fontana
Location: ES

Poston Tue, 26 Feb 2008 4:11 pm

I have not tried, but this should work:
Code: 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
put a sub-select in the SELECT clause that searches the fields you're looking for, based on time!
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Tue, 26 Feb 2008 4:28 pm

Thomas, I tried what you advised, I had done something similar, but without the rownum, I don't understand what good.

Doing it your way, also returns an error ORA-00913, which as I understand it, says that the subquery returns more columns than the main query.

But if we check only another 2 fields, I couldn't figure out.
Cayo Magno Fontana
Location: ES

Poston Tue, 26 Feb 2008 4:54 pm

The rownum serves to limit the number of rows in 1. Cannot return more than one row.

I think the error is that was placed over a field in the select top. try putting just 1.
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Tue, 26 Feb 2008 5:08 pm

I'm not getting at all Thomas, tried to also do this way:
Code: 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
it would be interesting to use an inner join? What do you think?
Cayo Magno Fontana
Location: ES

Poston Tue, 26 Feb 2008 5:30 pm

Friends, I did it!

Thomas, thank you very much for your help, I will post the solution if it is interest of someone later.

First, I created a view with the select primary:
Code: 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
then rode an inner join of view with the original table, where we needed of other fields. Thus:
Code: 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!
Cayo Magno Fontana
Location: ES

Poston Tue, 26 Feb 2008 6:46 pm

Cayo, A workaround (in this case, if the same license plate has two records with the same dt_inicio, only one of them will be returned):
Code: 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
;
rogenaro
Location: Londrina - PR

Rafael O. Genaro



Return to SQL

Who is online

Users browsing this forum: No registered users and 1 guest