1 Reply Latest reply on Oct 2, 2017 9:55 AM by Jennifer VonHagel

    Calculation over m:n relation

    Michael Leeming

      Hi there,

       

      I have a database about letter logistics and for a certain lapse of time one district has many, many letters but sometimes there occur customer complaints (that are not always easy to match to a specific letter, so no FK or sth like that). So in that lapse of time that district usually also has some costumer complaints.

       

      Now for amount of letters "a" and costumer complaints "c" I would like to calculate the ratio c/a for a certain district and lapse of time (which should have a linear runtime of a+c).

      My problem is that I can't figure out how to build my Data Source because if I have one SQL Query for letters and one for complaints I can't make a joint on those two tables because that only works for 1:1 relations, doesn't it? And if I use only one table like

       

      select c.*, (select count(*) from letters where district = c.district)

      from complaints c

       

      that also doesn't work in practice for the performance to calculate the table would be c*a (check for the district of each complaint how many letters where sent).

       

      So my question in the end: is there a possibility to build a Data Source with two seperate, independent tables because on the worksheets you always have to decide which table (or Data Source) shall be used, don't you? Or does anyone have another idea how to solve that?

       

      Thank you

       

      Michael

        • 1. Re: Calculation over m:n relation
          Jennifer VonHagel

          Hi Michael,

           

          Could you create a spreadsheet with sample data from letters and complaints? Maybe two sheets in the workbook - each having a table of sample data, and explain which fields are in common. I understand there isn't a direct relationship, but is it that by district and some period of time (day? week? month?) the two data sources can be compared?  With a concrete example, folks can more easily give suggestions.

           

          Best,

          Jennifer