Re: need urgent help

On Nov 22, 12:24 am, Bhishm <bhis..._at_gmail.com> wrote:
> 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

HINT: Try resolving the problem in steps.

The key to this is understanding that the result set of a SELECT can be considered a table.
You may already have the first step you need, but basically try to think of the problem in parts.

  HTH,
   Ed Received on Thu Nov 22 2007 - 01:47:43 CST