Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: need urgent help

Re: need urgent help

From: Bhishm <bhishms_at_gmail.com>
Date: Wed, 21 Nov 2007 21:24:06 -0800 (PST)
Message-ID: <57e3f34f-2eaf-4198-8071-4120b9d07660@s8g2000prg.googlegroups.com>


On Nov 20, 7:01 pm, DA Morgan <damor..._at_psoug.org> wrote:
> Bhishm wrote:
> > Hi,
>
> > I am creating a attendance sheet software for inhouse use.
>
> > my data is like this:-
>
> > ------------------------------------------------------------------------------------------------
> > | name | login time | logout
> > time |
> > ------------------------------------------------------------------------------------------------
> > | a | 2007-11-10 12:00:00 | 2007-11-10
> > 16:00:00 |
> > ------------------------------------------------------------------------------------------------
> > | b | 2007-11-10 15:00:00 | 2007-11-10
> > 18:00:00 |
> > ------------------------------------------------------------------------------------------------
>
> > My requirement:-
>
> > I want to generate an hourly report like this:-
> > --------------------------------------------------------------------------------------------
> > date time range total people logged
> > in
> > ---------------------------------------------------------------------------------------------
> > 2007-11-10 0 -2 0
> > --------------------------------------------------------------------------------------------
> > 2007-12-10 2-4 0
> > --------------------------------------------------------------------------------------------
> > .
> > .
> > -------------------------------------------------------------------------------------------
> > 2007-11-10 12-14 1
> > -------------------------------------------------------------------------------------------
> > 2007-11-10 14-16 2
> > --------------------------------------------------------------------------------------------
> > 2007-11-10 16-18 1
> > ------------------------------------------------------------------------------------------------
> > .
> > .
> > ---------------------------------------------------------------------------------------------
> > 2007-11-10 22-24 0
> > --------------------------------------------------------------------------------------------
>
> > This is what I want to creat , but I don't know how can I generate
> > such kind of report.
>
> > Can you please guide me for the same. Please reply urgently.
>
> > Thanks & Regards,
> > Bhishm
>
> Your problem is not urgent and I, for one, am offended at your abuse
> of the term.
>
> No production database is down.
> No house is on fire.
> No one is drowning.
>
> Apologize and you may get some help.
>
> That said you don't offer up any of the required DDL or any evidence
> that you have attempted to solve the problem yourself. Rather it looks
> like you are either a student trying to cheat or someone unqualified
> for their job. In either case no one should give you the answer.
> --
> Daniel A. Morgan
> Oracle Ace Director & Instructor
> University of Washington
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

Dear Daniel,

I apologize and I also tried a lot of things before writing to the group.

I tried to solve the problem in following ways but the problem I face is:-
i) Using Group By
I only get time where somebody logged in and not the interval where he is logged in, in an interval. Ex.
Data:-  



| a | 2007-11-10 12:00:00 | 2007-11-10 16:00:00 |  

| b | 2007-11-10 15:00:00 | 2007-11-10 18:00:00 |  

Result:-  


  2007-11-10   a     12-14                              1
 
-------------------------------------------------------------------------------------------
 2007-11-10    b     14-16                              1
 
--------------------------------------------------------------------------------------------

This is wrong as I am not able to get in what all intervals he is logged in.

I can get in what inteval people logged in, but not the intervals where he remains logged in.

ii) I tried left join, but didn't succeed.

These are the two things I tried.

Thanks & Regards,
Bhishm Received on Wed Nov 21 2007 - 23:24:06 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US