RE: Complex Query – SQLServerCentral

Say Hey Kid

Points: 687

December 29, 2013 at 7:40 am

Dear Friends

thank you for your kind assistant, i had run the query which reduce the gap and near to complete my needed result. but i noticed the following changes after i added more sample data and corrected the previous data. so i will appreciate if you may replace those data sample with the previous data and note the observations mentioned below under the results i got from the query i ran:

Corrected Sample Data:

INSERT INTO table1([Pax ID],[Reservation],[Surname],[First Name],[Flight Date],[Flight Number],[Board],[Off], [Original Booking Date],[Last Mod Date])

SELECT '1558611','899842','SULIMAN','ALI','2/1/2013','FO151','RIY','SAH','1/1/2013','1/1/2013' UNION ALL

SELECT '1558612','899842','ALGANADI','HASAN','2/1/2013','FO151','RIY','SAH','1/1/2013','1/1/2013' UNION ALL

SELECT '1558613','899844','ALYAFEE','MOHMMED','1/1/2013','FO160','SAH','TAI','1/1/2013','1/1/2013' UNION ALL

SELECT '1558616','899847','ASSIRI','ahmed','2/1/2013','FO873','AHB','ADE','1/1/2013','1/1/2013' UNION ALL

SELECT '1558616','899847','ASSIRI','ahmed','4/1/2013','FO874','ADE','JED','1/1/2013','1/1/2013' UNION ALL

SELECT '1558628','899847','asiri','alin','2/1/2013','FO873','AHB','ADE','1/1/2013','1/1/2013' UNION ALL

SELECT '1558628','899847','asiri','alin','4/1/2013','FO874','ADE','JED','1/1/2013','1/1/2013' UNION ALL

SELECT '1558616','899847','ASSIRI','ahmed','2/10/2013','FO875','JED','ADE','1/1/2013','1/1/2013' UNION ALL

SELECT '1558616','899847','ASSIRI','ahmed','2/10/2013','FO876','ADE','AHB','1/1/2013','1/1/2013' UNION ALL

SELECT '1558628','899847','asiri','alin','2/10/2013','FO875','JED','ADE','1/1/2013','1/1/2013' UNION ALL

SELECT '1558628','899847','asiri','alin','2/10/2013','FO876','ADE','AHB','1/1/2013','1/1/2013' UNION ALL

SELECT '1558618','899848','ALAQWAA','EBRAHEEM','1/1/2013','FO173','HOD','SAH','1/1/2013','1/1/2013' UNION ALL

SELECT '1558621','899850','ALGELHM','HAMID','3/1/2013','FO196','SAH','AAY','1/1/2013','1/1/2013' UNION ALL

SELECT '1558622','899861','ASGHAR','AMER','1/1/2013','FO205','ADE','SAH','1/1/2013','1/1/2013' UNION ALL

SELECT '1558623','899852','ALHALILI','HAMZAH','2/1/2013','FO174','SAH','HOD','1/1/2013','1/1/2013' UNION ALL

SELECT '1558624','899852','ALJAHDARI','GHALIAH','2/1/2013','FO174','SAH','HOD','1/1/2013','1/1/2013' UNION ALL

SELECT '1558625','899853','ABDULLAH','ADEL','1/1/2013','FO173','HOD','SAH','1/1/2013','1/1/2013' UNION ALL

SELECT '1558626','899854','alasmari','mohammed','2/1/2013','FO873','AHB','ADE','1/1/2013','1/1/2013' UNION ALL

SELECT '1558626','899854','alasmari','mohammed','4/1/2013','FO874','ADE','AHB','1/1/2013','1/1/2013' UNION ALL

SELECT '1558631','899856','ALI','FAWAZ','3/1/2013','FO196','SAH','AAY','1/1/2013','1/1/2013' UNION ALL

SELECT '1558631','899856','ALI','FAWAZ','3/1/2013','FO197','AAY','TAI','1/1/2013','1/1/2013' UNION ALL

SELECT '1558631','899856','ALI','FAWAZ','4/1/2013','FO198','TAI','CAI','1/1/2013','1/1/2013' UNION ALL

