Vipar Vipar - 4 months ago 7
SQL Question

Find rows that are the same, combine their results across multiple tables

So, my question title might not be correct but I need some help in figuring this out.

So to paint you the picture. I have the following model made in MSSQL (Currently ignore the

SR_
tables):

enter image description here

To be brief; The data put here is used to generate a report about customer satisfaction on the flights of the company I work for. I have to generate reports in Excel based that show a few things:


  • What is the average Rating of this week, this year on the flight?

  • What is the average Rating of the same time period, on that very same route the year before?

  • How many surveys is this based on that week, this year?

  • How many surveys is this based on that week, the year before?



And I have to do this for (at the moment) 13 questions. It'll be a long report but that's what they asked for. Here is an example of what that looks like in Excel:

enter image description here

Now here is where I start getting a bit overwhelmed on how to tackle the queries I need to make for the database. I have a result such as this:

enter image description here

The first batch is from this year (I know it says 2013 we are still migrating data from a previous system) and the second batch is from the year before. Now, the year before doesn't have any comparable data to any of the flights from the current year so they will be discarded in the report, and not counted. That's just how it is. But from the batch this year, you'll notice that 2 of the flights are the same route. This means I have to get all the surveys those two flights point to (SurveyData->Surveys->AnswersRating) and put them together so they are treated as one flight at the end.

I cannot figure out how I'd approach this in SQL so that I can find those same routes and pair them with those from the year before (if any) but before I pair them, put their surveys together so they are treated as one single route.

If I query the AnswersRating table to get all the answers to the surveys that were filled out by customers on that flight, you see the below:

enter image description here

Currently, for every 13 questions you have 1 survey but really all the questions you see out to the right can only appear once in a survey.

I hope I was clear enough about my problem. Any clue as to how I'd go about it?

Answer

why not evaluate all ID's with the same route via cte and then get the Details for this / previous year? Might look something like this:

WITH cte AS(
  SELECT ID
    FROM ...
    GROUP BY AirportFrom, AirportTo
    [Where...]
)
SELECT *
  FROM ... AS a
  JOIN cte AS b ON a.id = b.id
Comments