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
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:
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:
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:
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?