Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Complex Query Expand / Collapse
Author
Message
Posted Thursday, December 26, 2013 9:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, January 25, 2014 8:57 AM
Points: 12, Visits: 67
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
Post #1525982
Posted Thursday, December 26, 2013 9:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:29 PM
Points: 12,741, Visits: 31,053
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1525988
Posted Thursday, December 26, 2013 9:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, January 25, 2014 8:57 AM
Points: 12, Visits: 67
waiting for your kind help
Post #1525990
Posted Saturday, December 28, 2013 4:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 9:38 AM
Points: 6,908, Visits: 12,624
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...




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1526285
Posted Saturday, December 28, 2013 8:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, January 25, 2014 8:57 AM
Points: 12, Visits: 67
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
Post #1526299
Posted Saturday, December 28, 2013 6:49 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 9:42 PM
Points: 57, Visits: 96
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 ?
Post #1526320
Posted Sunday, December 29, 2013 7:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, January 25, 2014 8:57 AM
Points: 12, Visits: 67
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
Post #1526345
Posted Sunday, December 29, 2013 12:32 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 9:42 PM
Points: 57, Visits: 96
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.
Post #1526361
Posted Monday, December 30, 2013 12:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, January 25, 2014 8:57 AM
Points: 12, Visits: 67
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
Post #1526398
Posted Monday, December 30, 2013 12:27 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 9:42 PM
Points: 57, Visits: 96
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.

Post #1526399
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse