Very complex query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Very complex query

Author

Topic

alhakimi
Starting Member

23 Posts

Posted - 2013-12-10 : 05:53:19

Dear friends,
this question was answered before but i recieved error due to incomplete script so again asking this question
I have the following table has the passengers informations here is the complete fields:

SELECT [Pax ID], Reservation, Surname, [First Name], [Pax Type], Phone, Mobile, Email, Passport,
[Fare Class], [Flight Date], [Board (Departure Airport)], [Off (Arrival Airport)], [Flight Number], [Original Booking Date],
[Leg Status], [Last Mod Date], [Booking Origin (How flight booked)], [Travel Agency Name], [Province of Travel Agent],
[User Base (Closest Airport to Travel Agent)], [lng_Res_Segments_Id_Nmbr (Unique ID; not of interest)], [Flight Status],
lng_Res_Pax_Group_Id_Nmbr, RSDesc, Sumpayment, sumcharge
FROM dbo.[passengerstable]

i am writing some fields here with the example
[Pax ID]| Reservation| Surname| [Flight Date] | [Flight Number] | [Board (Departure Airport)]|[Off (Arrival Airport)]|Last Modified date | [Original Booking Date]
100001 | 10001 | Mike| 10/10/2013 | RJ100 | AMM | DEL |10/10/2013 15:10 | 08/10/2013
100002 | 10001 |Jason| 10/10/2013 | RJ100 | AMM | DEL |10/10/2013 15:10 | 08/10/2013
100003 | 10001 |amit| 10/11/2013 | RJ100 | AMM | DEL |10/10/2013 15:10 | 08/10/2013
100004 | 10002 | jack| 12/11/2013 | RJ200 | Del | AMM |11/11/2013 20:00 | 1/11/2013
100004 | 10002 | Jack| 13/11/2013 | RJ300 | AMM | CAI |11/11/2013 01:00 | 1/11/2013

what i want is to have some details from this table in to another view to looks like the below table where there is some time passengers traveling on the same reservation # i want them in new field(All of them in one field separated by , or |) and another field shows the number of passengers and i want to know the time difference between the purchase date and flight date also some time same passenger goes from example from first city to the second city with stay in middle city(transit city) ex. a-b-c so i want the B city to be in different field some time i could have two transit cities like (a-b-c-d) so i want b and in this field:
i also need the first departure city and last arrival city to be instead of departure city column and arrival city column like below:
[Pax ID]| Reservation| Surname| Pax in same Res#| Difference between flight date and purchase date| Transit city|
100001 |10001 | Mike|Jason,amit |3 | 2 days | |
100002 |10001 | Jason|Mike,amit |3 | 2 days | |
100003 |10001 | Amit|Jason,Mike |3 | 2 days | |
100004 | 10002 | jack| |1 | 11 days | AMM |

[Flight Date] | [Flight Number] | [Board (Departure Airport)]|[Off (Arrival Airport)]|Last Modified date | [Original Booking Date]
10/10/2013|RJ100 |AMM |DEL |10/10/2013 15:10|08/10/2013
10/10/2013|RJ100 |AMM |DEL |10/10/2013 15:10|08/10/2013
12/11/2013|RJ200,RJ300 |DEL |CAI |11/11/2013 20:00|1/11/2013

i want the complete query to add create this view that includes the previous data along with the new data in one view.

please let me know if you have any clarification.

thank you and B. Regards

Alhakimi

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2013-12-12 : 11:15:12

Hi

I think you might want to do something like this:

http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server

G

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-12 : 13:39:39

[code]
SELECT [ Pax ID ],[ Reservation],[ Surname],
STUFF((SELECT ',' + [ Surname] FROM PassengerTest 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 PassengerTest t1
JOIN PassengerTest 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 PassengerTest
WHERE [ Pax ID ] = t.[ Pax ID ]
AND [ Reservation] = t.[ Reservation]
FOR XML PATH('')
),1,1,'') AS FLightNo,
(SELECT TOP 1 [ Board (Departure Airport) ]
FROM PassengerTest
WHERE [ Pax ID ] = t.[ Pax ID ]
AND [ Reservation] = t.[ Reservation]
ORDER BY [Flight date] ASC) AS DepartCity,
(SELECT TOP 1 [ Off (Arrival Airport) ]
FROM PassengerTest
WHERE [ Pax ID ] = t.[ Pax ID ]
AND [ Reservation] = t.[ Reservation]
ORDER BY [Flight date] DESC) AS ArrivalCity,
[Last Modified date],
[Purchase Date]
FROM (SELECT [ Pax ID ],[ Reservation],[ Surname],MAX([ Flight Date ]) AS [Flight date],MAX([ Original Booking Date ]) AS [Purchase Date],MAX([Last Modified date ] ) AS [Last Modified date]
FROM PassengerTest
GROUP BY [ Pax ID ],[ Reservation],[ Surname]) t

