Very Computer

Hi There

I am a relative newbie to the more complex SQL statements flying
around my head currently. First off I would like to sketch the
scenario and then in Structured english layout what I want to do. any
hints, tips, suggestions will be greatly appreciated.

The company I work for logs all entries and departures from our
building each day. From his welter of information we would like to
exract the first login and last logout for the day. The problem is
that all smokers use their access cards to go outside to smoke, and
reps go and c clients. furthermore on the floor (we are a printing
company) we have shifts, and at anypoint a shift can span 2 days (thus
the query cannot only b focussed on 2day). somebody can also work
overtime (and even work for 2 days non stop) and we need to be certain
it did happen else they won't get their overtime pay!!!

currently the query we use basically gets the first login and the last
logout but accross 2days (thus making it xtremely inaccurate).

in structred english what i need to do is the following:
Select the firstlogin of a specified date and the last logout
(following this first login) where the datediff is greater than 4 hr
before the next login on the system.

Hope this makes some sense.
:D:D:D
johannes

If you have already a database send the DDL and some examples, should be
easier to help you.

If you want to solve the problem on your own, just try to use a case stement
and figur out, if the employee did a shift spanning two days or only one
day. (you can also use a subquery, depends on your design)

HTH, Jens S?meyer.

Quote:> Hi There

> I am a relative newbie to the more complex SQL statements flying
> around my head currently. First off I would like to sketch the
> scenario and then in Structured english layout what I want to do. any
> hints, tips, suggestions will be greatly appreciated.

> The company I work for logs all entries and departures from our
> building each day. From his welter of information we would like to
> exract the first login and last logout for the day. The problem is
> that all smokers use their access cards to go outside to smoke, and
> reps go and c clients. furthermore on the floor (we are a printing
> company) we have shifts, and at anypoint a shift can span 2 days (thus
> the query cannot only b focussed on 2day). somebody can also work
> overtime (and even work for 2 days non stop) and we need to be certain
> it did happen else they won't get their overtime pay!!!

> currently the query we use basically gets the first login and the last
> logout but accross 2days (thus making it xtremely inaccurate).

> in structred english what i need to do is the following:
> Select the firstlogin of a specified date and the last logout
> (following this first login) where the datediff is greater than 4 hr
> before the next login on the system.

> Hope this makes some sense.
> :D:D:D
> johannes