On Thu, 2005-09-01 at 13:45 -0300, Mauro Formigoni Junior wrote:

> d_dude_2003 wrote:
> > Hi there guys,
> >
> > I have a table like
> >
> > ID INTEGER
> > TIMEIN TIME
> > TIMEOUT TIME
> >
> > populates with records like
> > 1 Time1 <Null>
> > 2 <Null> Time2
> > 3 Time3 <Null>
> > 4 <Null> Time4 etc.
> >
> > If i use
> >
> > SELECT *
> > WHERE (TIMEIN > Param1) AND (TIMEIN < Param2) AND (TIMEOUT > Param1)
> > AND (TIMEOUT < Param2)
> >
> > will it return the right records and NULL is not being compared here?
> >
> > Hope u get it :)
> >
> > Thanx alot,
> > Eugene.
> >
>
> NULL is not major nor minor nor equal to any value, this select will not
> return nothing.
>
> To work with NULL values use the coalesce function like:
>
> select * where coalesce(timein, '01/01/1900') > Param1
>
> The engine replaces the NULL values found in timein field with the
> second parameter in the function ('01/01/1900' in this case).
>
> So happens the following:
>
> timein is null?
> - if yes: '01/01/1900' > Param1?
> - if no: timein > Param1?
>
> if the "second" condition is True then return the record.
>
> Mauro
>

As written, this query will return no records with nulls in timein or
timeout. Any value comparison against a null returns false. It will
return only the rows for people who both checked in and out between the
given times. I would be suspicious of this query because it could
result in unpaid workers.

(same query as above reformatted for my readability)

SELECT *
WHERE (TIMEIN > Param1)
AND (TIMEIN < Param2)
AND (TIMEOUT > Param1)
AND (TIMEOUT < Param2)



To return rows where people have checked in but not out ...

SELECT *
WHERE (TIMEIN > Param1)
AND (TIMEIN < Param2)
AND (TIMEOUT IS NULL)



To return all rows with erroneous entries, where people have checked out
but not checked in ...

SELECT *
WHERE (TIMEOUT > Param1)
AND (TIMEOUT < Param2)
AND (TIMEIN IS NULL)




To return all rows where people were present within a specific time
frame

SELECT *
WHERE ((TIMEIN > Param1)
AND (TIMEIN < Param2))
or ((TIMEOUT > Param1)
AND (TIMEOUT < Param2))