SELECT '1558624','899851','ASSIRI','ahmed','1/4/2013','FO120','TAI','ADE','1/3/2013','1/3/2013' UNION ALL

SELECT '1558624','899851','ASSIRI','ahmed','4/5/2013','FO121','ADE','TAI','1/3/2013','1/3/2013'

Current Output data:

1558611|899842|ALI|SULIMAN|HASAN|2|31|NULL|2/1/2013 0:00|FO151|RIY|SAH|1/1/2013 0:00|1/1/2013 0:00

1558612|899842|HASAN|ALGANADI|ALI|2|31|NULL|2/1/2013 0:00|FO151|RIY|SAH|1/1/2013 0:00|1/1/2013 0:00

1558613|899844|MOHMMED|ALYAFEE|NULL|1|0|NULL|1/1/2013 0:00|FO160|SAH|TAI|1/1/2013 0:00|1/1/2013 0:00

1558616|899847|ahmed|ASSIRI|alin,alin,alin,alin|2|90|ADE,AHB,JED|4/1/2013 0:00|FO873,FO874,FO875,FO876|AHB|JED|1/1/2013 0:00|1/1/2013 0:00

1558628|899847|alin|asiri|ahmed,ahmed,ahmed,ahmed|2|90|ADE,AHB,JED|4/1/2013 0:00| FO873,FO874,FO875,FO876|AHB|JED|1/1/2013 0:00|1/1/2013 0:00

1558618|899848|EBRAHEEM|ALAQWAA|NULL|1|0|NULL|1/1/2013 0:00|FO173|HOD|SAH|1/1/2013 0:00|1/1/2013 0:00

1558621|899850|HAMID|ALGELHM|NULL|1|59|NULL|3/1/2013 0:00|FO196|SAH|AAY|1/1/2013 0:00|1/1/2013 0:00

1558622|899861|AMER|ASGHAR|NULL|1|0|NULL|1/1/2013 0:00|FO205|ADE|SAH|1/1/2013 0:00|1/1/2013 0:00

1558624|899851|ahmed|ASSIRI|NULL|2|92|ADE,TAI|4/5/2013 0:00|FO120,FO121|TAI|TAI|1/3/2013 0:00|1/3/2013 0:00

1558623|899852|HAMZAH|ALHALILI|GHALIAH|2|31|NULL|2/1/2013 0:00|FO174|SAH|HOD|1/1/2013 0:00|1/1/2013 0:00

1558624|899852|GHALIAH|ALJAHDARI|HAMZAH|2|31|NULL|2/1/2013 0:00|FO174|SAH|HOD|1/1/2013 0:00|1/1/2013 0:00

1558625|899853|ADEL|ABDULLAH|NULL|1|0|NULL|1/1/2013 0:00|FO173|HOD|SAH|1/1/2013 0:00|1/1/2013 0:00

1558626|899854|mohammed|alasmari|NULL|1|90|ADE,AHB|4/1/2013 0:00|FO873,FO874|AHB|AHB|1/1/2013 0:00|1/1/2013 0:00

1558631|899856|FAWAZ|ALI|NULL|1|90|AAY,TAI|4/1/2013 0:00|FO196,FO197,FO198|AAY|CAI|1/1/2013 0:00|1/1/2013 0:00

the needed output :

1558611|899842|ALI|SULIMAN|HASAN|2|31|NULL|2/1/2013 0:00|FO151|RIY|SAH|1/1/2013 0:00|1/1/2013 0:00

1558612|899842|HASAN|ALGANADI|ALI|2|31|NULL|2/1/2013 0:00|FO151|RIY|SAH|1/1/2013 0:00|1/1/2013 0:00

1558613|899844|MOHMMED|ALYAFEE|NULL|1|0|NULL|1/1/2013 0:00|FO160|SAH|TAI|1/1/2013 0:00|1/1/2013 0:00

1558616|899847|ahmed|ASSIRI|Alin Asiri|2|90|ADE|4/1/2013 0:00|FO873,FO874|AHB|JED|1/1/2013 0:00|1/1/2013 0:00

1558628|899847|alin|asiri|ahmed Assiri|2|90|ADE|4/1/2013 0:00|FO873,FO874|AHB|JED|1/1/2013 0:00|1/1/2013 0:00

