Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have the following question i would appriciates your kind assistant to get the output mentioned below: 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.

  4. Different between purchase date and flight date to be stored in new column called ( different between purchase and flight date)

  5. 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 22 dec 2013 and the departure of the second flight is on 23 dec 2013 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 NULL
,   [First Name] varchar NULL
,   [Pax Type] char NULL
,   [Flight Date] [smalldatetime] NULL
,   [Flight Number] varchar NULL
,   [Board] varchar NULL
,   [Off] varchar 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' 

Here is what i have reached to

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

output needed

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 have any clarification.

thank you Alhakimi

share|improve this question

migrated from serverfault.com Jan 3 at 20:50

This question came from our site for professional system and network administrators.

1  
Is this your homework? You should do it on your own... –  Falcon Momot Jan 1 at 10:34
2  
Bad habits to kick : declaring VARCHAR without (length) by Aaron Bertrand. Also, seems like you have not event tested your create and insert script before posting. –  Kin Jan 3 at 21:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.