CODEWITHSUNDEEP
sqlsql-server
OmniOwl
OmniOwl

Reputation: 5709

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:

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?

Upvotes: 0

Views: 43

Answers (2)

Turan
Turan

Reputation: 142

You'll need to use correlated queries.

For example: what is the average rating of the same time period, on that very same route the year before?

This solution might not be exactly correct for your database, but it will indicate you how to solve it.

Select AVG(rating), Flightperiod, FlightID
from Answersrating ar
join Surveys s on s.ID = ar.surveryID
join SurveryData sd on s.surveyDataID = sd.ID
where Flightperiod = (Select Flightperiod 
                      from SurveyData sd2 
                      where sd.flightID = sd2.flightID 
                        and Flightperiod = DATEADD(year, 1 ,CurDate())) --today 1 year ago. You might need to alter this date to suit your needs.

This will return the AVG rating for every flight (ID), but where the flightperiod is one year ago.

Upvotes: 1

Tyron78
Tyron78

Reputation: 4197

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

Upvotes: 1

Related Questions