output
--------------------------------------------
Pax ID Reservation Surname pax in same res# # of pax Diff flight date purchase date TransitCity Flight date FLightNo DepartCity ArrivalCity Last Modified date Purchase Date
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
100001 10001 Mike Jason,amit 3 61 NULL 2013-10-10 00:00:00.000 RJ100 AMM DEL 2013-10-10 15:10:00.000 2013-08-10 00:00:00.000
100002 10001 Jason Mike,amit 3 61 NULL 2013-10-10 00:00:00.000 RJ100 AMM DEL 2013-10-10 15:10:00.000 2013-08-10 00:00:00.000
100003 10001 amit Mike,Jason 3 62 NULL 2013-10-11 00:00:00.000 RJ100 AMM DEL 2013-10-10 15:10:00.000 2013-08-10 00:00:00.000
100004 10002 jack NULL 1 334 AMM 2013-12-11 00:00:00.000 RJ200 , RJ300 Del AMM 2013-11-11 20:00:00.000 2013-01-11 00:00:00.000

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

alhakimi
Starting Member

23 Posts

Posted - 2013-12-17 : 08:45:08

Dear ,

thank you , its really excellent query, i have some observations and feedback ,appreciates your kind assistant.

I have small change to the query i need to add the column [First name ]so i need the first name and last name to be in the column passengers in the same booking.

[Pax ID]| Reservation| Surname|[First Name] [Flight Date] | [Flight Number] | [Board (Departure Airport)]|[Off (Arrival Airport)]|Last Modified date | [Original Booking Date]
this is just one way ticket so the output should be two records one for each record
100001 | 10001 | Mike | Jack | 10/10/2013 | RJ100 | JFK | FRA |10/10/2013 15:10 | 08/10/2013

here Two passengers in the same booking number and one way ticket so the output should be Two rows one row showing the passenger is Jason Amit and the passenger traveling with him Jason Sam and the other row should be Jason Sam as main passenger and the travelling with him Jaosn Amit.
100002 | 10003 | Jason | Amit | 10/10/2013 | RJ500 | JED | BOM |10/10/2013 15:10 | 08/10/2013
100008 | 10003 | Jason | Sam | 10/10/2013 | RJ500 | JED | BOM |10/10/2013 15:10 | 08/10/2013

Here i have two passengers is going from Del - AMM then AMM - CAI means two flights and the transit city is AMM and the departure city is DEL - and the arrival city is CAI in the previous query it was showing the transit city is AMM and the departure city is AMM too while it was DEL means it took the departure of the second row instead of the first row. and flight numbers should be RJ200, RJ300 while currently( RJ200,RJ200,RJ300, RJ300) means duplicate flight number. also some time two passengers are having
in this case the output will have Two rows instead of 4 rows one row for each passenger also in pr

100004 | 10002 | jack | Rohit | 12/11/2013 | RJ200 | Del | AMM |11/11/2013 20:00 | 1/11/2013
100004 | 10002 | Jack | Rohit | 13/11/2013 | RJ300 | AMM | CAI |11/11/2013 01:00 | 1/11/2013
100005 | 10002 | Ibrahim| Naji | 12/11/2013 | RJ200 | Del | AMM |11/11/2013 20:00 | 1/11/2013
100005 | 10002 | Ibrahim| Naji | 13/11/2013 | RJ300 | AMM | CAI |11/11/2013 01:00 | 1/11/2013

this is round trip booking with Two flights number RJ800, RJ900 there will be no transit city for each record as its direct CDG - ORY and the return is ORY - CDG. there should be two records here one record for each flight.
100006 | 10006 | Akbar | Ahmed | 24/11/2013 | RJ800 | CDG | ORY |20/11/2013 10:00 | 10/11/2013
100006 | 10006 | Akbar | Ahmed | 28/11/2013 | RJ900 | ORY | CDG |20/11/2013 10:00 | 10/11/2013

