SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Very complex query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

alhakimi
Starting Member

Yemen
23 Posts

Posted - 12/10/2013 :  05:53:19  Show Profile  Reply with Quote
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

263 Posts

Posted - 12/12/2013 :  11:15:12  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/12/2013 :  13:39:39  Show Profile  Reply with Quote

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



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

alhakimi
Starting Member

Yemen
23 Posts

Posted - 12/17/2013 :  08:45:08  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/17/2013 :  09:02:08  Show Profile  Reply with Quote
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
Go to Top of Page

alhakimi
Starting Member

Yemen
23 Posts

Posted - 12/17/2013 :  11:53:24  Show Profile  Reply with Quote
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
Go to Top of Page

alhakimi
Starting Member

Yemen
23 Posts

Posted - 12/19/2013 :  15:53:00  Show Profile  Reply with Quote
HI, do you need any more clarification ?
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 12/19/2013 :  17:15:00  Show Profile  Reply with Quote
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
Go to Top of Page

alhakimi
Starting Member

Yemen
23 Posts

Posted - 12/22/2013 :  17:31:33  Show Profile  Reply with Quote
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


Go to Top of Page

alhakimi
Starting Member

Yemen
23 Posts

Posted - 12/25/2013 :  13:11:53  Show Profile  Reply with Quote
HI friends , i wanted to check if you have any clarifications thank you so much for your help
Go to Top of Page

alhakimi
Starting Member

Yemen
23 Posts

Posted - 12/27/2013 :  05:04:06  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.22 seconds. Powered By: Snitz Forums 2000