Re: sql reporting problem from oracle database

MARTINJAMESYOUNG_at_HOTMAIL.COM (Martin Young) wrote:

>Hi
>
>I'm new to sql / plsql and i'm trying to produce a report that can
>output results to an excel worksheet.
>
>Our occupational health department use an application called OPAS. the
>back end of this is an oracle database.
>
>I need to produce a report that will identify all valid restrictions
>from the restrictions view.
>
>the problem being that the database creates a new record if a
>restriction is lifted see below for example;
>
> Name Event type
> martin young Restriction Applied: No Pushing/Pulling
> martin young Restriction Lifted: No Pushing/Pulling
> martin young Restriction Applied: Bending and Twisting
> martin young Restriction Applied: Limited Use of Upper Limb
> martin young Restriction Applied: Lifting and Handling
> martin young Restriction Applied: Overhead Work
> martin young Restriction Lifted: Limited Use of Upper Limb
>
>I have created the following code to return all of the restrictions
>how can i modify it to just display applied restrictions?
>
>SELECT DISTINCT
> A.PIN,
> A.FULLNAME AS "NAME",
> A.COSTCENTRE AS "COST CENTRE",
> A.D_S__ATTENDANCE_DATE AS "RESTRICTION APPLIED ON",
> A.D_S__DUE_DATE AS "RECALL DATE",
> A.S__EVENT_TYPE AS"RESTRICTION TYPE",
> a.P__EMPLOYMENT_STATUS
>FROM
> cass.opasrestrictions_view A
>WHERE
> A.PIN <>'1' AND
> a.S__EVENT_TYPE LIKE 'Restriction%' AND
> a.P__EMPLOYMENT_STATUS NOT LIKE 'Terminated%'
>
>Ideally this then could automatically e-mail to a specified number of
>people in scheduled tasks.
>
>Any help at all is greatly appreciated
>
>Martin

With that design it will be difficult to construct a Sql statement to only efficiently show those restrictions still in place.
It would mean parsing the contents of the event_type to separate the action ( the stuff before the : ) and the type of restriction ( after the : ) then if , for identical types, there is a matching pair of action entries ( one applied and one lifted) eliminate that set from the returned data.( Be sure to check for reapplication of a restriction of the same type, as well..probably need to factor in the date of the action.)

If a redesign is possible, try to get create a table with one record for each employee as a master table  with a link to their restriction history and current status as a detail table. Received on Fri Sep 19 2003 - 09:41:07 CDT