here the flight is Del- AMM then AMM - CAI and then CAI - CDG the transit city will be two cities which is here AMM and CAI so it the transit column should have both cities seprated with comma and one row to include the departure city which is Del and arrival city which is here CDG also the the flight numbers which is RJ200, RJ300, RJ400
it should have Two rows in this scenario one for each passenger as both are traveling on same Booking number.
100009 | 10010 | karim | Saeed | 12/12/2013 | RJ200 | Del | AMM |11/11/2013 20:00 | 1/11/2013
100009 | 10010 | Karim | Saeed | 13/12/2013 | RJ300 | AMM | CAI |11/11/2013 01:00 | 1/11/2013
100010 | 10010 | Karim | Saeed | 13/12/2013 | RJ400 | CAI | CDG |11/11/2013 20:00 | 1/11/2013

please let me know if you have any doubt in my query.
thank you and B. Regards

Alhakimi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-17 : 09:02:08

Show us what you want as output for each of the above cases.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

alhakimi
Starting Member

23 Posts

Posted - 2013-12-17 : 11:53:24

Dear please find the output below for each scenario separated with empty row for each example.
the output:
--------------------------------------------
Pax ID Reservation Surname First Name pax in same res# # of pax Diff flight date purchase date TransitCity Flight date FLightNo DepartCity ArrivalCity Last Modified date Purchase Date
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
100001 10001 Mike Jack Null 1 2 10/10/2013 Null RJ100 JFK FRA 10/10/2013 15:10 08/10/2013

100002 10003 Jason Amit Jason Sam 2 2 10/10/2013 Null RJ500 JED BOM 10/10/2013 15:10 08/10/2013
100008 10003 Jason Sam Jason Amit 2 2 10/10/2013 Null RJ500 JED BOM 10/10/2013 15:10 08/10/2013

100004 10002 jack Rohit Ibrahim Jaji 2 11 12/11/2013 AMM RJ200 Del CAI 11/11/2013 20:00 1/11/2013
100005 10002 Ibrahim Naji Jack Rohit 2 19 20/11/2013 AMM RJ300 CAI DEL 11/11/2013 20:00 1/11/2013

100006 10006 Akbar Ahmed Null 1 13 24/11/2013 Null RJ800 CDG ORY 20/11/2013 10:00 10/11/2013
100006 10006 Akbar Ahmed Null 1 17 28/11/2013 Null RJ900 ORY CDG 20/11/2013 10:00 10/11/2013

100009 10010 karim Saeed Null 1 11 12/12/2013 AMM,CAI RJ200 Del CDG 11/11/2013 20:00 1/11/2013

thank you and B. Regards

Alhakimi

alhakimi
Starting Member

23 Posts

Posted - 2013-12-19 : 15:53:00

HI, do you need any more clarification ?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-12-19 : 17:15:00

Can you at least put your sample data in a consumable format?

Here are some links to help you prepare that:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

alhakimi
Starting Member

23 Posts

Posted - 2013-12-22 : 17:31:33

