RE: Complex Query – SQLServerCentral

alhakimi

Say Hey Kid

Points: 687

January 4, 2014 at 10:43 am

#1677527

1- create query

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

)

2- insert query

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','4/1/2013','FO873','AHB','ADE','1/1/2013','1/1/2013' UNION ALL

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

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

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

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

SELECT '1558616','899847','ASSIRI','ahmed','6/11/2013','FO876','ADE','AHB','1/1/2013','1/1/2013' UNION ALL

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

SELECT '1558628','899847','asiri','alin','6/11/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','2/2/2013','FO196','SAH','AAY','1/1/2013','1/1/2013' UNION ALL

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

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

SELECT '1558634','899851','KAMAL','YASSER','1/4/2013','FO120','TAI','ADE','1/3/2013','1/3/2013' UNION ALL

SELECT '1558634','899851','KAMAL','YASSER','4/5/2013','FO121','ADE','TAI','1/3/2013','1/3/2013'

3- The output i want:

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|2/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|2/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|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|4/2/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|4/2/2013 0:00

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

1558628|899847|alin|asiri|ahmed Assiri|2|160|ADE|6/10/2013 0:00|FO875,FO876|JED|AHB|1/1/2013 0:00|1/1/2013 0:00|6/11/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|1/1/2013 0:00

1558621|899850|HAMID|ALGELHM|NULL|1|0|NULL|1/1/2013 0:00|FO196|SAH|AAY|1/1/2013 0:00|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|1/1/2013 0:00

1558634|899851|YASSER|KAMAL|NULL|1|92|null|4/5/2013 0:00|FO120|TAI|ADE|1/3/2013 0:00|1/3/2013 0:00|4/5/2013 0:00

1558634|899851|YASSER|KAMAL|NULL|1|92|null|4/5/2013 0:00|FO120|ADE|TAI|1/3/2013 0:00|1/3/2013 0:00|4/5/2013 0:00

1558631|899856|FAWAZ|ALI|NULL|1|32|AAY,TAI|2/2/2013 0:00|FO196,FO197,FO198|SAH|CAI|1/1/2013 0:00|1/1/2013 0:00|2/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|2/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|2/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|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|4/1/2013 0:00

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

3- What Mr. Lowell provided is :

SELECT [Pax ID],[Reservation],[First name],[Surname],

STUFF((SELECT ',' + [First name] FROM [table1] WHERE [Reservation] = t.[Reservation] AND [First name] <> t.[First name] 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],[First Name],[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],[First Name] ,[Surname]) t

in regards to the arrival date , sorry i missed to mention it in my previous post ..

i know its not possible to get it as there is no flight time, so it will be the last row flight date of each passenger flight .. as mentioned in the needed output above..

for more clarification please let me know.

thank you in advance

Alhakimi