I need some help in constructing an advanced sql select statement for report generation. This is my case:
I need to produce a report for workers without wages for a period of time from certain department.
My print report screen would have 2 dropdown fields - period and department. In order to identify those workers without wages, i need to search from a few tables, for example, harvesting , jobcard, transport to make sure that the worker name/id doesn't appear inside the record and the total working hour is 0 if his record exist within the selected date.
This seems to involve lots of selection, where shouldi start from. First select all the active users from the vallid department, then only filter based on whether his record exist in those other tables. Please advice. Thanks in advance.
Hi there Paul, thanks for your reply. It was not easy to generate the diagram, and ive reverse generated one sample - removing lots of other details. Hope it helps a bit. Ive attached together with this post.
Hrmstaff is the workers table, while the rest are tables which would be used to identify the working hours of staff. For example, each of those tables (other than hrmstaff) have fields like 'worker' (pfmjobcard), harvestName (pfmharvestdetail)..etc
Note: Having some problem attaching the file at the moment