Hi All,
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
)
Pax ID Reservation Surname First Name Flight Date Flight Number Board Off Original Booking Date Last Mod Date

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1558611 899842 SULIMAN ALI 02/01/2013 0:00 FO151 RIY SAH 01/01/2013 0:00 01/01/2013 8:59
1558612 899842 ALGANADI HASAN 02/01/2013 0:00 FO151 RIY SAH 01/01/2013 0:00 01/01/2013 8:59
1558613 899844 ALYAFEE MOHMMED 01/01/2013 0:00 FO160 SAH TAI 01/01/2013 0:00 01/01/2013 9:00
1558616 899847 ASSIRI ahmed 02/01/2013 0:00 FO873 AHB ADE 01/01/2013 0:00 01/01/2013 9:11
1558617 899847 ASSIRI ahmed 04/01/2013 0:00 FO874 ADE JED 01/01/2013 0:00 01/01/2013 9:11
1558628 899847 asiri alin 02/01/2013 0:00 FO873 AHB ADE 01/01/2013 0:00 01/01/2013 9:11
1558629 899847 asiri alin 04/01/2013 0:00 FO874 ADE JED 01/01/2013 0:00 01/01/2013 9:11
1558618 899848 ALAQWAA EBRAHEEM 01/01/2013 0:00 FO173 HOD SAH 01/01/2013 0:00 01/01/2013 9:12
1558621 899850 ALGELHM HAMID 03/01/2013 0:00 FO196 SAH AAY 01/01/2013 0:00 01/01/2013 9:16
1558622 899851 ASGHAR AMER 01/01/2013 0:00 FO205 ADE SAH 01/01/2013 0:00 01/01/2013 9:18
1558623 899852 ALHALILI HAMZAH 02/01/2013 0:00 FO174 SAH HOD 01/01/2013 0:00 01/01/2013 9:20
1558624 899852 ALJAHDARI GHALIAH 02/01/2013 0:00 FO174 SAH HOD 01/01/2013 0:00 01/01/2013 9:20
1558625 899853 ABDULLAH ADEL 01/01/2013 0:00 FO173 HOD SAH 01/01/2013 0:00 01/01/2013 9:21
1558626 899854 alasmari mohammed 02/01/2013 0:00 FO873 AHB ADE 01/01/2013 0:00 01/01/2013 9:21
1558627 899854 alasmari mohammed 04/01/2013 0:00 FO874 ADE AHB 01/01/2013 0:00 01/01/2013 9:21
1558631 899856 ALI FAWAZ 03/01/2013 0:00 FO196 SAH AAY 01/01/2013 0:00 01/01/2013 9:22
1558631 899856 ALI FAWAZ 03/01/2013 0:00 FO197 AAY TAI 01/01/2013 0:00 01/01/2013 9:22
1558631 899856 ALI FAWAZ 04/01/2013 0:00 FO198 TAI CAI 01/01/2013 0:00 01/01/2013 9:22
1558624 899851 ASSIRI ahmed 01/04/2013 0:00 FO120 TAI ADE 01/03/2013 0:00 01/03/2013 9:11
1558624 899851 ASSIRI ahmed 04/05/2013 0:00 FO121 ADE TAI 01/03/2013 0:00 01/03/2013 9:11

Here is the help i got from the forum:
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

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 02/01/2013 0:00 FO151 RIY SAH 01/01/2013 0:00 01/01/2013 8:59 02/01/2013 0:00
1558612 899842 ALGANADI HASAN SULIMAN ALI 2 1 NULL 02/01/2013 0:00 FO151 RIY SAH 01/01/2013 0:00 01/01/2013 8:59 02/01/2013 0:00
1558613 899844 ALYAFEE MOHMMED NULL 1 2 NULL 03/01/2013 0:00 FO160 SAH TAI 01/01/2013 0:00 01/01/2013 9:00 03/01/2013 0:00
1558616 899847 ASSIRI ahmed ASIRI ALIN 2 1 ADE 02/01/2013 0:00 FO873,FO874 AHB JED 01/01/2013 0:00 01/01/2013 9:11 04/01/2013 0:00
1558628 899847 asiri alin ASSIRI AHMED 2 1 ADE 02/01/2013 0:00 FO873,FO874 AHB ADE 01/01/2013 0:00 01/01/2013 9:11 04/01/2013 0:00
1558626 899854 alasmari mohammed NULL 1 1 NULL 02/01/2013 0:00 FO873 AHB ADE 01/01/2013 0:00 01/01/2013 9:21 02/01/2013 0:00
1558627 899854 alasmari mohammed NULL 1 3 NULL 04/01/2013 0:00 FO874 ADE AHB 01/01/2013 0:00 01/01/2013 9:21 04/01/2013 0:00
1558631 899856 ALI FAWAZ NULL 1 2 AAY,TAI 03/01/2013 0:00 FO196,FO197,FO198 SAH CAI 01/01/2013 0:00 01/01/2013 9:22 04/01/2013 0:00
1558624 899851 ASSIRI ahmed NULL 1 30 NULL 01/04/2013 0:00 FO120 TAI ADE 01/03/2013 0:00 01/03/2013 9:11 01/04/2013 0:00
1558624 899851 ASSIRI ahmed NULL 1 33 NULL 04/05/2013 0:00 FO121 ADE TAI 01/03/2013 0:00 01/03/2013 9:11 04/05/2013 0:00
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Please let me know if you need any further details.

thank you and B. Regards

Alhakimi

alhakimi
Starting Member

23 Posts

Posted - 2013-12-25 : 13:11:53

HI friends , i wanted to check if you have any clarifications thank you so much for your help

alhakimi
Starting Member

23 Posts

Posted - 2013-12-27 : 05:04:06

Here is the query with the data in cleanup format

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources