3 Replies Latest reply on Nov 23, 2016 8:27 AM by Michael Hesser

    MySQL Query Question

    Kyle Boyce

      Hey guys/gals,

       

      I was wondering if you guys could help me out.

       

      I need a report that answers: "If a requirement is required, do we have the required document on file?"

       

      So I've got a table for our customers that gives me if a checkbox is checked like "Doc Type 1 Required" = 1

       

      Then I've got another table for the documents that we actually have on file to say "Doc Type on file" = 1

       

      My issue is that there are about 5-6 different requirements that could apply to our customers and I need to say if any/all apply do we have the corresponding document.

       

      thanks!

        • 1. Re: MySQL Query Question
          Dmitry Chirkov

          if [requirement] = 1

          then if [on file] = 1 then "all good" else "missing" end

          else "whatever"

          end

           

          Are your tables joined?

          • 2. Re: MySQL Query Question
            Kyle Boyce

            Thank you for the reply Dmitry. I guess my problem is the requirement and the on file can be multiple for each customer for instance. requirement 1&2&... = 1(required) and on file is 1 or 0 for both or neither or just one.

            • 3. Re: MySQL Query Question
              Michael Hesser

              Hello Kyle;

              I think Dmitry Chirkov's answer is correct, you just need to replicate it to cover conditions 1-6.

               

              You can concatenate those results and determine if any failed:

               

              Check For Fails

              IF  CONTAINS([Check 1]+[Check 2]+[Check 3]...,"missing") THEN "At least one check FAILED"

              ELSE "All checks PASSED"

              END

               

              You might also be able to string these together into a single test:

               

              Check For All Fails

              [Requirement 1] = [Requirement 1] * [On File 1] AND

              [Requirement 2] = [Requirement 2] * [On File 2] AND

              [Requirement 3] = [Requirement 3] * [On File 3] AND

              [Requirement 4] = [Requirement 4] * [On File 4] AND

              [Requirement 5] = [Requirement 5] * [On File 5] AND

              [Requirement 6] = [Requirement 6] * [On File 6]

               

              Here I've done some fancy stringing of boolean tests. If a test is not required (0), it negates the results of the test and yields a TRUE for that requirement. And since I'm "adding" them using "AND", even one FALSE will make the entire result FALSE.

              Notes:

              1. You may need to convert dimensions into integers
              2. This also operates on the premise that every requirement and on file will be a 0 or a 1. If you have [On File] results that are null, you can use the ifnull function to reset them 0.

               

              As Dmitri indicated, you might be able to do this more efficiently if your tables are joined. This might allow you to do a single LOD calculation for each customer.