Alpha Software Mobile Development Tools:   Alpha Anywhere    |   Alpha TransForm subscribe to our YouTube Channel  Follow Us on LinkedIn  Follow Us on Twitter  Follow Us on Facebook
Devcon 2020 Goes Virtual! LEARN MORE >

Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

Compare Two Database Fields To Get Percentage

Collapse
X
Collapse
 
  • Page of 2
  • Filter
  • Time
  • Show
Clear All
new posts
Previous 1 2 template Next
    #1

    Compare Two Database Fields To Get Percentage

    I want to compare a series of two database fields together to see if they match. I want to do this for about 16 of them and get a percentage that match, so for example if 5 of them match then the percentage calculated would be 31%. The set that is one database table would need to be compared to many in another table so I would get a percentage that matches like series A compared to series AA is 20% and series A compared to series AB is 25% and so on. What would be the best way to do this in Alpha? Would I need to store the percentages in a database field or have it done on the fly and display the percentages? I hope this makes sense in what I am trying to do!
    Tags: None
    #2
    Re: Compare Two Database Fields To Get Percentage

    Seems like a view would be a great place to start thereby skipping all irrelevant fields in the parent tables when performing the calculations and shorten display time.
    NWCOPRO: Nuisance Wildlife Control Software My Application: http://www.nwcopro.com "Without forgetting, we would have no memory at all...now what was I saying?"

    Comment

      #3
      Re: Compare Two Database Fields To Get Percentage

      With a view, would it be ran when the user clicks on something that would display the percentages? Would a view be better and less sql execution time or a stored procedure that runs every so often that does the comparison and stores the percentages in database fields?

      Comment

        #4
        Re: Compare Two Database Fields To Get Percentage

        Depends how you do it. In MySQL i would create the view there which you can think of as its own table. So if there are ONLY two fields in the view the actual lookup would be faster than a join (generally speaking of course) since the call would be to a table with ONLY two fields rather than all the fields in both tables. The math end could then be done client side to get the numbers your looking for. So this part of it is more about speed, Once you have the values, maybe in a list? Then you could do the math via javascript. That part I probably couldn’t help you with but I think its a good foundation to do what you want, and again once you have the values I think others could help you work out the math part and how you might want to display it, etc.
        NWCOPRO: Nuisance Wildlife Control Software My Application: http://www.nwcopro.com "Without forgetting, we would have no memory at all...now what was I saying?"

        Comment

          #5
          Re: Compare Two Database Fields To Get Percentage

          Steven,

          You can do this in a view, it will be calculated on the fly when you select from it, just like any regular table, and unless you have a huge number of rows it will be instantaneous

          you could also write a stored procedure and execute that to return the result set, but it's probably not needed here

          Nils

          Comment

            #6
            Re: Compare Two Database Fields To Get Percentage

            It would be comparing about 21 fields per row in one table to 21 fields per row in another table to get a percentage of the fields that match. Initially, it wouldnt be many rows but if it started getting used a lot then it could grow to thousands of rows or more, hopefully that will happen :) . Given that information do you still think a view would be better than stored procedure? I appreciate your input!!!

            Comment

              #7
              Re: Compare Two Database Fields To Get Percentage

              I think you need to post some sample data and the query used now in order to answer that. It the tables are properly indexed, such an operation should be quite simple. Which database are you using?

              Comment

                #8
                Re: Compare Two Database Fields To Get Percentage

                I agree you might want to post more info - I initially thought you were comparing 2 fields not 21 but again if there are 40 fields in each table a view might be better.
                NWCOPRO: Nuisance Wildlife Control Software My Application: http://www.nwcopro.com "Without forgetting, we would have no memory at all...now what was I saying?"

                Comment

                  #9
                  Re: Compare Two Database Fields To Get Percentage

                  What do I need to post the 2 tables I want to compare? I have some other tables that I need to get some information from but not to do the actual comparison.

                  Comment

                    #10
                    Re: Compare Two Database Fields To Get Percentage

                    For sake of argument: Let's say you have Table A and Table B fields A1, A2, A3, A4 -> A10, and A5 + B1, B2, B3, B4, -> B10.
                    Can you explain what you mean by percentage of fields match?
                    A1 matches B1? OR A1-A10 matches B1-B10?

                    Are you pulling all the fields in from both tables? Are there the same number of rows in each table, in other words, what defines 100% match?
                    NWCOPRO: Nuisance Wildlife Control Software My Application: http://www.nwcopro.com "Without forgetting, we would have no memory at all...now what was I saying?"

                    Comment

                      #11
                      Re: Compare Two Database Fields To Get Percentage

                      Originally posted by swest View Post
                      What do I need to post the 2 tables I want to compare? I have some other tables that I need to get some information from but not to do the actual comparison.
                      Steven

                      In general, the more info you post here the easier it is to help, remember that the members in this forum are all users like you and we answer questions outside of work, so the more help and info you provide, the better chance of a useful answer

                      If you compare table1.a = table2.a , table1.b = table2.b and have a fixed number of columns, a view is simple with some case selects for conditions, and maybe a nested view to calculate percentage totals if thats easier
                      If you have a variable number of fields to compare, it's still possible, but you would need a stored procedure and probably some dynamic sql

                      Nils

                      Comment

                        #12
                        Re: Compare Two Database Fields To Get Percentage

                        I am comparing One Row In TableA that has 21 columns TO MANY ROWS In TableB that has the same 21 columns. However, I want the percentage that match of the one row in TableA 21 columns to each of TableB's rows 21 columns. I also want to sort my list view based on the highest percentages first to lowest percentages last. I was thinking that a view might be best at first but if a lot of users use the system then a view might cause a lot more sql execution time? If there gets to be 1000s of rows in TableB that has to be compared each time the user clicks on that one row in TableA then wouldnt that cause a sql to work too much, slowing the app? With a stored procedure couldnt I just have it run after a new row is added to TableA or TableB or just run the stored procedure at a certain processor load level and store each percentage in a field that I can sort the List View from? Does this give more detail of what I am looking to do, or just more confusing?

                        Comment

                          #13
                          Re: Compare Two Database Fields To Get Percentage

                          Steven,

                          just to be sure, you compare table_A.col_A to table_B.col_A , table_A.col_B to table_B.col_B, table_A.col_C to table_B.col_C etc.

                          I would still make the view , I know it's 21 subselects for each row, but Sql server is really fast at this, and unless you have very large tables, all of your view data will probably in memory, and that will be very fast, a 1000 rows should certainly never be a problem, maybe 10 or 100 million.
                          I just made a test with 11 columns on a row with 787 rows total, and it doesn't matter if I select 1 row or all rows, execution time is less than 1 second

                          This is the Sql I used
                          -- Query for view, compares column col_A in table_A with col_A in table_B for all rows in table_B
                          SELECT Id,
                          Col_A, ( select COUNT(*) from table_B B where B.Col_A = A.Col_A) AS Col_A_COUNT,
                          Col_B, ( select COUNT(*) from table_B B where B.Col_B = A.Col_B) AS Col_B_COUNT,
                          FROM Table_A A


                          The approach with a procedure to calculate and store the result is just a maintenance nightmare, you will need to run it on every insert, update and delete.
                          You could also make a stored procedure to calculate the percentage for 1 row with the row id as input, but that is just for 1 row, and you would need to call it for every row to compare.

                          You should make some testdata, create the sql for the view, and take a look at the execution plan and make sure it's using indexes etc.

                          As always the key to success is test, test and test.

                          Comment

                            #14
                            Re: Compare Two Database Fields To Get Percentage

                            Yeah I think what you are saying about the comparison is correct with what I am trying to do. Another question about the sql view, if in my list control I have a detail view that shows some other information like a picture, would it be easy to add in the percentages that are produced from the sql view and add it to list control's detail view that the user will actually see?

                            Comment

                              #15
                              Re: Compare Two Database Fields To Get Percentage

                              Yes, I think so,
                              I would just make a view and add the columns there, the view can even be updatable, just specify the primary key. It can't be to complex for the update to work, but a view that uses a table and uses subselects for the extra columns should work fine. A join may also work, just test it.
                              If you don't need updates the view can be as complex as you like

                              Comment

                              Previous 1 2 template Next
                              Working...
                              X