December 26, 2013 at 9:05 am
Dear Friends , appreciates your kind help to prepare the following query i have the following question with all the details thanks for your assistant in advance...
I need to get new report that has the following data :
1- Passengers traveling together in the same Reservation number the column is(Reservation) so new column should contain all the passengers traveling with the main passenger and separated by comma. and each passenger should have his own record and again adding in the new column ( passengers traveling with) the other passengers in the same Reservation. for example:
2- Number of passengers in the same reservation Number will be stored in column called ( Number of passengers)
3- if the flight is via connection point(transit city) i want to put the connection city in new column which is called( transit city) and in case there is more than one transit city then both cities should be mentioned separated by comma.for example passenger is traveling from A TO D with transit of one hour transit in B and some times another 3 hours in C so the transit cities should include B,C. and the departure city should be A AND Arrival city should D. so one row will contain this information / passenger.
3- Different between purchase date and flight date to be stored in new column called ( different between purchase and flight date)
4- arrival date is new requirement which should include the departure date of the second flight for example reservation having passenger traveling from A TO B and then B TO C the departure of the first flight is on 22dec2013 and the departure of the second flight is on 23dec2013 so the arrival will be the last flight departure date.
Here is the table structure:
CREATE TABLE table1(
[Pax ID] [int] NULL,
[Reservation] [int] NULL,
[Surname] [varchar](40) NULL,
[First Name] [varchar](40) NULL,
[Pax Type] [char](1) NULL,
[Flight Date] [smalldatetime] NULL,
[Flight Number] [varchar](10) NULL,
[Board] [varchar](3) NULL,
[Off] [varchar](3) NULL,
[Original Booking Date] [smalldatetime] NULL,
[Last Mod Date] [smalldatetime] NULL
)
here is some sample data:
Pax ID|Reservation|Surname|First Name|Flight Date|Flight Number|Board|Off|Original Booking Date|Last Mod Date
1558611|899842|SULIMAN|ALI|2/1/2013|FO151|RIY|SAH|1/1/2013|1/1/2013
1558612|899842|ALGANADI|HASAN|2/1/2013|FO151|RIY|SAH|1/1/2013|1/1/2013
1558613|899844|ALYAFEE|MOHMMED|1/1/2013|FO160|SAH|TAI|1/1/2013|1/1/2013
1558616|899847|ASSIRI|ahmed|2/1/2013|FO873|AHB|ADE|1/1/2013|1/1/2013
1558617|899847|ASSIRI|ahmed|4/1/2013|FO874|ADE|JED|1/1/2013|1/1/2013
1558628|899847|asiri|alin|2/1/2013|FO873|AHB|ADE|1/1/2013|1/1/2013
1558629|899847|asiri|alin|4/1/2013|FO874|ADE|JED|1/1/2013|1/1/2013
1558618|899848|ALAQWAA|EBRAHEEM|1/1/2013|FO173|HOD|SAH|1/1/2013|1/1/2013
1558621|899850|ALGELHM|HAMID|3/1/2013|FO196|SAH|AAY|1/1/2013|1/1/2013
1558622|899851|ASGHAR|AMER|1/1/2013|FO205|ADE|SAH|1/1/2013|1/1/2013
1558623|899852|ALHALILI|HAMZAH|2/1/2013|FO174|SAH|HOD|1/1/2013|1/1/2013
1558624|899852|ALJAHDARI|GHALIAH|2/1/2013|FO174|SAH|HOD|1/1/2013|1/1/2013
1558625|899853|ABDULLAH|ADEL|1/1/2013|FO173|HOD|SAH|1/1/2013|1/1/2013
1558626|899854|alasmari|mohammed|2/1/2013|FO873|AHB|ADE|1/1/2013|1/1/2013
1558627|899854|alasmari|mohammed|4/1/2013|FO874|ADE|AHB|1/1/2013|1/1/2013
1558631|899856|ALI|FAWAZ|3/1/2013|FO196|SAH|AAY|1/1/2013|1/1/2013
1558631|899856|ALI|FAWAZ|3/1/2013|FO197|AAY|TAI|1/1/2013|1/1/2013
1558631|899856|ALI|FAWAZ|4/1/2013|FO198|TAI|CAI|1/1/2013|1/1/2013
1558624|899851|ASSIRI|ahmed|1/4/2013|FO120|TAI|ADE|1/3/2013|1/3/2013
1558624|899851|ASSIRI|ahmed|4/5/2013|FO121|ADE|TAI|1/3/2013|1/3/2013
Here is the query i reached to up to now:
SELECT [Pax ID],[Reservation],[Surname],
STUFF((SELECT ',' + [Surname] FROM [TABLE1] WHERE [Reservation] = t.[Reservation] AND [Surname] <> t.[Surname] FOR XML PATH('')),1,1,'') AS [pax in same res#],
COUNT(1) OVER (PARTITION BY [Reservation]) AS [# of pax],
DATEDIFF(dd,[Purchase date],[Flight date]) AS [Diff flight date purchase date],
STUFF((SELECT DISTINCT ',' + t1.[Off (Arrival Airport)]
FROM [TABLE1] t1
JOIN [TABLE1] t2
ON t1.[Pax ID] = t2.[Pax ID]
AND t1.[Reservation] = t2.[Reservation]
AND t1.[Off (Arrival Airport)] = t2.[Board (Departure Airport)]
WHERE t1.[Pax ID] = t.[Pax ID]
AND t1.[Reservation] = t.[Reservation]
FOR XML PATH('')
),1,1,'') AS TransitCity,
[Flight date],
STUFF((SELECT ',' + [Flight Number]
FROM [TABLE1]
WHERE [Pax ID] = t.[Pax ID]
AND [Reservation] = t.[Reservation]
FOR XML PATH('')
),1,1,'') AS FLightNo,
(SELECT TOP 1 [Board (Departure Airport)]
FROM [TABLE1]
WHERE [Pax ID] = t.[Pax ID]
AND [Reservation] = t.[Reservation]
ORDER BY [Flight date] ASC) AS DepartCity,
(SELECT TOP 1 [Off (Arrival Airport)]
FROM [TABLE1]
WHERE [Pax ID] = t.[Pax ID]
AND [Reservation] = t.[Reservation]
ORDER BY [Flight date] DESC) AS ArrivalCity,
[Last Mod Date],
[Purchase Date]
FROM (SELECT [Pax ID],[Reservation],[Surname],MAX([Flight Date]) AS [Flight date],MAX([Original Booking Date]) AS [Purchase Date],MAX([Last Mod Date] ) AS [Last Mod Date]
FROM [TABLE1]
GROUP BY [Pax ID],[Reservation],[Surname]) t
Expected output
--------------------------------------------
Pax ID|Reservation|Surname|Firstname|pax in same res# |# of pax|Diff flight date purchase date|TransitCity| Flight date| FLightNo| DepartCity|ArrivalCity|Last Modified Date|Purchase Date| ARRIVAL DATE
1558611|899842|SULIMAN|ALI|ALGANADI,HASAN|2|1|NULL|2/1/2013|FO151|RIY|SAH|1/1/2013|1/1/2013|2/1/2013
1558612|899842|ALGANADI|HASAN|SULIMAN,ALI|2|1|NULL|2/1/2013|FO151|RIY|SAH|1/1/2013|1/1/2013|2/1/2013
1558613|899844|ALYAFEE|MOHMMED|NULL|1|2|NULL|3/1/2013|FO160|SAH|TAI|1/1/2013|1/1/2013|3/1/2013
1558616|899847|ASSIRI|ahmed|ASIRI,ALIN|2|1|ADE|2/1/2013|FO873,FO874|AHB|JED|1/1/2013|1/1/2013|4/1/2013
1558628|899847|asiri|alin|ASSIRI,AHMED|2|1|ADE|2/1/2013|FO873,FO874|AHB|ADE|1/1/2013|1/1/2013|4/1/2013
1558626|899854|alasmari|mohammed|NULL|1|1|NULL|2/1/2013|FO873|AHB|ADE|1/1/2013|1/1/2013|2/1/2013
1558627|899854|alasmari|mohammed|NULL|1|3|NULL|4/1/2013|FO874|ADE|AHB|1/1/2013|1/1/2013|4/1/2013
1558631|899856|ALI|FAWAZ|NULL|1|2|AAY,TAI|3/1/2013|FO196,FO197,FO198|SAH|CAI|1/1/2013|1/1/2013|4/1/2013
1558624|899851|ASSIRI|ahmed|NULL|1|30|NULL|1/4/2013|FO120|TAI|ADE|1/3/2013|1/3/2013|1/4/2013
1558624|899851|ASSIRI|ahmed|NULL|1|33|NULL|4/5/2013|FO121|ADE|TAI|1/3/2013|1/3/2013|4/5/2013
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Please let me know if you need any further details.
thank you and B. Regards
Alhakimi
December 26, 2013 at 9:20 am
here's the data you posted, but cleaned up and in a ready to use format:
the column names in your query did not match the data, and there was no easy way to insert the sample data.
now that it's in a ready-to-consume format, we can start looking at the question itself.
CREATE TABLE table1(
[Pax ID] [int] NULL,
[Reservation] [int] NULL,
[Surname] [varchar](40) NULL,
[First Name] [varchar](40) NULL,
[Pax Type] [char](1) NULL,
[Flight Date] [smalldatetime] NULL,
[Flight Number] [varchar](10) NULL,
[Board] [varchar](3) NULL,
[Off] [varchar](3) NULL,
[Original Booking Date] [smalldatetime] NULL,
[Last Mod Date] [smalldatetime] NULL
)
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 '1558617','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 '1558629','899847','asiri','alin','4/1/2013','FO874','ADE','JED','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','899851','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 '1558627','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'
SELECT [Pax ID],[Reservation],[Surname],
STUFF((SELECT ',' + [Surname] FROM [TABLE1] WHERE [Reservation] = t.[Reservation] AND [Surname] <> t.[Surname] FOR XML PATH('')),1,1,'') AS [pax in same res#],
COUNT(1) OVER (PARTITION BY [Reservation]) AS [# of pax],
DATEDIFF(dd,[Purchase date],[Flight date]) AS [Diff flight date purchase date],
STUFF((SELECT DISTINCT ',' + t1.[Off]
FROM [TABLE1] t1
JOIN [TABLE1] t2
ON t1.[Pax ID] = t2.[Pax ID]
AND t1.[Reservation] = t2.[Reservation]
AND t1.[Off] = t2.[Board]
WHERE t1.[Pax ID] = t.[Pax ID]
AND t1.[Reservation] = t.[Reservation]
FOR XML PATH('')
),1,1,'') AS TransitCity,
[Flight date],
STUFF((SELECT ',' + [Flight Number]
FROM [TABLE1]
WHERE [Pax ID] = t.[Pax ID]
AND [Reservation] = t.[Reservation]
FOR XML PATH('')
),1,1,'') AS FLightNo,
(SELECT TOP 1 [Board]
FROM [TABLE1]
WHERE [Pax ID] = t.[Pax ID]
AND [Reservation] = t.[Reservation]
ORDER BY [Flight date] ASC) AS DepartCity,
(SELECT TOP 1 [Off]
FROM [TABLE1]
WHERE [Pax ID] = t.[Pax ID]
AND [Reservation] = t.[Reservation]
ORDER BY [Flight date] DESC) AS ArrivalCity,
[Last Mod Date],
[Purchase Date]
FROM (SELECT [Pax ID],[Reservation],[Surname],MAX([Flight Date]) AS [Flight date],MAX([Original Booking Date]) AS [Purchase Date],MAX([Last Mod Date] ) AS [Last Mod Date]
FROM [TABLE1]
GROUP BY [Pax ID],[Reservation],[Surname]) t
Lowell
December 26, 2013 at 9:29 am
waiting for your kind help
December 28, 2013 at 4:49 am
alhakimi (12/26/2013)
waiting for your kind help
Sending a "reminder" just a few minutes after the original post implies some kind of urgency on your side...
But you might want to respect that those of us living in the "Western Hemisphere" prefer to celebrate Christmas Holiday's...
If it's really urgent, you might need to contact a consultant...
December 28, 2013 at 8:27 am
Hi Lutz
V. sorry for that , i didn't mean to send a remainder. it was correction of a post i thanked Mr. Lowell for cleanup my question and make it more clear. but was affried that my appreciation will be understood by others as the problem s solved.
that why i edit it and sent that text.
Merry Christmas and happy new year.. enjoy
December 28, 2013 at 6:49 pm
Did you run the SQL he provided ? I did, and at first glance, he seems to have answered your question.
What question do you still have ?
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:
Pax ID|Reservation|First name|Surname|pax in same res#|# of pax|Diff flight date purchase date|TransitCity|Flight date|FLightNo|DepartCity|ArrivalCity|Last Mod Date|Purchase Date
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 :
Pax ID|Reservation|First name|Surname|pax in same res#|# of pax|Diff flight date purchase date|TransitCity|Flight date|FLightNo|DepartCity|ArrivalCity|Last Mod Date|Purchase Date
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
December 29, 2013 at 12:32 pm
Surely you know enough about SQL to add the first name as a field ? It also seems to me like you have issues in part because of how your data is laid out, you should consider your database schema in terms of how you want to use it, so that it lends it self to the tasks at hand.
Are you working for an airline, or are we doing your school assignment for you ? Either way, it would be better if you engaged beyond assigning us tasks, and understood what was going on.
December 30, 2013 at 12:15 am
Dear Christian, thank you v.much for your efforts.
regarding your comments , i am able to add the first Name column as field but i highlighted that to allow you to add in in the [Pax in the same PNR#] which i couldn't make it with both names.
I am working for airline , but unfortunately the data i have is limited to what i have given you. there is no much fields that could allow me to filter my data except what i have , i want to generate report that shows all the data of each passenger in one row , but some cases i can't because the result will be not correct for the round trip flights where the departure city and arrival city will be the same for example if passenger traveling CAIRO - PARIS - CAIRO , If i used the same condition the result will be he traveled from Cairo to Cairo and the transit city was Paris.. which is confusing output.
so thats why i am having those scenarios in parts.
I hope that some one could help me in this.
thank you and B. Regards
Alhakimi
December 30, 2013 at 12:27 am
I am somewhat troubled. You're being paid to work for an airline, but you're really lost as to how to do the job you're being paid to do ?
Everything you're saying seems to me to point towards the database not really being suited to the task at hand. This questions has become a confusing set of edge cases. I'd suggest you need to either fix the database design, or if it can't be changed, work out how to differentiate what you want. It looks to me like a starting point might be to group by departure date, in order to not end up grouping the return flights with the flights going out. Sometimes people do have a connection the next day, but your data base does not have arrival times, or even departure times, these are ways in which it is fundamentally broken.
December 30, 2013 at 12:30 am
I am somewhat troubled. You're being paid to work for an airline, but you're really lost as to how to do the job you're being paid to do ?
Everything you're saying seems to me to point towards the database not really being suited to the task at hand. This questions has become a confusing set of edge cases. I'd suggest you need to either fix the database design, or if it can't be changed, work out how to differentiate what you want. It looks to me like a starting point might be to group by departure date, in order to not end up grouping the return flights with the flights going out. Sometimes people do have a connection the next day, but your data base does not have arrival times, or even departure times, these are ways in which it is fundamentally broken.
December 30, 2013 at 12:32 am
Just to add, I don't think anyone is going to give you the SQL to solve this, because the issues are deeper. If you need help, we'll need to work through it one thing at a time. First things first. Do you have ANY room to improve this database format ? If you had arrival times and departure times, you could work out what's a group of connected flights by how close they are together. As it is, you could try, but with some commercial flights taking 15 hours and landing before they left, or missing a day, that is hard.
December 30, 2013 at 12:39 am
Its true that the database is not designed well, i don't want to go in details why its not designed well but this is the only fields and data we are getting from reservation host that limits the option of having the data easily filtered.
I wasn't the one who designed the database structure to be blamed.. i am only using the data i have.
December 30, 2013 at 12:51 am
Sure - I understand how that can happen. But, what do you propose should be the business rule to work out what is a connecting flight and what is not ? Because that is the first step, not for you to pick out instances and say 'this is two flights', but for you to define business rules by which this can be satisfactorily worked out. Once you have business rules ( which probably the client should define ), then you can work on SQL. Until then, until the client has signed off on what you're going to do, you're just asking for code that works today and not tomorrow. With this DB, I think that can't be avoided, which is all the more reason the client needs to sign off on the rules you are implementing.
If you know the rules, by all means, post them, and we can work on defining them in SQL.
December 30, 2013 at 1:06 am
Thanks , let me do some home work and try to find out how i can make the business rule with the client. and let you know shortly. thank you for your efforts and appologize for being so troubled..
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply