1 2 Previous Next 22 Replies Latest reply on Feb 4, 2016 5:13 AM by disabled_morkus

    Need help w/ ExecuteSQL command

    StylisticGambit

      I work at a doctor's office.  Our database (not designed by me) stores patient test results in a table which includes a different field for each and every test we offer.


      The resulting data looks something like this:

      DATEPATIENTGLUCOSE_RESULTHEMOGLOBIN_A1C_RESULT
      01/07/162285684
      01/07/16228565.1
      01/13/162285692
      01/15/16228561094.8
      01/20/16228564.5

       

      I would like to create an ExecuteSQL command that would generate:

      DATEPATIENTGLUCOSE_RESULTHEMOGLOBIN_A1C_RESULT
      01/07/1622856845.1
      01/13/162285692
      01/15/16228561094.8
      01/20/16228564.5


      I've tried different variations of SELECT DISTINCT, UNION, and JOIN, but I'm not able to find something that does what I want.


      Is such a thing impossible?  Can anyone help?

        • 1. Re: Need help w/ ExecuteSQL command
          siplus

          There's something I don't understand.

           

          If your premise holds, then how can you have the 01/15/16 line that has 2 parameters filled in ? As compared to the first 2 lines , that have same date and patient, but a single parameter filled in per record ?

           

          (Not to say that you should have a examResult table, holding 4 fields: PatientID, date, ParameterID and Value)

          • 2. Re: Need help w/ ExecuteSQL command
            StylisticGambit

            The answer is that each record in this table consists of a single order to our testing company. 

             

            If two tests appear on the same order then they'll share a record, but if they appear on different orders then they'll have different records.

             

            However, when summarizing this data, we don't care whether the tests are on the same order or not.  We only care about the date.

            • 3. Re: Need help w/ ExecuteSQL command
              siplus

              do you import a file coming from your testing company ? Or do you have somebody typing the results in ?

               

              How many parameters (= different tests) you have ?

              • 4. Re: Need help w/ ExecuteSQL command
                StylisticGambit

                No, the results are all entered manually by the nurses.

                 

                I agree, it's a poor design.  This table has been in production since the mid 90's.  It has almost 2000 total fields in order to keep track of all the test results, and over 100,000 records.  To make things worse, there are over 100 different layouts for entering the test results, based on the tests ordered (because you don't want to create a single "ORDER" layout with 2000 fields on it).

                 

                Redesigning this crazy database it is a whole 'nother can of worms.

                • 5. Re: Need help w/ ExecuteSQL command

                  First of all be VERY CAREFUL including any medical information, possibly including "Patient IDs" in a public context. HIPPA regulations are extremely strict (not that anyone ever actually gets penalized, of course).

                   

                  ---

                   

                  As others have said, this is not a good database design, first. The list of tests should be in a child table, not in the main table.

                   

                  However, if you write down what you are trying to "SELECT" in conversational English, the select statement is usually easy to write.

                   

                  If you don't know SQL, check out W3Shools free SQL online course.

                   

                  You just need something like select field1,field2, ... FROM table WHERE <selection conditions> ORDER by <fields to sort by> ASC/DESC.

                   

                  In your above lower screen shot, it looks like you're sorting (ORDER BY) GLUCOSE_RESULT.

                   

                  Good luck.

                   

                  - m

                  • 6. Re: Need help w/ ExecuteSQL command
                    beverly

                    I've read all the comments. I think, perhaps  a SUM() of each of the fields, but GROUP BY the date (and patient) would work here.

                     

                    ExecuteSQL (

                    "

                    SELECT \"DATE\", \"PATIENT\", SUM(glucose_result), SUM(hemoglobin_a1C_result)

                    FROM testResults

                    GROUP BY  \"PATIENT\", \"DATE\"

                    ORDER BY \"PATIENT\", \"DATE\"

                    "

                    ; "" ; "" )

                     

                    But that's based on one and only one value for each test for each date (whether that's two records or not).

                    beverly

                     

                    EDIT: I forgot to add the ORDER BY... (now in there!)

                    • 7. Re: Need help w/ ExecuteSQL command
                      StylisticGambit

                      All of the data that I provided in my first post, including date, patient, and results, were entirely made up by me for the purpose of this discussion.


                      The problem I'm having is that I'm trying to consolidate the results from multiple different records into a single record.  Notice how there are 5 lines in my first table and 4 lines in my second one.  I've tried to do so with JOIN and UNION, but so far I haven't been able to come up with the result that I want.

                      • 8. Re: Need help w/ ExecuteSQL command
                        siplus

                        Our solution includes a Laboratory module. There are about 80 parameters that we import directly from 4 different analysis devices via serial port and keyspan. (divided in chemical, haematologic, coagulation, urine and other). Other tests required outside are imported as pdf in a container. Telling you all this in order to make clear that I feel your pain.

                         

                        Our Lab module is quite complex, too, but the core is a table having a few fields:

                         

                        AnalysisTypeID

                        AnalysisID

                        "" Date

                        "" PatientID

                        "" Value

                        "" ScanID


                        The analysisTypeID delivers the analysis name, measure unit, min and max values which represent normality if the measured value is within.


                        These are ideas for you, should you decide to normalize the whole.


                        ----



                        In your case, with manual input (which is really bad, avoiding it if possible would be great), the nurses should create a new record for every date-patient-parameter tuple, and enter the value.


                        Upon record commit, a script should consolidate the new record into another table, which has only one record for a date-patient combo, and a repeating field with as many repetitions as you have potential exams.  If you have 1000 different exams, it should have 1000 repetitions. The repetition number should be also the ID of a specific test in a fixed Tests database.

                        • 9. Re: Need help w/ ExecuteSQL command
                          StylisticGambit

                          Thank you beverly!  Your result is better than anything I've come up with so far.

                           

                          Unfortunately, it's possible that we could have a patient with two different results (for the same test) on the same date.  But maybe I can find a way to search for that and weed those duplicates out.

                          • 10. Re: Need help w/ ExecuteSQL command
                            beverly

                            Then possibly the AVG() instead of SUM()?

                             

                            Returns the average of the values in a group. Null values are ignored.

                             

                            ExecuteSQL (

                            "

                            SELECT \"DATE\", \"PATIENT\", AVG(glucose_result), AVG(hemoglobin_a1C_result)

                            FROM testResults

                            GROUP BY  \"PATIENT\", \"DATE\"

                            ORDER BY \"PATIENT\", \"DATE\"

                            "

                            ; "" ; "" )

                            • 11. Re: Need help w/ ExecuteSQL command
                              siplus

                              I exclude the need for avg results for a patient, if not for statistical reports.

                              Usually - to make it very simple - you see a patient, measure parameters, give drug prescription/diet/surgery and are interested to see how parameters changed from last time.

                              • 12. Re: Need help w/ ExecuteSQL command
                                beverly

                                agreed, average is likely not the solution. Bad input = bad output, so we are trying to help get around all that. Only OP can  say what works.

                                • 13. Re: Need help w/ ExecuteSQL command
                                  StylisticGambit

                                  Beverly, I thought your average idea might work for our purpose, so I spoke with one of the doctors.  But he definitely doesn't want an average.  Any other ideas?


                                  --------


                                  Thank you for your comments and suggestions, siplus!


                                  I'm already in the process of implementing a lot of what you suggested, but your suggestion of using a repeating field is new to me.  I'm currently doing something similar with a calculation in my ORDERS table that generates a multikey:


                                  FIELDS_IN_USE = Case (

                                    GLUCOSE_RESULT <> "" ; GetFieldName ( GLUCOSE_RESULT ) ;

                                    HEMOGLOBIN_A1C_RESULT <> "" ; GetFieldName ( HEMOGLOBIN_A1C_RESULT ) ;

                                    ...

                                  )


                                  Then, I can use FIELDS_IN_USE in a relationship against a normals table to retrieve a list of relevant normals, for example.


                                  I'll take some time to process your suggestions a little more and may contact you in the future if I'm looking for more advice with the redesign.

                                  • 14. Re: Need help w/ ExecuteSQL command
                                    StylisticGambit

                                    Sorry siplus!  I wasn't thinking properly when I wrote my FIELDS_IN_USE calculation, above.  It actually goes something like this:


                                    FIELDS_IN_USE = If ( GLUCOSE_RESULT <> "" ; GetFieldName ( GLUCOSE_RESULT ) & ¶ ) &

                                    If ( HEMOGLOBIN_A1C_RESULT <> "" ; GetFieldName ( HEMOGLOBIN_A1C_RESULT ) & ¶ ) &

                                    ...

                                    1 2 Previous Next