9 Replies Latest reply on Apr 26, 2017 4:08 AM by mercihappy

    Execute SQL dynamic charting - Repairing processes

    mercihappy

      Hi,

       

      I have a complex database, we do repairs like phones laptops etc., and I want to see how many devices does each technician fix a day.

      It is usual that more technicians repair one device (someone analyzes it, another one will do the repair, maybe one will do soldering on the logic board etc.). So I have a table "Worksheets" and a related table "Worksheets_Fix", these are related by 'WorksheetNumber' and 'fk_WorksheetNumber'.

       

      The table "Worksheets_Fix" has the following important fields:

       

      Worksheet no.                    (from the Worksheet)

      Timestamp                         (the current timestamp when someone adds a repairing process)

      Type                                    (there are four types: Fixed, Analyzed, Soldered, Other)

      Technician                          (who added the repairing process)

      Fixing                                  (this one is '1' for all records)

       

      When someone does a repairing process, it will be shown in a portal on the layout "Worksheets" for each worksheet.

       

      I have a chart with X-axis (it shows distinct dates from table Worksheets within a month which is dynamically sat) and Y-axis with the following calculation:

       

       

      Let ( [_Row = 1 ;

       

      _Item = GetValue ( Summarytable::GB_Technicians ; _Row ) ] ;

       

      If ( not IsEmpty ( _Item ) ;

       

      ExecuteSQL (

       

      " SELECT SUM ( \"Fixing\" )

      FROM \"Worksheets_Fix\"

      WHERE \"Date_Yearmonth\" = ? AND \"Technician\" = ?

      GROUP BY \"Dátum_Dátum\"

      "

      ; "" ; "" ; Summarytable::GB_Yearmonth ; _Item )

       

      ) )

       

       

      If technicians would work everyday this could be fine, but there are two days a week they do not work. The result of my SQL statement shows only those dates when there is an existing record with the name of the technician. It would be good to list the other days also with the sum result "0", then my chart would place the data points to the right position.

       

      I attach a photo about my problem. Here I sat the Month to 2017 April, so my chart shows records only from this month. This technician ("A.Zoli") did not work on 2017.04.01., on 2017.04.02., on 2017.04.08 and on 2017.04.09., not as the chart shows.

       

      My other issue is that I do not want to count one device (so one worksheet number) as 2, 3 or more repairing process. (For example if someone analyzes and then repairs the same device under the same worksheet number, it should counted as one fixing not two.)

       

      Thanks for any help.

        • 1. Re: Execute SQL dynamic charting - Repairing processes
          jbrown

          Hello.

          The simplest way to deal with this is to put a record in your Worksheet_Fix table for each day each person didn't work, put a 0 in the count of fixes, and then include that in your ExecuteSQL statement (It would include automatically). This will give you the same number of fix-count data as the dates you have.

           

          You might also want to pull the x-axis data from the same table as the y-axis data; that will ensure the count of items in each axis is the same, and, as long as the two axis data are sorted in the same manner, you will get the data points to line up.

           

          In a charting situation, I prefer to pull the data from a summary table: a static table that holds all the summarized data for a week/day/month/year, whatever time period. This includes a record for when there's 0 of something for the time period..

          • 2. Re: Execute SQL dynamic charting - Repairing processes
            mercihappy

            Dear Jeremy Brown,

             

            Thank you for answering. I've also had this idea, creating a new record, but having these new records everyday for each technician is not a good solution for me. Each person works 5 days a week, but we are open 7 days a week.

             

            X-axis does not matter, it shows the same, i've tried.

             

            To be honest my database is quite big, and it started to run very slowly. That is why I do not want summary tables like this, instead I try execute SQL. It is far faster now.

            • 3. Re: Execute SQL dynamic charting - Repairing processes
              philmodjunk

              A properly designed summary table should improve performance by greatly reducing the number of records needed to produce your chart.

              • 4. Re: Execute SQL dynamic charting - Repairing processes
                beverly

                I don't think I would have everyday for every technician. I think I would have a table of dates that is a "lookup" or even a JOIN so that every date for a given month can be used in the query. Perhaps even with a CASE is needed here.

                 

                What two days? is it always a weekend? or is it different for each technician? or ?? We cannot "create records" with ExecuteSQL(), but we can 'create' data with a few tricks.

                 

                Not enough info for me to give a detailed answer.

                beverly

                • 5. Re: Execute SQL dynamic charting - Repairing processes
                  user19752

                  My other issue is that I do not want to count one device (so one worksheet number) as 2, 3 or more repairing process. (For example if someone analyzes and then repairs the same device under the same worksheet number, it should counted as one fixing not two.)

                   

                  Isn't there a case that a person analyze and another one repair it?

                  • 6. Re: Execute SQL dynamic charting - Repairing processes
                    user19752

                    And do you want to count only first occurrence of worksheet number at first date, or once for each date?

                    • 7. Re: Execute SQL dynamic charting - Repairing processes
                      mercihappy

                      It is not always weekend. We are open every day and we try to optimize the schedule, so every day we have more technicians here, and every technician have 2 days off a week.

                       

                      It is common that a person analyzes and another one repairs the device. But it is also common that one person analyzes it, (then does something else), then he repairs it. I'm happy I've solved this problem like this for each month:

                       

                      ValueCount (

                      ExecuteSQL (

                       

                       

                      " SELECT DISTINCT \"fk_Worksheetnumber\", \"Date\"

                      FROM \"Worksheets_Fix\"

                      WHERE \"Date_Yearmonth\" = ? AND \"Technician\" = ? AND \"Type\" <> ?

                      ORDER BY \"Dátum\"

                      "

                      ; Char ( 9 ) ; "" ; Summarytable::GB_Yearmonth ; "A.Zoli" ; "Other" ) )

                       

                       

                      It is nice because if someone spend time with a device more times a day, it will count as one repairing process. But is he does a process with a device one day, and then he does it again on another day with the same device, it counts as two repairing processes.

                       

                      And for each day finally I made a summary table. I have fields for all our technicians. But I have a table for Parts (like touch screen, battery etc.) with about 700 records, and I want to see how many parts we replace a day for each part. I would not make a summery table with 700 fields, it would be insane. And the problem with this is the same. With SQL data points won't be on the right position. Any ideas?

                      • 8. Re: Execute SQL dynamic charting - Repairing processes
                        user19752

                        I agree 700 fields may be nonsense, but anyway a bar(?) chart with 700 data point (each parts) may not be readable, isn't it?

                        • 9. Re: Execute SQL dynamic charting - Repairing processes
                          mercihappy

                          Absolutely not, but i make my charts to be dynamic, so I can choose one or more parts which will appear. Also I can change the date so my chart will show what I want to see.