1558616|899847|ahmed|ASSIRI|Alin Asiri|2|90|ADE|2/10/2013 0:00|FO875,FO876|JED|AHB|1/1/2013 0:00|1/1/2013 0:00

1558628|899847|alin|asiri|ahmed Assiri|2|90|ADE|4/1/2013 0:00|FO873,FO874|AHB|JED|1/1/2013 0:00|1/1/2013 0:00

1558618|899848|EBRAHEEM|ALAQWAA|NULL|1|0|NULL|1/1/2013 0:00|FO173|HOD|SAH|1/1/2013 0:00|1/1/2013 0:00

1558621|899850|HAMID|ALGELHM|NULL|1|59|NULL|3/1/2013 0:00|FO196|SAH|AAY|1/1/2013 0:00|1/1/2013 0:00

1558622|899861|AMER|ASGHAR|Null|1|0|NULL|1/1/2013 0:00|FO205|ADE|SAH|1/1/2013 0:00|1/1/2013 0:00

1558624|899851|ahmed|ASSIRI|NULL|1|92|null|4/5/2013 0:00|FO120|TAI|ADE|1/3/2013 0:00|1/3/2013 0:00

1558624|899851|ahmed|ASSIRI|NULL|1|92|null|4/5/2013 0:00|FO120|ADE|TAI|1/3/2013 0:00|1/3/2013 0:00

1558631|899856|FAWAZ|ALI|NULL|1|90|AAY,TAI|4/1/2013 0:00|FO196,FO197,FO198|SAH|CAI|1/1/2013 0:00|1/1/2013 0:00

1558623|899852|HAMZAH|ALHALILI|GHALIAH|2|31|NULL|2/1/2013 0:00|FO174|SAH|HOD|1/1/2013 0:00|1/1/2013 0:00

1558624|899852|GHALIAH|ALJAHDARI|HAMZAH|2|31|NULL|2/1/2013 0:00|FO174|SAH|HOD|1/1/2013 0:00|1/1/2013 0:00

1558625|899853|ADEL|ABDULLAH|NULL|1|0|NULL|1/1/2013 0:00|FO173|HOD|SAH|1/1/2013 0:00|1/1/2013 0:00

1558626|899854|mohammed|alasmari|NULL|1|90|NULL|4/1/2013 0:00|FO873|AHB|ADE|1/1/2013 0:00|1/1/2013 0:00

1558626|899854|mohammed|alasmari|NULL|1|90|NULL|5/1/2013 0:00|FO874|ADE|AHB|1/1/2013 0:00|1/1/2013 0:00

the query given by Mr. lowell was almost ready , it only needs some more work to become perfect to the need. so the query did excellent result for one way tickets. but the round trip and multi city booking still need to be modified.

the requirement is to have each passenger flight information in one row so i will need who flown with him in same reservation and same flight , number of passenger in same reservation, transit city incase if its through middle points , and first departure city , last arrival city AND THE FLIGHT NUMBERS.

the main requirement of this report is to put each passenger flying one flight from city to another ( it could be direct or via transit city/ cities) information in one row. where the return flight for same passenger should be in another row because its different flight.

so to make it more clear if one reservation has one / two passengers going from FRANKFURT - PARIS OUTPUT SHOULD BRING ME TWO RECORDS ONE RECORD FOR EACH PASSENGER

and if one reservation has one / two passengers going from DELHI - NEW YORK AND BACK TO DELHI IT SHOULD HAVE EACH PASSENGER WILL BE HAVING HIS OWN ROW FOR THE ON GOING FLIGHT AND OTHER ROW FOR THE RETURN FLIGHT AND EACH FLIGHT.

how the query should be built :

1- for single flight the query is fine.

2- Round trip flight if the reservation number has two records having the same PAX ID it should check the first row departure city if its equal to the second row arrival city then its round trip and should have two rows one row for the first flight and second row for the return flight this for each passenger in the same reservation should have the same information .. this scenario is like FRANKFURT - DELIHI AND RETURN IS DELHI - FRANKFURT SO THERE IS NO TRANSIT CITY.

3- multi city which means traveling throught many cities to reach to the destination example: FRANKFURT - AMMAN THEN AMMAN - DUBAI / IT COULD BE RETURN FLIGHT TOO LIKE DUBAI - AMMAN THEN AMMAN TO FRANKFURT.

IN THIS CASE IF RESERVATION IS HAVING MORE THAN TWO [PAX ID] IN THE SAME RESERVATION MEANS THE FLIGHT IS MULTI CITY SO IT HAS TWO CANDATIONS:

1- SHOULD CHECK IF THE FIRST ROW DEPARTURE CITY IS NOT EQUAL TO THE SECOND ROW ARRIVAL CITY IF TRUE THEN THE TRANSIT CITY IS FIRST ROW ARRIVAL CITY AND THEN IT SHOULD CHECK IF THE SECOND ROW ARRIVAL CITY IS EQUAL TO THE THIRD ROW DEPARTURE CITY MEANS THE TRANSIT CITY IS THE THIRD ROW ARRIVAL CITY.

SO LET ME KNOW IF YOU NEED ANY CLARIFICAITION.

THANK YOU

HERE ARE MORE ABOUT the observations:

case1

1- First Name column is missing and its needed to put be stored in the [pax in same res#] column along with the surname the Example ( HASAN ALGANADI ) in the first row.

2- In row Number 4 (Reservation=899847) there is some additional data now. the current scenario i want to have cases like passenger is traveling : AHB-ADE-JED AND THEN GOING BACK JED-ADE-AHB.

how can i make the going FLIGHT in one row and return flight is one row. and the needed output for this observation is :

1558616|899847|ahmed|ASSIRI|Alin Asiri|2|90|ADE|4/1/2013 0:00|FO873,FO874|AHB|JED|1/1/2013 0:00|1/1/2013 0:00

(main thing here is the transit city is only ADE and the flight number are FO873,FO874 departure city is AHB and Arrival City is JED)

1558628|899847|alin|asiri|ahmed,ahmed,ahmed,ahmed|2|90|ADE|4/1/2013 0:00|FO873,FO874|AHB|JED|1/1/2013 0:00|1/1/2013 0:00

(main thing here is the transit city is only ADE and the flight number are FO873,FO874 departure city is AHB and Arrival City is JED)

1558616|899847|ahmed|ASSIRI|Alin Asiri|2|90|ADE|2/10/2013 0:00|FO875,FO876|JED|AHB|1/1/2013 0:00|1/1/2013 0:00

(main thing here is the transit city is only ADE and the flight number are FO875,FO876 departure city is JED and Arrival City is AHB)

1558628|899847|alin|asiri|ahmed Assiri|2|90|ADE|4/1/2013 0:00|FO873,FO874|AHB|JED|1/1/2013 0:00|1/1/2013 0:00

(main thing here is the transit city is only ADE and the flight number are (FO873,FO874) and departure city is AHB and Arrival City is JED)

case 2:

when there is round trip flight for example : The last Two rows (Reservation=899851) flight is GOING FROM TAI TO ADE AND THE RETURN IS ADE - TAI

so each flight the ongoing and the return must have different row. the output for this scenario / case should be like this:

1558624|899851|ahmed|ASSIRI|AMER|2|92|null|4/5/2013 0:00|FO120|TAI|ADE|1/3/2013 0:00|1/3/2013 0:00

1558624|899851|ahmed|ASSIRI|AMER|2|92|null|4/5/2013 0:00|FO120|ADE|TAI|1/3/2013 0:00|1/3/2013 0:00

the above two records the transit city is null because its direct flight from TAI - ADE And the return is also direct from ADE - TAI. SO THE FLightNo will be like above.

case 3: the [reservation=899856] the [Board] should be the first row departure city so in this case the correct data should be :

1558631|899856|FAWAZ|ALI|NULL|1|90|AAY,TAI|4/1/2013 0:00|FO196,FO197,FO198|SAH|CAI|1/1/2013 0:00|1/1/2013 0:00

( the only observation here is the departure city that should be SAH) rest is v. fine..

hope i was able to explain the points up . please let me know if you have any clarification.

thank you and . Regards

Alhakimi