[Solved] Use a Filter Table only when BOOLEAN is TRUE

[dreamquartz]

Hello All,

I have the following tables:
tAudit
tAuthorization
tPerson
tService
For a Person, listed in tPerson, being allowed to provide a Service, I use the following principles:
tAuthorization covers everything to authorize the Person the provide a Service.
tAudit covers information about the Audit, Date Of Audit, and Date of Expiery of the Audit of a Person recorded in tPerson and has Authorization under tAuthorization.
tService describes Services offered.

There are multiple people able to provide the same Service, as long as they have the Authorization and, when required, are having an 'Valid' Audit.

In some cases no Person has to be Audited to provide a specific Service, and in other cases every Person MUST be Audited to be able to provide a specific Service.

A Service, listed in tService.Advanced as TRUE, MUST be Audited. This listed Service is NOT listed again as a Service that is NOT Advanced. All Services are unique.
tService holds both types of Services.

tAudit is therefore a 'Filter' for 'tAuthorization' when a Person needs to be selected for an 'Advanced' Service.

The selection process always has process information from tAuthorization and tAudit, because of the Date Of Expiry to be able to provide a specific Service.
The result set varies per day, per Service, and per Person.

If a Service is 'Advanced' there must be a result set for tAuthorization AND tAudit, because tAudit has to be used to 'filter' tAuthorization. If a Service is not 'Advanced' there must result set for tAuthorization and the result set of tAudit is NULL.

I do not seem to be able to use tAudit as a 'Filter' table when tService.Advanced is set to TRUE, and not not use tAudit when tService.Advanced is set to FALSE or NULL.

Can someone please provide me with some guidance?

Kind regards,

Dream

[F3K Total]

Hello,
i would use two views to define the possible authorizations, like

1. v_POSSIBLE_ADVANCED_AUTHORIZATIONS

   Code: Select all

   SELECT
       "tPerson"."ID_PERSON",
       "tPerson"."NAME",
       "tService"."ID_SERVICE",
       "tService"."SERVICE",
       "tService"."ADVANCED",
       MAX( "tAudit"."DATE_EXPIRE" ) "DATE_EXPIRE",
       DATEDIFF( 'DD', CURRENT_DATE, MAX( "tAudit"."DATE_EXPIRE" ) ) "VALID_DAYS"
   FROM
       "tPerson" 
   JOIN
       "tAudit"
   ON
       "tAudit"."FK_ID_PERSON" = "tPerson"."ID_PERSON"
   JOIN
       "tService"
   ON
       "tAudit"."FK_ID_SERVICE" = "tService"."ID_SERVICE"
   GROUP BY
       "tPerson"."ID_PERSON",
       "tPerson"."NAME",
       "tService"."ID_SERVICE",
       "tService"."SERVICE",
       "tService"."ADVANCED"
   HAVING
       DATEDIFF( 'DD', CURRENT_DATE, MAX( "tAudit"."DATE_EXPIRE" ) ) > 0

2. v_POSSIBLE_AUTHORIZATIONS

   Code: Select all

   SELECT
       "ID_PERSON",
       "NAME",
       "ID_SERVICE",
       "SERVICE",
       "ADVANCED",
       "DATE_EXPIRE",
       "VALID_DAYS"
   FROM
       "SELECT
       "ID_PERSON",
       "NAME",
       "ID_SERVICE",
       "SERVICE",
       "ADVANCED",
       "DATE_EXPIRE",
       "VALID_DAYS"
   FROM
       "v_POSSIBLE_ADVANCED_AUTHORIZATIONS"
   UNION SELECT
       "tPerson"."ID_PERSON",
       "tPerson"."NAME",
       "tService"."ID_SERVICE",
       "tService"."SERVICE",
       "tService"."ADVANCED",
       NULL "DATE_EXPIRE",
       NULL "VALID_DAYS" 
   FROM
       "tPerson",
       "tService"
   WHERE
       "tService"."ADVANCED" = FALSE"
   UNION SELECT
       "tPerson"."ID_PERSON",
       "tPerson"."NAME",
       "tService"."ID_SERVICE",
       "tService"."SERVICE",
       "tService"."ADVANCED",
       NULL "DATE_EXPIRE",
       NULL "VALID_DAYS" 
   FROM
       "tPerson",
       "tService"
   WHERE
       "tService"."ADVANCED" = FALSE

   to serve a form,

followed by a query:

Code: Select all

SELECT 
    "FK_ID_PERSON",
    "FK_ID_SERVICE",
    "AUTHORIZED",
    CASE WHEN "DATE_EXPIRE" IS NULL THEN CHAR( 10004 ) WHEN DATEDIFF( 'DD', CURRENT_DATE, "DATE_EXPIRE" ) > 0 THEN CHAR( 10004 ) ELSE CHAR( 10008 ) END "VALID?"
FROM
    "tAuthorization"

to display the current status.
R

[dreamquartz]

Great,

This was exactly what I was looking for.
Consider it to be solved.

Dream