Hi

I think the following does what your require. Note changes in Capitals, and extra brackets :)

select h.fullname, s.shiftdate, p.pchin, p.pchout, s.shift_begin,
s.shift_end

from scheduled_work s

LEFT join pchup p on p.empno = s.empno

join empinfo_history h on h.empno = s.empno

where P.EMPNO IS NULL OR (

(cast(p.pchin as date) = s.shiftdate) and

((cast(p.pchin as time) > s.shift_begin) or

(cast(p.pchout as time) < s.shift_end)) )

order by s.shiftdate




Rob Martin
Software Engineer

phone +64 03 377 0495
fax +64 03 377 0496
web www.chreos.com

Wild Software Ltd



Clay Shannon wrote:

>I know that sounds anti-logical/non-sensical, but here goes:
>
>
>
>I need to see who either punched in late or punched out early:
>
>
>
>select h.fullname, s.shiftdate, p.pchin, p.pchout, s.shift_begin,
>s.shift_end
>
>from scheduled_work s
>
>join pchup p on p.empno = s.empno
>
>join empinfo_history h on h.empno = s.empno
>
>where (cast(p.pchin as date) = s.shiftdate) and
>
>((cast(p.pchin as time) > s.shift_begin) or
>
>(cast(p.pchout as time) < s.shift_end))
>
>order by s.shiftdate
>
>
>
>but also need to see which records exist in scheduled_work which do not have
>corresponding records in pchup-in other words, the person did not punch in
>or out at all (did not show up for work on a day they were schedule to
>work).
>
>
>
>How to do?
>
>
>
>Clay Shannon,
>
>Dimension 4 Software
>
>
>
>
>
>[Non-text portions of this message have been removed]
>
>
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>Visit http://firebird.sourceforge.net and click the Resources item
>on the main (top) menu. Try Knowledgebase and FAQ links !
>
>Also search the knowledgebases at http://www.ibphoenix.com
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>Yahoo! Groups Links
>
>
>
>
>
>
>
>
>
>