Help in query

  • Dear Friends

    i have two Tables , one has New records and another table has the same columns but with updates transactions , i need to generate report that shows the last transaction occurs to each passenger, i need only single record with the last update or status.

    CREATE TABLE [dbo].[PNRAdds](

    [Pax ID] [int] NULL,

    [Flight Date] [smalldatetime] NULL,

    [Flight Number] [varchar](10) NULL,

    [Last Mod Date] [smalldatetime] NULL,

    [Status] [varchar](30) NULL

    ) ON [PRIMARY]

    GO

    second table :

    CREATE TABLE [dbo].[PNRUpdates](

    [Pax ID] [int] NULL,

    [Flight Date] [smalldatetime] NULL,

    [Flight Number] [varchar](10) NULL,

    [Last Mod Date] [smalldatetime] NULL,

    [Status] [varchar](30) NULL

    ) ON [PRIMARY]

    GO

    Data : in first table

    INSERT INTO pnradds([Pax ID],[Flight Date],[Flight Number],[Last Mod Date],[status])

    SELECT '1558611','2/1/2014','FO151','2014-01-01 08:59:00' 'O' UNION ALL

    SELECT '1558612','2/1/2015','FO151','2015-01-01 08:59:00' 'O' UNION ALL

    SELECT '1558613','1/1/2013','FO160','2013-10-01 08:59:00' 'O' UNION ALL

    SELECT '1558616','2/1/2012','FO873','2012-10-01 08:59:00' 'O'

    Data in second Table :

    INSERT INTO PNRUpdates([Pax ID],[Flight Date],[Flight Number],[Last Mod Date],[Status])

    SELECT '1558611','2/1/2014','FO151','2014-09-01 012:59:00', 'C' UNION ALL

    SELECT '1558612','2/1/2015','FO151','2015-05-01 12:00:00', 'C' UNION ALL

    SELECT '1558616','2/1/2012','FO873','2012-12-01 10:59:00' ,'S' UNION ALL

    SELECT '1558611','2/1/2014','FO151','2014-12-01 05:59:00', 'X' UNION ALL

    SELECT '1558612','2/1/2015','FO151','2015-01-01 08:59:00', 'X' UNION ALL

    SELECT '1558616','2/1/2012','FO873','2012-10-01 08:59:00' ,'X'

    Result needed :

    Pax IDFlight DateFlight NumberLast Mod DateStatus

    15586112/1/2014 0:00FO15112/1/2014 5:59X

    15586122/1/2015 0:00FO1515/1/2015 12:00C

    15586131/1/2013 0:00FO16010/1/2013 8:59O

    15586162/1/2012 0:00FO87312/1/2012 10:59S

    Thank you

    Regards

    Alhakimi

  • Quick solution using the row_number function

    ๐Ÿ˜Ž

    USE tempdb;

    GO

    SET NOCOUNT ON;

    IF OBJECT_ID(N'dbo.PNRAdds') IS NOT NULL DROP TABLE dbo.PNRAdds;

    CREATE TABLE [dbo].[PNRAdds](

    [Pax ID] [int] NULL,

    [Flight Date] [smalldatetime] NULL,

    [Flight Number] [varchar](10) NULL,

    [Last Mod Date] [smalldatetime] NULL,

    [Status] [varchar](30) NULL

    ) ON [PRIMARY]

    IF OBJECT_ID(N'dbo.PNRUpdates') IS NOT NULL DROP TABLE dbo.PNRUpdates;

    CREATE TABLE [dbo].[PNRUpdates](

    [Pax ID] [int] NULL,

    [Flight Date] [smalldatetime] NULL,

    [Flight Number] [varchar](10) NULL,

    [Last Mod Date] [smalldatetime] NULL,

    [Status] [varchar](30) NULL

    ) ON [PRIMARY]

    INSERT INTO pnradds([Pax ID],[Flight Date],[Flight Number],[Last Mod Date],[status])

    SELECT '1558611','2/1/2014','FO151','2014-01-01 08:59:00','O' UNION ALL

    SELECT '1558612','2/1/2015','FO151','2015-01-01 08:59:00','O' UNION ALL

    SELECT '1558613','1/1/2013','FO160','2013-10-01 08:59:00','O' UNION ALL

    SELECT '1558616','2/1/2012','FO873','2012-10-01 08:59:00','O'

    ;

    INSERT INTO PNRUpdates([Pax ID],[Flight Date],[Flight Number],[Last Mod Date],[Status])

    SELECT '1558611','2/1/2014','FO151','2014-09-01 012:59:00', 'C' UNION ALL

    SELECT '1558612','2/1/2015','FO151','2015-05-01 12:00:00', 'C' UNION ALL

    SELECT '1558616','2/1/2012','FO873','2012-12-01 10:59:00' ,'S' UNION ALL

    SELECT '1558611','2/1/2014','FO151','2014-12-01 05:59:00', 'X' UNION ALL

    SELECT '1558612','2/1/2015','FO151','2015-01-01 08:59:00', 'X' UNION ALL

    SELECT '1558616','2/1/2012','FO873','2012-10-01 08:59:00' ,'X'

    ;

    /*

    Method: Union the sets and use row_number to optain the

    last entry for each key (Pax ID)

    */

    ;WITH BASE_DATA AS

    (

    SELECT

    [Pax ID]

    ,[Flight Date]

    ,[Flight Number]

    ,[Last Mod Date]

    ,[Status]

    FROM dbo.PNRAdds

    UNION ALL

    SELECT

    [Pax ID]

    ,[Flight Date]

    ,[Flight Number]

    ,[Last Mod Date]

    ,[Status]

    FROM dbo.PNRUpdates

    )

    ,FIND_LAST_UPDATE AS

    (

    SELECT

    BD.[Pax ID]

    ,ROW_NUMBER() OVER

    (

    PARTITION BY BD.[Pax ID]

    ORDER BY BD.[Last Mod Date] DESC

    ) AS PAX_RID

    ,BD.[Flight Date]

    ,BD.[Flight Number]

    ,BD.[Last Mod Date]

    ,BD.[Status]

    FROM BASE_DATA BD

    )

    SELECT

    FLU.[Pax ID]

    ,FLU.[Flight Date]

    ,FLU.[Flight Number]

    ,FLU.[Last Mod Date]

    ,FLU.[Status]

    FROM FIND_LAST_UPDATE FLU

    WHERE FLU.PAX_RID = 1

    ;

    Results

    Pax ID Flight Date Flight Number Last Mod Date Status

    ----------- ----------------------- ------------- ----------------------- --------

    1558611 2014-02-01 00:00:00 FO151 2014-12-01 05:59:00 X

    1558612 2015-02-01 00:00:00 FO151 2015-05-01 12:00:00 C

    1558613 2013-01-01 00:00:00 FO160 2013-10-01 08:59:00 O

    1558616 2012-02-01 00:00:00 FO873 2012-12-01 10:59:00 S

  • A similar concept to Eirikur, but with a single CTE and LEFT Join in the main SQL.

    WITH CTE_GetLatest

    AS

    (

    SELECT

    [Pax Id]

    ,[Flight Date]

    ,[Flight Number]

    ,[Status]

    ,[Last Mod Date]

    ,ROW_NUMBER()

    OVER

    (PARTITION BY [Pax Id],[Flight Date],[Flight Number]

    ORDER BY [Last Mod Date] DESC) Latest

    from #PNRUpdates

    )

    SELECT

    Main.[Pax Id]

    , Main.[Flight Date]

    , Main.[Flight Number]

    , ISNULL(CTE_GetLatest.[Last Mod Date],Main.[Last Mod Date]) AS [Last Mod Date]

    , ISNULL(CTE_GetLatest.[Status],Main.[Status])AS [Status]

    FROM #PNRAdds Main

    LEFT JOIN CTE_GetLatest

    ON Main.[Pax Id]=CTE_GetLatest.[Pax Id]

    AND Main.[Flight Date]=CTE_GetLatest.[Flight Date]

    AND Main.[Flight Number]=CTE_GetLatest.[Flight Number]

    AND Latest=1

    As a general tip, I would suggest that you don't use Spaces in column names.

    I would also consider adding one column to the PNRUpdates table if possible, which would be a flag used to indicate most current row in the data data, this then removes the need to have a Row_number() order clause.

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • Here's another way:

    SELECT

    a.[Pax ID],

    a.[Flight Date],

    a.[Flight Number],

    [Last Mod Date] = CASE WHEN x.[Pax ID] IS NULL THEN a.[Last Mod Date] ELSE x.[Last Mod Date] END,

    [Status] = CASE WHEN x.[Pax ID] IS NULL THEN a.[Status] ELSE x.[Status] END

    FROM pnradds a

    OUTER APPLY (

    SELECT TOP 1 u.[Pax ID], u.[Last Mod Date], u.[Status]

    FROM PNRUpdates u

    WHERE u.[Pax ID] = a.[Pax ID]

    AND u.[Flight Date] = a.[Flight Date]

    AND u.[Flight Number] = a.[Flight Number]

    ORDER BY [Last Mod Date] DESC

    ) x

    -- Note that the CASE blocks cannot be replaced with ISNULL because [Last Mod Date] and [Status] are nullable.

    โ€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.โ€ - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • ChrisM@Work (2/18/2015)


    Here's another way:

    SELECT

    a.[Pax ID],

    a.[Flight Date],

    a.[Flight Number],

    [Last Mod Date] = CASE WHEN x.[Pax ID] IS NULL THEN a.[Last Mod Date] ELSE x.[Last Mod Date] END,

    [Status] = CASE WHEN x.[Pax ID] IS NULL THEN a.[Status] ELSE x.[Status] END

    FROM pnradds a

    OUTER APPLY (

    SELECT TOP 1 u.[Pax ID], u.[Last Mod Date], u.[Status]

    FROM PNRUpdates u

    WHERE u.[Pax ID] = a.[Pax ID]

    AND u.[Flight Date] = a.[Flight Date]

    AND u.[Flight Number] = a.[Flight Number]

    ORDER BY [Last Mod Date] DESC

    ) x

    -- Note that the CASE blocks cannot be replaced with ISNULL because [Last Mod Date] and [Status] are nullable.

    Word of caution, the update could include changes in flight number and / or date

    ๐Ÿ˜Ž

  • Eirikur Eiriksson (2/18/2015)


    ChrisM@Work (2/18/2015)


    Here's another way:

    SELECT

    a.[Pax ID],

    a.[Flight Date],

    a.[Flight Number],

    [Last Mod Date] = CASE WHEN x.[Pax ID] IS NULL THEN a.[Last Mod Date] ELSE x.[Last Mod Date] END,

    [Status] = CASE WHEN x.[Pax ID] IS NULL THEN a.[Status] ELSE x.[Status] END

    FROM pnradds a

    OUTER APPLY (

    SELECT TOP 1 u.[Pax ID], u.[Last Mod Date], u.[Status]

    FROM PNRUpdates u

    WHERE u.[Pax ID] = a.[Pax ID]

    AND u.[Flight Date] = a.[Flight Date]

    AND u.[Flight Number] = a.[Flight Number]

    ORDER BY [Last Mod Date] DESC

    ) x

    -- Note that the CASE blocks cannot be replaced with ISNULL because [Last Mod Date] and [Status] are nullable.

    Word of caution, the update could include changes in flight number and / or date

    ๐Ÿ˜Ž

    Yep ๐Ÿ˜‰


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • You can accomplished this using below query:

    ;WITH CTE AS

    (

    SELECT [Pax ID],[Flight Date],[Flight Number],[Last Mod Date],[status]

    FROM PNRAdds

    UNION ALL

    SELECT [Pax ID],[Flight Date],[Flight Number],[Last Mod Date],[status]

    FROM PNRUpdates

    ),

    CTE1 AS

    (

    SELECT [Pax ID], [Flight Date],[Flight Number], [Last Mod Date], Status,

    ROW_NUMBER() over(partition by [Pax Id] order by [last mod date]DESC) rn

    FROM CTE

    )

    SELECT [Pax ID], CONVERT(varchar(10),[Flight Date], 101) FlightDate

    ,[Flight Number], CONVERT(varchar(10),[Last Mod Date],101) LastModDate, Status FROM CTE1

    WHERE rn = 1

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Dear Friends ,

    I was not able to adjust the query you send with my actual table , so may you please assist with the current tables structure:

    CREATE TABLE [dbo].[AmeliaPNRUpdates](

    [Pax ID] [int] NULL,

    [Reservation] [int] NULL,

    [Surname] [varchar](40) NULL,

    [First Name] [varchar](40) NULL,

    [Pax Type] [char](1) NULL,

    [Phone] [varchar](16) NULL,

    [Mobile] [varchar](16) NULL,

    [Email] [varchar](128) NULL,

    [Passport] [varchar](20) NULL,

    [Fare Class] [varchar](8) NULL,

    [Flight Date] [smalldatetime] NULL,

    [Flight Number] [varchar](10) NULL,

    [Board] [varchar](3) NULL,

    [Off] [varchar](3) NULL,

    [Original Booking Date] [smalldatetime] NULL,

    [Leg Status] [varchar](1) NULL,

    [Last Mod Date] [smalldatetime] NULL,

    [Booking Origin] [varchar](6) NULL,

    [Agency Name] [varchar](40) NULL,

    [Province] [varchar](40) NULL,

    [User Base] [varchar](5) NULL,

    [lng_Res_Segments_Id_Nmbr] [varchar](15) NULL,

    [Flight Status] [varchar](9) NULL,

    [lng_Res_Pax_Group_Id_Nmbr] [int] NULL,

    [RSDesc] [varchar](30) NULL

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

    INSERT [dbo].[AmeliaPNRUpdates] ([Pax ID], [Reservation], [Surname], [First Name], [Pax Type], [Phone], [Mobile], [Email], [Passport], [Fare Class], [Flight Date], [Flight Number], [Board], [Off], [Original Booking Date], [Leg Status], [Last Mod Date], [Booking Origin], [Agency Name], [Province], [User Base], [lng_Res_Segments_Id_Nmbr], [Flight Status], [lng_Res_Pax_Group_Id_Nmbr], [RSDesc]) VALUES (2180259, 1270364, N'ALI', N'ABDULLAH ABDO', N'A', N'700067263', NULL, NULL, NULL, N'A-YMN', CAST(0xA41D0000 AS SmallDateTime), N'FO500', N'SAH', N'TAI', CAST(0xA41C0000 AS SmallDateTime), N'C', CAST(0xA41D0255 AS SmallDateTime), N'Web', N'TAI Yemen Alalya Travel', N'Taiz Al Janad', N'TAI', N'2643572C', N'Closed', 1883648, N'BORD')

    INSERT [dbo].[AmeliaPNRUpdates] ([Pax ID], [Reservation], [Surname], [First Name], [Pax Type], [Phone], [Mobile], [Email], [Passport], [Fare Class], [Flight Date], [Flight Number], [Board], [Off], [Original Booking Date], [Leg Status], [Last Mod Date], [Booking Origin], [Agency Name], [Province], [User Base], [lng_Res_Segments_Id_Nmbr], [Flight Status], [lng_Res_Pax_Group_Id_Nmbr], [RSDesc]) VALUES (2180261, 1270367, N'ALMAMARI', N'EBRAHIM AHMED', N'A', N'736631231', NULL, NULL, NULL, N'A-YMN', CAST(0xA41D0000 AS SmallDateTime), N'FO501', N'TAI', N'SAH', CAST(0xA41C0000 AS SmallDateTime), N'C', CAST(0xA41D039A AS SmallDateTime), N'Web', N'TAI ZAIN TRAVEL', N'Taiz Al Janad', N'TAI', N'2643574C', N'Open', 1883650, N'BORD')

    INSERT [dbo].[AmeliaPNRUpdates] ([Pax ID], [Reservation], [Surname], [First Name], [Pax Type], [Phone], [Mobile], [Email], [Passport], [Fare Class], [Flight Date], [Flight Number], [Board], [Off], [Original Booking Date], [Leg Status], [Last Mod Date], [Booking Origin], [Agency Name], [Province], [User Base], [lng_Res_Segments_Id_Nmbr], [Flight Status], [lng_Res_Pax_Group_Id_Nmbr], [RSDesc]) VALUES (2180262, 1270367, N'ALMASHWALI', N'AHMED ALi', N'A', N'736631231', NULL, NULL, NULL, N'A-YMN', CAST(0xA41D0000 AS SmallDateTime), N'FO501', N'TAI', N'SAH', CAST(0xA41C0000 AS SmallDateTime), N'C', CAST(0xA41D039A AS SmallDateTime), N'Web', N'TAI ZAIN TRAVEL', N'Taiz Al Janad', N'TAI', N'2643575C', N'Open', 1883651, N'BORD')

    INSERT [dbo].[AmeliaPNRUpdates] ([Pax ID], [Reservation], [Surname], [First Name], [Pax Type], [Phone], [Mobile], [Email], [Passport], [Fare Class], [Flight Date], [Flight Number], [Board], [Off], [Original Booking Date], [Leg Status], [Last Mod Date], [Booking Origin], [Agency Name], [Province], [User Base], [lng_Res_Segments_Id_Nmbr], [Flight Status], [lng_Res_Pax_Group_Id_Nmbr], [RSDesc]) VALUES (2180274, 1270374, N'ABDOO', N'IBRAHEEM', N'A', N'735644442', N'498710', N'THABIT20141@GMAIL.COM', NULL, N'A-YMN', CAST(0xA41D0000 AS SmallDateTime), N'FO501', N'TAI', N'SAH', CAST(0xA41C0000 AS SmallDateTime), N'C', CAST(0xA41D039A AS SmallDateTime), N'Web', N'SAH ALSHAEER Travel', N'Sana''a Intl', N'SAH', N'2643592C', N'Open', 1883661, N'BORD')

    INSERT [dbo].[AmeliaPNRUpdates] ([Pax ID], [Reservation], [Surname], [First Name], [Pax Type], [Phone], [Mobile], [Email], [Passport], [Fare Class], [Flight Date], [Flight Number], [Board], [Off], [Original Booking Date], [Leg Status], [Last Mod Date], [Booking Origin], [Agency Name], [Province], [User Base], [lng_Res_Segments_Id_Nmbr], [Flight Status], [lng_Res_Pax_Group_Id_Nmbr], [RSDesc]) VALUES (2180275, 1270374, N'ALMUSEE', N'ADEL', N'A', N'498710', NULL, NULL, NULL, N'A-YMN', CAST(0xA41D0000 AS SmallDateTime), N'FO501', N'TAI', N'SAH', CAST(0xA41C0000 AS SmallDateTime), N'C', CAST(0xA41D039A AS SmallDateTime), N'Web', N'SAH ALSHAEER Travel', N'Sana''a Intl', N'SAH', N'2643593C', N'Open', 1883662, N'BORD')

    INSERT [dbo].[AmeliaPNRUpdates] ([Pax ID], [Reservation], [Surname], [First Name], [Pax Type], [Phone], [Mobile], [Email], [Passport], [Fare Class], [Flight Date], [Flight Number], [Board], [Off], [Original Booking Date], [Leg Status], [Last Mod Date], [Booking Origin], [Agency Name], [Province], [User Base], [lng_Res_Segments_Id_Nmbr], [Flight Status], [lng_Res_Pax_Group_Id_Nmbr], [RSDesc]) VALUES (2180276, 1270374, N'ALRADAEI', N'ALI', N'A', N'498710', NULL, NULL, NULL, N'A-YMN', CAST(0xA41D0000 AS SmallDateTime), N'FO501', N'TAI', N'SAH', CAST(0xA41C0000 AS SmallDateTime), N'C', CAST(0xA41D039A AS SmallDateTime), N'Web', N'SAH ALSHAEER Travel', N'Sana''a Intl', N'SAH', N'2643594C', N'Open', 1883663, N'BORD')

    INSERT [dbo].[AmeliaPNRUpdates] ([Pax ID], [Reservation], [Surname], [First Name], [Pax Type], [Phone], [Mobile], [Email], [Passport], [Fare Class], [Flight Date], [Flight Number], [Board], [Off], [Original Booking Date], [Leg Status], [Last Mod Date], [Booking Origin], [Agency Name], [Province], [User Base], [lng_Res_Segments_Id_Nmbr], [Flight Status], [lng_Res_Pax_Group_Id_Nmbr], [RSDesc]) VALUES (2180277, 1270374, N'GAMEL', N'GAMAL', N'A', N'498710', NULL, NULL, NULL, N'A-YMN', CAST(0xA41D0000 AS SmallDateTime), N'FO501', N'TAI', N'SAH', CAST(0xA41C0000 AS SmallDateTime), N'C', CAST(0xA41D039A AS SmallDateTime), N'Web', N'SAH ALSHAEER Travel', N'Sana''a Intl', N'SAH', N'2643595C', N'Open', 1883664, N'BORD')

    INSERT [dbo].[AmeliaPNRUpdates] ([Pax ID], [Reservation], [Surname], [First Name], [Pax Type], [Phone], [Mobile], [Email], [Passport], [Fare Class], [Flight Date], [Flight Number], [Board], [Off], [Original Booking Date], [Leg Status], [Last Mod Date], [Booking Origin], [Agency Name], [Province], [User Base], [lng_Res_Segments_Id_Nmbr], [Flight Status], [lng_Res_Pax_Group_Id_Nmbr], [RSDesc]) VALUES (2180278, 1270374, N'HUSSEN', N'ABDULARAHMAN', N'A', N'498710', NULL, NULL, NULL, N'A-YMN', CAST(0xA41D0000 AS SmallDateTime), N'FO501', N'TAI', N'SAH', CAST(0xA41C0000 AS SmallDateTime), N'C', CAST(0xA41D039A AS SmallDateTime), N'Web', N'SAH ALSHAEER Travel', N'Sana''a Intl', N'SAH', N'2643596C', N'Open', 1883665, N'BORD')

    INSERT [dbo].[AmeliaPNRUpdates] ([Pax ID], [Reservation], [Surname], [First Name], [Pax Type], [Phone], [Mobile], [Email], [Passport], [Fare Class], [Flight Date], [Flight Number], [Board], [Off], [Original Booking Date], [Leg Status], [Last Mod Date], [Booking Origin], [Agency Name], [Province], [User Base], [lng_Res_Segments_Id_Nmbr], [Flight Status], [lng_Res_Pax_Group_Id_Nmbr], [RSDesc]) VALUES (2180279, 1270375, N'ABDULSALAM', N'ESMAIL ABDULFATAH', N'A', N'770107569', NULL, N'alezzy.jamal@gmail.com', NULL, N'A-YMN', CAST(0xA41D0000 AS SmallDateTime), N'FO500', N'SAH', N'TAI', CAST(0xA41C0000 AS SmallDateTime), N'C', CAST(0xA41D0205 AS SmallDateTime), N'Web', N'SAH UNIVEERSAL APT', N'Sana''a Intl', N'SAH', N'2643597C', N'Closed', 1883666, N'BORD')

    INSERT [dbo].[AmeliaPNRUpdates] ([Pax ID], [Reservation], [Surname], [First Name], [Pax Type], [Phone], [Mobile], [Email], [Passport], [Fare Class], [Flight Date], [Flight Number], [Board], [Off], [Original Booking Date], [Leg Status], [Last Mod Date], [Booking Origin], [Agency Name], [Province], [User Base], [lng_Res_Segments_Id_Nmbr], [Flight Status], [lng_Res_Pax_Group_Id_Nmbr], [RSDesc]) VALUES (2180280, 1270376, N'FARUOQ', N'AMEEN', N'A', N'735644442', N'498710', N'THABIT20141@GMAIL.COM', NULL, N'A-YMN', CAST(0xA41D0000 AS SmallDateTime), N'FO501', N'TAI', N'SAH', CAST(0xA41C0000 AS SmallDateTime), N'C', CAST(0xA41D039A AS SmallDateTime), N'Web', N'SAH ALSHAEER Travel', N'Sana''a Intl', N'SAH', N'2643598C', N'Open', 1883667, N'BORD')

    INSERT [dbo].[AmeliaPNRUpdates] ([Pax ID], [Reservation], [Surname], [First Name], [Pax Type], [Phone], [Mobile], [Email], [Passport], [Fare Class], [Flight Date], [Flight Number], [Board], [Off], [Original Booking Date], [Leg Status], [Last Mod Date], [Booking Origin], [Agency Name], [Province], [User Base], [lng_Res_Segments_Id_Nmbr], [Flight Status], [lng_Res_Pax_Group_Id_Nmbr], [RSDesc]) VALUES (2180281, 1270376, N'OBAID', N'MOHAMMED', N'A', N'498710', NULL, NULL, NULL, N'A-YMN', CAST(0xA41D0000 AS SmallDateTime), N'FO501', N'TAI', N'SAH', CAST(0xA41C0000 AS SmallDateTime), N'C', CAST(0xA41D039A AS SmallDateTime), N'Web', N'SAH ALSHAEER Travel', N'Sana''a Intl', N'SAH', N'2643599C', N'Open', 1883668, N'BORD')

    INSERT [dbo].[AmeliaPNRUpdates] ([Pax ID], [Reservation], [Surname], [First Name], [Pax Type], [Phone], [Mobile], [Email], [Passport], [Fare Class], [Flight Date], [Flight Number], [Board], [Off], [Original Booking Date], [Leg Status], [Last Mod Date], [Booking Origin], [Agency Name], [Province], [User Base], [lng_Res_Segments_Id_Nmbr], [Flight Status], [lng_Res_Pax_Group_Id_Nmbr], [RSDesc]) VALUES (2180282, 1270376, N'ALKHRAZ', N'AHMED', N'A', N'498710', NULL, NULL, NULL, N'A-YMN', CAST(0xA41D0000 AS SmallDateTime), N'FO501', N'TAI', N'SAH', CAST(0xA41C0000 AS SmallDateTime), N'C', CAST(0xA41D039A AS SmallDateTime), N'Web', N'SAH ALSHAEER Travel', N'Sana''a Intl', N'SAH', N'2643600C', N'Open', 1883669, N'BORD')

    INSERT [dbo].[AmeliaPNRUpdates] ([Pax ID], [Reservation], [Surname], [First Name], [Pax Type], [Phone], [Mobile], [Email], [Passport], [Fare Class], [Flight Date], [Flight Number], [Board], [Off], [Original Booking Date], [Leg Status], [Last Mod Date], [Booking Origin], [Agency Name], [Province], [User Base], [lng_Res_Segments_Id_Nmbr], [Flight Status], [lng_Res_Pax_Group_Id_Nmbr], [RSDesc]) VALUES (2180283, 1270376, N'KHALED', N'ALHGRH', N'A', N'498710', NULL, NULL, NULL, N'A-YMN', CAST(0xA41D0000 AS SmallDateTime), N'FO501', N'TAI', N'SAH', CAST(0xA41C0000 AS SmallDateTime), N'C', CAST(0xA41D039A AS SmallDateTime), N'Web', N'SAH ALSHAEER Travel', N'Sana''a Intl', N'SAH', N'2643601C', N'Open', 1883670, N'BORD')

    INSERT [dbo].[AmeliaPNRUpdates] ([Pax ID], [Reservation], [Surname], [First Name], [Pax Type], [Phone], [Mobile], [Email], [Passport], [Fare Class], [Flight Date], [Flight Number], [Board], [Off], [Original Booking Date], [Leg Status], [Last Mod Date], [Booking Origin], [Agency Name], [Province], [User Base], [lng_Res_Segments_Id_Nmbr], [Flight Status], [lng_Res_Pax_Group_Id_Nmbr], [RSDesc]) VALUES (2180292, 1270380, N'ALMGHAS', N'TAMER ABDULAZIZ', N'A', N'241420', N'733499596', N'myholidays.ye@gmail.com', NULL, N'A-YMN', CAST(0xA41D0000 AS SmallDateTime), N'FO501', N'TAI', N'SAH', CAST(0xA41C0000 AS SmallDateTime), N'C', CAST(0xA41D039A AS SmallDateTime), N'Web', N'SAH Jubari for Tourism Travel', N'Sana''a Intl', N'SAH', N'2643612C', N'Open', 1883676, N'BORD')

    INSERT [dbo].[AmeliaPNRUpdates] ([Pax ID], [Reservation], [Surname], [First Name], [Pax Type], [Phone], [Mobile], [Email], [Passport], [Fare Class], [Flight Date], [Flight Number], [Board], [Off], [Original Booking Date], [Leg Status], [Last Mod Date], [Booking Origin], [Agency Name], [Province], [User Base], [lng_Res_Segments_Id_Nmbr], [Flight Status], [lng_Res_Pax_Group_Id_Nmbr], [RSDesc]) VALUES (2180312, 1270393, N'ALHARAZI', N'TALAL', N'A', N'770409470', NULL, N'ALAQEEQTRAVEL@GMAIL.COM', NULL, N'A-YMN', CAST(0xA41D0000 AS SmallDateTime), N'FO500', N'SAH', N'TAI', CAST(0xA41C0000 AS SmallDateTime), N'C', CAST(0xA41D024B AS SmallDateTime), N'Web', N'SAH ALAQEEQ TRAVEL', N'Sana''a Intl', N'SAH', N'2643637C', N'Closed', 1883691, N'BORD')

    INSERT [dbo].[AmeliaPNRUpdates] ([Pax ID], [Reservation], [Surname], [First Name], [Pax Type], [Phone], [Mobile], [Email], [Passport], [Fare Class], [Flight Date], [Flight Number], [Board], [Off], [Original Booking Date], [Leg Status], [Last Mod Date], [Booking Origin], [Agency Name], [Province], [User Base], [lng_Res_Segments_Id_Nmbr], [Flight Status], [lng_Res_Pax_Group_Id_Nmbr], [RSDesc]) VALUES (2180315, 1270395, N'T.B.A.', N'Adult', N'A', NULL, N'01-206908', NULL, N'-1', NULL, CAST(0xA4250000 AS SmallDateTime), N'FO820', N'ADE', N'JED', CAST(0xA41C0000 AS SmallDateTime), N'X', CAST(0xA41C0585 AS SmallDateTime), N'Amelia', N'FELIX AIRWAYS', N'Sana''a Intl', N'AAP', N'2643640C', N'Open', 1883694, N'NOT-CHECKED-IN')

    INSERT [dbo].[AmeliaPNRUpdates] ([Pax ID], [Reservation], [Surname], [First Name], [Pax Type], [Phone], [Mobile], [Email], [Passport], [Fare Class], [Flight Date], [Flight Number], [Board], [Off], [Original Booking Date], [Leg Status], [Last Mod Date], [Booking Origin], [Agency Name], [Province], [User Base], [lng_Res_Segments_Id_Nmbr], [Flight Status], [lng_Res_Pax_Group_Id_Nmbr], [RSDesc]) VALUES (2180316, 1270395, N'T.B.A.', N'Adult', N'A', NULL, N'01-206908', NULL, N'-1', NULL, CAST(0xA43E0000 AS SmallDateTime), N'FO825', N'JED', N'ADE', CAST(0xA41C0000 AS SmallDateTime), N'X', CAST(0xA41C0585 AS SmallDateTime), N'Amelia', N'FELIX AIRWAYS', N'Sana''a Intl', N'AAP', N'2643641C', N'Open', 1883694, N'NOT-CHECKED-IN')

    INSERT [dbo].[AmeliaPNRUpdates] ([Pax ID], [Reservation], [Surname], [First Name], [Pax Type], [Phone], [Mobile], [Email], [Passport], [Fare Class], [Flight Date], [Flight Number], [Board], [Off], [Original Booking Date], [Leg Status], [Last Mod Date], [Booking Origin], [Agency Name], [Province], [User Base], [lng_Res_Segments_Id_Nmbr], [Flight Status], [lng_Res_Pax_Group_Id_Nmbr], [RSDesc]) VALUES (2180320, 1270399, N'SAIF', N'GAWHARAH MOHAMED', N'A', N'772858687', NULL, NULL, NULL, N'A-YMN', CAST(0xA41D0000 AS SmallDateTime), N'FO501', N'TAI', N'SAH', CAST(0xA41C0000 AS SmallDateTime), N'C', CAST(0xA41D039A AS SmallDateTime), N'Web', N'TAI ALHARBI TRAVEL', N'Taiz Al Janad', N'TAI', N'2643645C', N'Open', 1883698, N'BORD')

    /****** Object: Table [dbo].[AmeliaPNRAdds] Script Date: 02/22/2015 12:26:55 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[AmeliaPNRAdds](

    [Pax ID] [int] NULL,

    [Reservation] [int] NULL,

    [Surname] [varchar](40) NULL,

    [First Name] [varchar](40) NULL,

    [Pax Type] [char](1) NULL,

    [Phone] [varchar](16) NULL,

    [Mobile] [varchar](16) NULL,

    [Email] [varchar](128) NULL,

    [Passport] [varchar](20) NULL,

    [Fare Class] [varchar](8) NULL,

    [Flight Date] [smalldatetime] NULL,

    [Flight Number] [varchar](10) NULL,

    [Board] [varchar](3) NULL,

    [Off] [varchar](3) NULL,

    [Original Booking Date] [smalldatetime] NULL,

    [Leg Status] [varchar](1) NULL,

    [Last Mod Date] [smalldatetime] NULL,

    [Booking Origin] [varchar](6) NULL,

    [Agency Name] [varchar](40) NULL,

    [Province] [varchar](40) NULL,

    [User Base] [varchar](5) NULL,

    [lng_Res_Segments_Id_Nmbr] [varchar](15) NULL,

    [Flight Status] [varchar](9) NULL,

    [lng_Res_Pax_Group_Id_Nmbr] [int] NULL,

    [RSDesc] [varchar](30) NULL

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

    INSERT [dbo].[AmeliaPNRAdds] ([Pax ID], [Reservation], [Surname], [First Name], [Pax Type], [Phone], [Mobile], [Email], [Passport], [Fare Class], [Flight Date], [Flight Number], [Board], [Off], [Original Booking Date], [Leg Status], [Last Mod Date], [Booking Origin], [Agency Name], [Province], [User Base], [lng_Res_Segments_Id_Nmbr], [Flight Status], [lng_Res_Pax_Group_Id_Nmbr], [RSDesc]) VALUES (2181129, 1270735, N'ALAHMDI', N'SABAH MOSLAH', N'A', N'773756780', NULL, NULL, NULL, N'A-YMN', CAST(0xA4210000 AS SmallDateTime), N'FO196', N'SAH', N'AAY', CAST(0xA41D0000 AS SmallDateTime), N'C', CAST(0xA41D04EF AS SmallDateTime), N'Web', N'AAY ALMUHANDIS TRAVEL', N'Al Ghaydah', N'AAY', N'2644572C', N'Open', 1884400, N'NOT-CHECKED-IN')

    INSERT [dbo].[AmeliaPNRAdds] ([Pax ID], [Reservation], [Surname], [First Name], [Pax Type], [Phone], [Mobile], [Email], [Passport], [Fare Class], [Flight Date], [Flight Number], [Board], [Off], [Original Booking Date], [Leg Status], [Last Mod Date], [Booking Origin], [Agency Name], [Province], [User Base], [lng_Res_Segments_Id_Nmbr], [Flight Status], [lng_Res_Pax_Group_Id_Nmbr], [RSDesc]) VALUES (2181130, 1270736, N'ABDULRAB', N'MOHAMMED ABDULLAH', N'A', N'711259123', NULL, N'makkah2013@live.com', NULL, N'N', CAST(0xA4330000 AS SmallDateTime), N'FO820', N'ADE', N'JED', CAST(0xA41D0000 AS SmallDateTime), N'C', CAST(0xA41D04EF AS SmallDateTime), N'Web', N'ADE Makkah Travel', N'Aden Intl', N'ADE', N'2644574C', N'Open', 1884401, N'NOT-CHECKED-IN')

    INSERT [dbo].[AmeliaPNRAdds] ([Pax ID], [Reservation], [Surname], [First Name], [Pax Type], [Phone], [Mobile], [Email], [Passport], [Fare Class], [Flight Date], [Flight Number], [Board], [Off], [Original Booking Date], [Leg Status], [Last Mod Date], [Booking Origin], [Agency Name], [Province], [User Base], [lng_Res_Segments_Id_Nmbr], [Flight Status], [lng_Res_Pax_Group_Id_Nmbr], [RSDesc]) VALUES (2181131, 1270736, N'ABDULRAB', N'MOHAMMED ABDULLAH', N'A', N'711259123', NULL, N'makkah2013@live.com', NULL, N'Z', CAST(0xA4450000 AS SmallDateTime), N'FO825', N'JED', N'ADE', CAST(0xA41D0000 AS SmallDateTime), N'C', CAST(0xA41D04EF AS SmallDateTime), N'Web', N'ADE Makkah Travel', N'Aden Intl', N'ADE', N'2644573C', N'Open', 1884401, N'NOT-CHECKED-IN')

    INSERT [dbo].[AmeliaPNRAdds] ([Pax ID], [Reservation], [Surname], [First Name], [Pax Type], [Phone], [Mobile], [Email], [Passport], [Fare Class], [Flight Date], [Flight Number], [Board], [Off], [Original Booking Date], [Leg Status], [Last Mod Date], [Booking Origin], [Agency Name], [Province], [User Base], [lng_Res_Segments_Id_Nmbr], [Flight Status], [lng_Res_Pax_Group_Id_Nmbr], [RSDesc]) VALUES (2181132, 1270737, N'HONEAFAN', N'NAJI SAAD', N'A', N'774077873', N'774077873', NULL, NULL, N'A-YMN', CAST(0xA4210000 AS SmallDateTime), N'FO196', N'SAH', N'AAY', CAST(0xA41D0000 AS SmallDateTime), N'C', CAST(0xA41D04F2 AS SmallDateTime), N'Web', N'AAY ALMUHANDIS TRAVEL', N'Al Ghaydah', N'AAY', N'2644575C', N'Open', 1884402, N'NOT-CHECKED-IN')

    INSERT [dbo].[AmeliaPNRAdds] ([Pax ID], [Reservation], [Surname], [First Name], [Pax Type], [Phone], [Mobile], [Email], [Passport], [Fare Class], [Flight Date], [Flight Number], [Board], [Off], [Original Booking Date], [Leg Status], [Last Mod Date], [Booking Origin], [Agency Name], [Province], [User Base], [lng_Res_Segments_Id_Nmbr], [Flight Status], [lng_Res_Pax_Group_Id_Nmbr], [RSDesc]) VALUES (2181133, 1270738, N'ALJAIFI', N'YAHYA HEZAM', N'A', N'771240040', N'770700222', NULL, NULL, N'A-YMN', CAST(0xA41F0000 AS SmallDateTime), N'FO109', N'ADE', N'SAH', CAST(0xA41D0000 AS SmallDateTime), N'C', CAST(0xA41D04FC AS SmallDateTime), N'Web', N'SAH ALMOKARRAM TOURS', N'Sana''a Intl', N'SAH', N'2644576C', N'Open', 1884403, N'NOT-CHECKED-IN')

    INSERT [dbo].[AmeliaPNRAdds] ([Pax ID], [Reservation], [Surname], [First Name], [Pax Type], [Phone], [Mobile], [Email], [Passport], [Fare Class], [Flight Date], [Flight Number], [Board], [Off], [Original Booking Date], [Leg Status], [Last Mod Date], [Booking Origin], [Agency Name], [Province], [User Base], [lng_Res_Segments_Id_Nmbr], [Flight Status], [lng_Res_Pax_Group_Id_Nmbr], [RSDesc]) VALUES (2181134, 1270739, N'ALHABRI', N'AHMED ABDULALEM AHMED', N'A', N'96613833000', N'966551355958', N'JAMALSANAN@YAHOO.COM', NULL, N'DYOW', CAST(0xA41E0000 AS SmallDateTime), N'FO831', N'DMM', N'TAI', CAST(0xA41D0000 AS SmallDateTime), N'C', CAST(0xA41D04FD AS SmallDateTime), N'Web', N'DMM STARS TRAVEL', N'NA', N'DMM', N'2644577C', N'Open', 1884404, N'NOT-CHECKED-IN')

    INSERT [dbo].[AmeliaPNRAdds] ([Pax ID], [Reservation], [Surname], [First Name], [Pax Type], [Phone], [Mobile], [Email], [Passport], [Fare Class], [Flight Date], [Flight Number], [Board], [Off], [Original Booking Date], [Leg Status], [Last Mod Date], [Booking Origin], [Agency Name], [Province], [User Base], [lng_Res_Segments_Id_Nmbr], [Flight Status], [lng_Res_Pax_Group_Id_Nmbr], [RSDesc]) VALUES (2181135, 1270740, N'baazab', N'dokhanh mohammed salem', N'A', N'770570561', NULL, NULL, NULL, N'A-YMN', CAST(0xA41E0000 AS SmallDateTime), N'FO108', N'SAH', N'ADE', CAST(0xA41D0000 AS SmallDateTime), N'C', CAST(0xA41D04FD AS SmallDateTime), N'Web', N'SAH ALSHAEER Travel', N'Sana''a Intl', N'SAH', N'2644578C', N'Open', 1884405, N'NOT-CHECKED-IN')

    INSERT [dbo].[AmeliaPNRAdds] ([Pax ID], [Reservation], [Surname], [First Name], [Pax Type], [Phone], [Mobile], [Email], [Passport], [Fare Class], [Flight Date], [Flight Number], [Board], [Off], [Original Booking Date], [Leg Status], [Last Mod Date], [Booking Origin], [Agency Name], [Province], [User Base], [lng_Res_Segments_Id_Nmbr], [Flight Status], [lng_Res_Pax_Group_Id_Nmbr], [RSDesc]) VALUES (2181136, 1270740, N'baazab', N'maryam ali mohammed', N'A', N'770570561', NULL, NULL, NULL, N'A-YMN', CAST(0xA41E0000 AS SmallDateTime), N'FO108', N'SAH', N'ADE', CAST(0xA41D0000 AS SmallDateTime), N'C', CAST(0xA41D04FD AS SmallDateTime), N'Web', N'SAH ALSHAEER Travel', N'Sana''a Intl', N'SAH', N'2644579C', N'Open', 1884406, N'NOT-CHECKED-IN')

    INSERT [dbo].[AmeliaPNRAdds] ([Pax ID], [Reservation], [Surname], [First Name], [Pax Type], [Phone], [Mobile], [Email], [Passport], [Fare Class], [Flight Date], [Flight Number], [Board], [Off], [Original Booking Date], [Leg Status], [Last Mod Date], [Booking Origin], [Agency Name], [Province], [User Base], [lng_Res_Segments_Id_Nmbr], [Flight Status], [lng_Res_Pax_Group_Id_Nmbr], [RSDesc]) VALUES (2181137, 1270740, N'awdh', N'tahani qahtan', N'C', N'770570561', NULL, NULL, NULL, N'A-YMN', CAST(0xA41E0000 AS SmallDateTime), N'FO108', N'SAH', N'ADE', CAST(0xA41D0000 AS SmallDateTime), N'C', CAST(0xA41D04FD AS SmallDateTime), N'Web', N'SAH ALSHAEER Travel', N'Sana''a Intl', N'SAH', N'2644580C', N'Open', 1884407, N'NOT-CHECKED-IN')

    INSERT [dbo].[AmeliaPNRAdds] ([Pax ID], [Reservation], [Surname], [First Name], [Pax Type], [Phone], [Mobile], [Email], [Passport], [Fare Class], [Flight Date], [Flight Number], [Board], [Off], [Original Booking Date], [Leg Status], [Last Mod Date], [Booking Origin], [Agency Name], [Province], [User Base], [lng_Res_Segments_Id_Nmbr], [Flight Status], [lng_Res_Pax_Group_Id_Nmbr], [RSDesc]) VALUES (2181138, 1270741, N'ALHUBAISHI', N'IFTIKAR MAHFOUDH', N'A', N'966530048200', N'966530048200', N'MOHD.GABER@YAHOO.COM', NULL, N'DYOW', CAST(0xA4210000 AS SmallDateTime), N'FO873.', N'AHB', N'ADE', CAST(0xA41D0000 AS SmallDateTime), N'C', CAST(0xA41D04FE AS SmallDateTime), N'Web', N'AHB ALTAYYAR SP GIZ', N'NA', N'AHB', N'2644581C', N'Open', 1884408, N'NOT-CHECKED-IN')

    INSERT [dbo].[AmeliaPNRAdds] ([Pax ID], [Reservation], [Surname], [First Name], [Pax Type], [Phone], [Mobile], [Email], [Passport], [Fare Class], [Flight Date], [Flight Number], [Board], [Off], [Original Booking Date], [Leg Status], [Last Mod Date], [Booking Origin], [Agency Name], [Province], [User Base], [lng_Res_Segments_Id_Nmbr], [Flight Status], [lng_Res_Pax_Group_Id_Nmbr], [RSDesc]) VALUES (2181138, 1270741, N'ALHUBAISHI', N'IFTIKAR MAHFOUDH', N'A', N'966530048200', N'966530048200', N'MOHD.GABER@YAHOO.COM', NULL, N'DYOW', CAST(0xA4210000 AS SmallDateTime), N'FO873.', N'ADE', N'SAH', CAST(0xA41D0000 AS SmallDateTime), N'C', CAST(0xA41D04FE AS SmallDateTime), N'Web', N'AHB ALTAYYAR SP GIZ', N'NA', N'AHB', N'2644583C', N'Open', 1884408, N'NOT-CHECKED-IN')

    INSERT [dbo].[AmeliaPNRAdds] ([Pax ID], [Reservation], [Surname], [First Name], [Pax Type], [Phone], [Mobile], [Email], [Passport], [Fare Class], [Flight Date], [Flight Number], [Board], [Off], [Original Booking Date], [Leg Status], [Last Mod Date], [Booking Origin], [Agency Name], [Province], [User Base], [lng_Res_Segments_Id_Nmbr], [Flight Status], [lng_Res_Pax_Group_Id_Nmbr], [RSDesc]) VALUES (2181139, 1270741, N'ALSALAHI', N'RAHF ALMUDAFAR AHMED', N'C', N'966530048200', NULL, NULL, NULL, N'DYOW', CAST(0xA4210000 AS SmallDateTime), N'FO873.', N'AHB', N'ADE', CAST(0xA41D0000 AS SmallDateTime), N'C', CAST(0xA41D04FE AS SmallDateTime), N'Web', N'AHB ALTAYYAR SP GIZ', N'NA', N'AHB', N'2644582C', N'Open', 1884409, N'NOT-CHECKED-IN')

    INSERT [dbo].[AmeliaPNRAdds] ([Pax ID], [Reservation], [Surname], [First Name], [Pax Type], [Phone], [Mobile], [Email], [Passport], [Fare Class], [Flight Date], [Flight Number], [Board], [Off], [Original Booking Date], [Leg Status], [Last Mod Date], [Booking Origin], [Agency Name], [Province], [User Base], [lng_Res_Segments_Id_Nmbr], [Flight Status], [lng_Res_Pax_Group_Id_Nmbr], [RSDesc]) VALUES (2181139, 1270741, N'ALSALAHI', N'RAHF ALMUDAFAR AHMED', N'C', N'966530048200', NULL, NULL, NULL, N'DYOW', CAST(0xA4210000 AS SmallDateTime), N'FO873.', N'ADE', N'SAH', CAST(0xA41D0000 AS SmallDateTime), N'C', CAST(0xA41D04FE AS SmallDateTime), N'Web', N'AHB ALTAYYAR SP GIZ', N'NA', N'AHB', N'2644584C', N'Open', 1884409, N'NOT-CHECKED-IN')

    INSERT [dbo].[AmeliaPNRAdds] ([Pax ID], [Reservation], [Surname], [First Name], [Pax Type], [Phone], [Mobile], [Email], [Passport], [Fare Class], [Flight Date], [Flight Number], [Board], [Off], [Original Booking Date], [Leg Status], [Last Mod Date], [Booking Origin], [Agency Name], [Province], [User Base], [lng_Res_Segments_Id_Nmbr], [Flight Status], [lng_Res_Pax_Group_Id_Nmbr], [RSDesc]) VALUES (2181140, 1270742, N'ALRAHMI', N'GEHAD MURSHED SAEED MOHAMMED', N'A', N'96613833000', N'966554384312', N'JAMALSANAN@YAHOO.COM', NULL, N'DYOW', CAST(0xA41E0000 AS SmallDateTime), N'FO831', N'DMM', N'TAI', CAST(0xA41D0000 AS SmallDateTime), N'C', CAST(0xA41D0509 AS SmallDateTime), N'Web', N'DMM STARS TRAVEL', N'NA', N'DMM', N'2644585C', N'Open', 1884410, N'NOT-CHECKED-IN')

    INSERT [dbo].[AmeliaPNRAdds] ([Pax ID], [Reservation], [Surname], [First Name], [Pax Type], [Phone], [Mobile], [Email], [Passport], [Fare Class], [Flight Date], [Flight Number], [Board], [Off], [Original Booking Date], [Leg Status], [Last Mod Date], [Booking Origin], [Agency Name], [Province], [User Base], [lng_Res_Segments_Id_Nmbr], [Flight Status], [lng_Res_Pax_Group_Id_Nmbr], [RSDesc]) VALUES (2181141, 1270742, N'ALHATTAMI', N'FAISAL SARHAN ABDULLAH', N'A', N'966554384312', NULL, NULL, NULL, N'DYOW', CAST(0xA41E0000 AS SmallDateTime), N'FO831', N'DMM', N'TAI', CAST(0xA41D0000 AS SmallDateTime), N'C', CAST(0xA41D0509 AS SmallDateTime), N'Web', N'DMM STARS TRAVEL', N'NA', N'DMM', N'2644586C', N'Open', 1884411, N'NOT-CHECKED-IN')

    INSERT [dbo].[AmeliaPNRAdds] ([Pax ID], [Reservation], [Surname], [First Name], [Pax Type], [Phone], [Mobile], [Email], [Passport], [Fare Class], [Flight Date], [Flight Number], [Board], [Off], [Original Booking Date], [Leg Status], [Last Mod Date], [Booking Origin], [Agency Name], [Province], [User Base], [lng_Res_Segments_Id_Nmbr], [Flight Status], [lng_Res_Pax_Group_Id_Nmbr], [RSDesc]) VALUES (2181142, 1270743, N'ALKHAWLANI', N'ABDULLAH', N'A', N'01 241420', N'772220080', N'info@jubaritravel.com', NULL, N'DYOW', CAST(0xA4210000 AS SmallDateTime), N'FO873.', N'AHB', N'ADE', CAST(0xA41D0000 AS SmallDateTime), N'C', CAST(0xA41D0516 AS SmallDateTime), N'Web', N'SAH Jubari for Tourism Travel', N'Sana''a Intl', N'SAH', N'2644587C', N'Open', 1884412, N'NOT-CHECKED-IN')

    INSERT [dbo].[AmeliaPNRAdds] ([Pax ID], [Reservation], [Surname], [First Name], [Pax Type], [Phone], [Mobile], [Email], [Passport], [Fare Class], [Flight Date], [Flight Number], [Board], [Off], [Original Booking Date], [Leg Status], [Last Mod Date], [Booking Origin], [Agency Name], [Province], [User Base], [lng_Res_Segments_Id_Nmbr], [Flight Status], [lng_Res_Pax_Group_Id_Nmbr], [RSDesc]) VALUES (2181142, 1270743, N'ALKHAWLANI', N'ABDULLAH', N'A', N'01 241420', N'772220080', N'info@jubaritravel.com', NULL, N'DYOW', CAST(0xA4210000 AS SmallDateTime), N'FO873.', N'ADE', N'SAH', CAST(0xA41D0000 AS SmallDateTime), N'C', CAST(0xA41D0516 AS SmallDateTime), N'Web', N'SAH Jubari for Tourism Travel', N'Sana''a Intl', N'SAH', N'2644588C', N'Open', 1884412, N'NOT-CHECKED-IN')

    INSERT [dbo].[AmeliaPNRAdds] ([Pax ID], [Reservation], [Surname], [First Name], [Pax Type], [Phone], [Mobile], [Email], [Passport], [Fare Class], [Flight Date], [Flight Number], [Board], [Off], [Original Booking Date], [Leg Status], [Last Mod Date], [Booking Origin], [Agency Name], [Province], [User Base], [lng_Res_Segments_Id_Nmbr], [Flight Status], [lng_Res_Pax_Group_Id_Nmbr], [RSDesc]) VALUES (2181143, 1270744, N'FARIE', N'NASEF', N'A', N'24444444', N'777757667', N'yousuf.mohageb@gmail.com', NULL, N'CD YMN', CAST(0xA41E0000 AS SmallDateTime), N'FO830', N'SAH', N'TAI', CAST(0xA41D0000 AS SmallDateTime), N'C', CAST(0xA41D0521 AS SmallDateTime), N'Web', N'SAH ALEMAD1 TRAVEL', N'Sana''a Intl', N'SAH', N'2644589C', N'Open', 1884413, N'NOT-CHECKED-IN')

    INSERT [dbo].[AmeliaPNRAdds] ([Pax ID], [Reservation], [Surname], [First Name], [Pax Type], [Phone], [Mobile], [Email], [Passport], [Fare Class], [Flight Date], [Flight Number], [Board], [Off], [Original Booking Date], [Leg Status], [Last Mod Date], [Booking Origin], [Agency Name], [Province], [User Base], [lng_Res_Segments_Id_Nmbr], [Flight Status], [lng_Res_Pax_Group_Id_Nmbr], [RSDesc]) VALUES (2181144, 1270745, N'ABDULHAK', N'ABDULNASER MOHAMMED', N'A', N'733766666', NULL, NULL, NULL, N'CD YMN', CAST(0xA41E0000 AS SmallDateTime), N'FO830', N'SAH', N'TAI', CAST(0xA41D0000 AS SmallDateTime), N'C', CAST(0xA41D0523 AS SmallDateTime), N'Web', N'TAI ALHARBI TRAVEL', N'Taiz Al Janad', N'TAI', N'2644590C', N'Open', 1884414, N'NOT-CHECKED-IN')

    INSERT [dbo].[AmeliaPNRAdds] ([Pax ID], [Reservation], [Surname], [First Name], [Pax Type], [Phone], [Mobile], [Email], [Passport], [Fare Class], [Flight Date], [Flight Number], [Board], [Off], [Original Booking Date], [Leg Status], [Last Mod Date], [Booking Origin], [Agency Name], [Province], [User Base], [lng_Res_Segments_Id_Nmbr], [Flight Status], [lng_Res_Pax_Group_Id_Nmbr], [RSDesc]) VALUES (2181145, 1270746, N'ALAWLAQI', N'AHMED MOHAMMED', N'A', N'735044000', NULL, NULL, NULL, N'BYOW', CAST(0xA41E0000 AS SmallDateTime), N'FO820', N'ADE', N'JED', CAST(0xA41D0000 AS SmallDateTime), N'C', CAST(0xA41D052D AS SmallDateTime), N'Web', N'ADE ALTAMAYOZ', N'Aden Intl', N'ADE', N'2644591C', N'Open', 1884415, N'NOT-CHECKED-IN')

    INSERT [dbo].[AmeliaPNRAdds] ([Pax ID], [Reservation], [Surname], [First Name], [Pax Type], [Phone], [Mobile], [Email], [Passport], [Fare Class], [Flight Date], [Flight Number], [Board], [Off], [Original Booking Date], [Leg Status], [Last Mod Date], [Booking Origin], [Agency Name], [Province], [User Base], [lng_Res_Segments_Id_Nmbr], [Flight Status], [lng_Res_Pax_Group_Id_Nmbr], [RSDesc]) VALUES (2181146, 1270747, N'THABIT', N'KHALED', N'A', N'777287096', N'04289274', N'UTTBASIM@GMAIL.COM', NULL, N'CD YMN', CAST(0xA41E0000 AS SmallDateTime), N'FO830', N'SAH', N'TAI', CAST(0xA41D0000 AS SmallDateTime), N'C', CAST(0xA41D0535 AS SmallDateTime), N'Web', N'TAI UNIVERSAL', N'Taiz Al Janad', N'TAI', N'2644592C', N'Open', 1884416, N'NOT-CHECKED-IN')

    INSERT [dbo].[AmeliaPNRAdds] ([Pax ID], [Reservation], [Surname], [First Name], [Pax Type], [Phone], [Mobile], [Email], [Passport], [Fare Class], [Flight Date], [Flight Number], [Board], [Off], [Original Booking Date], [Leg Status], [Last Mod Date], [Booking Origin], [Agency Name], [Province], [User Base], [lng_Res_Segments_Id_Nmbr], [Flight Status], [lng_Res_Pax_Group_Id_Nmbr], [RSDesc]) VALUES (2181147, 1270747, N'THABIT', N'MOHAMMED', N'A', N'777287096', NULL, NULL, NULL, N'CD YMN', CAST(0xA41E0000 AS SmallDateTime), N'FO830', N'SAH', N'TAI', CAST(0xA41D0000 AS SmallDateTime), N'C', CAST(0xA41D0535 AS SmallDateTime), N'Web', N'TAI UNIVERSAL', N'Taiz Al Janad', N'TAI', N'2644593C', N'Open', 1884417, N'NOT-CHECKED-IN')

    INSERT [dbo].[AmeliaPNRAdds] ([Pax ID], [Reservation], [Surname], [First Name], [Pax Type], [Phone], [Mobile], [Email], [Passport], [Fare Class], [Flight Date], [Flight Number], [Board], [Off], [Original Booking Date], [Leg Status], [Last Mod Date], [Booking Origin], [Agency Name], [Province], [User Base], [lng_Res_Segments_Id_Nmbr], [Flight Status], [lng_Res_Pax_Group_Id_Nmbr], [RSDesc]) VALUES (2181148, 1270747, N'THABIT', N'WALEED', N'A', N'777287096', NULL, NULL, NULL, N'CD YMN', CAST(0xA41E0000 AS SmallDateTime), N'FO830', N'SAH', N'TAI', CAST(0xA41D0000 AS SmallDateTime), N'C', CAST(0xA41D0535 AS SmallDateTime), N'Web', N'TAI UNIVERSAL', N'Taiz Al Janad', N'TAI', N'2644594C', N'Open', 1884418, N'NOT-CHECKED-IN')

    INSERT [dbo].[AmeliaPNRAdds] ([Pax ID], [Reservation], [Surname], [First Name], [Pax Type], [Phone], [Mobile], [Email], [Passport], [Fare Class], [Flight Date], [Flight Number], [Board], [Off], [Original Booking Date], [Leg Status], [Last Mod Date], [Booking Origin], [Agency Name], [Province], [User Base], [lng_Res_Segments_Id_Nmbr], [Flight Status], [lng_Res_Pax_Group_Id_Nmbr], [RSDesc]) VALUES (2181149, 1270748, N'ABDULADHEM', N'ASHWAQ ABDULJALIL', N'A', N'01469792', N'736778830', N'altakaritt@yahoo.com', NULL, N'CD YMN', CAST(0xA41E0000 AS SmallDateTime), N'FO831', N'TAI', N'SAH', CAST(0xA41D0000 AS SmallDateTime), N'C', CAST(0xA41D053D AS SmallDateTime), N'Web', N'SAH ALTAKARI Travel', N'Sana''a Intl', N'SAH', N'2644595C', N'Open', 1884419, N'NOT-CHECKED-IN')

    INSERT [dbo].[AmeliaPNRAdds] ([Pax ID], [Reservation], [Surname], [First Name], [Pax Type], [Phone], [Mobile], [Email], [Passport], [Fare Class], [Flight Date], [Flight Number], [Board], [Off], [Original Booking Date], [Leg Status], [Last Mod Date], [Booking Origin], [Agency Name], [Province], [User Base], [lng_Res_Segments_Id_Nmbr], [Flight Status], [lng_Res_Pax_Group_Id_Nmbr], [RSDesc]) VALUES (2181150, 1270749, N'YASLAM', N'MOHAHMMED MEHDI', N'A', N'02255351', N'738109209', N'SALAHMADAN@YAHOO.COM', NULL, N'A-YMN', CAST(0xA41F0000 AS SmallDateTime), N'FO109', N'ADE', N'SAH', CAST(0xA41D0000 AS SmallDateTime), N'C', CAST(0xA41D0541 AS SmallDateTime), N'Web', N'ADE Binmadan Travel', N'Aden Intl', N'SAH', N'2644596C', N'Open', 1884420, N'NOT-CHECKED-IN')

    INSERT [dbo].[AmeliaPNRAdds] ([Pax ID], [Reservation], [Surname], [First Name], [Pax Type], [Phone], [Mobile], [Email], [Passport], [Fare Class], [Flight Date], [Flight Number], [Board], [Off], [Original Booking Date], [Leg Status], [Last Mod Date], [Booking Origin], [Agency Name], [Province], [User Base], [lng_Res_Segments_Id_Nmbr], [Flight Status], [lng_Res_Pax_Group_Id_Nmbr], [RSDesc]) VALUES (2181151, 1270750, N'SALEH', N'MOHAMMED', N'A', N'777429171', NULL, NULL, NULL, N'CD YMN', CAST(0xA41F0000 AS SmallDateTime), N'FO187', N'GXF', N'ADE', CAST(0xA41D0000 AS SmallDateTime), N'C', CAST(0xA41D055A AS SmallDateTime), N'Web', N'GXF ALWAFA TRAVEL', N'Seiyun', N'GXF', N'2644597C', N'Open', 1884421, N'NOT-CHECKED-IN')

    INSERT [dbo].[AmeliaPNRAdds] ([Pax ID], [Reservation], [Surname], [First Name], [Pax Type], [Phone], [Mobile], [Email], [Passport], [Fare Class], [Flight Date], [Flight Number], [Board], [Off], [Original Booking Date], [Leg Status], [Last Mod Date], [Booking Origin], [Agency Name], [Province], [User Base], [lng_Res_Segments_Id_Nmbr], [Flight Status], [lng_Res_Pax_Group_Id_Nmbr], [RSDesc]) VALUES (2181151, 1270750, N'SALEH', N'MOHAMMED', N'A', N'777429171', NULL, NULL, NULL, N'CD YMN', CAST(0xA41F0000 AS SmallDateTime), N'FO187', N'ADE', N'SAH', CAST(0xA41D0000 AS SmallDateTime), N'C', CAST(0xA41D055A AS SmallDateTime), N'Web', N'GXF ALWAFA TRAVEL', N'Seiyun', N'GXF', N'2644598C', N'Open', 1884421, N'NOT-CHECKED-IN')

    INSERT [dbo].[AmeliaPNRAdds] ([Pax ID], [Reservation], [Surname], [First Name], [Pax Type], [Phone], [Mobile], [Email], [Passport], [Fare Class], [Flight Date], [Flight Number], [Board], [Off], [Original Booking Date], [Leg Status], [Last Mod Date], [Booking Origin], [Agency Name], [Province], [User Base], [lng_Res_Segments_Id_Nmbr], [Flight Status], [lng_Res_Pax_Group_Id_Nmbr], [RSDesc]) VALUES (2181152, 1269942, N'SHARAFALDEEN', N'AHMED ABDULSALAM', N'A', N'701355066', N'777793389', NULL, NULL, N'DYOW', CAST(0xA43A0000 AS SmallDateTime), N'FO831', N'DMM', N'TAI', CAST(0xA41D0000 AS SmallDateTime), N'C', CAST(0xA41D055E AS SmallDateTime), N'Web', N'TAI ALHARBI TRAVEL', N'Taiz Al Janad', N'TAI', N'2644599C', N'Open', 1883092, N'NOT-CHECKED-IN')

    INSERT [dbo].[AmeliaPNRAdds] ([Pax ID], [Reservation], [Surname], [First Name], [Pax Type], [Phone], [Mobile], [Email], [Passport], [Fare Class], [Flight Date], [Flight Number], [Board], [Off], [Original Booking Date], [Leg Status], [Last Mod Date], [Booking Origin], [Agency Name], [Province], [User Base], [lng_Res_Segments_Id_Nmbr], [Flight Status], [lng_Res_Pax_Group_Id_Nmbr], [RSDesc]) VALUES (2181153, 1270751, N'ALHABSHI', N'DORRAH', N'A', N'7772211', NULL, NULL, NULL, N'A-YMN', CAST(0xA4260000 AS SmallDateTime), N'FO186', N'ADE', N'GXF', CAST(0xA41D0000 AS SmallDateTime), N'C', CAST(0xA41D055F AS SmallDateTime), N'Web', N'GXF ALWAFA TRAVEL', N'Seiyun', N'GXF', N'2644600C', N'Open', 1884422, N'NOT-CHECKED-IN')

    I will need to show the last transaction after from both tables . here are just sample data..

    Thank you

    Alhakimi

  • Quick note, this seemingly simple task may be slightly more complex than it appears to be at first. ๐Ÿ˜‰ , i.e. in your data sample there are no updates to the existing records, the multiple PAX records are for the returning segments. This means that an additional key or grouping attribute is needed for the set, my suspicion is that the lng_Res_Segments_Id_Nmbr could be used, don't know enough about the source of the data to tell though.

    Quick question, is this data coming off a GDS or is it from some small CRS?

    ๐Ÿ˜Ž

    Add some updates to the data set, changing the RSDesc to CHECKED-IN.

    INSERT [dbo].[AmeliaPNRUpdates] ([Pax ID], [Reservation], [Surname], [First Name], [Pax Type], [Phone], [Mobile], [Email], [Passport], [Fare Class], [Flight Date], [Flight Number], [Board], [Off], [Original Booking Date], [Leg Status], [Last Mod Date], [Booking Origin], [Agency Name], [Province], [User Base], [lng_Res_Segments_Id_Nmbr], [Flight Status], [lng_Res_Pax_Group_Id_Nmbr], [RSDesc]) VALUES (2181140, 1270742, N'ALRAHMI', N'GEHAD MURSHED SAEED MOHAMMED', N'A', N'96613833000', N'966554384312', N'JAMALSANAN@YAHOO.COM', NULL, N'DYOW', CAST(0xA41E0000 AS SmallDateTime), N'FO831', N'DMM', N'TAI', CAST(0xA41D0000 AS SmallDateTime), N'C', CAST(0xA41D0541 AS SmallDateTime), N'Web', N'DMM STARS TRAVEL', N'NA', N'DMM', N'2644585C', N'Open', 1884410, N'CHECKED-IN')

    INSERT [dbo].[AmeliaPNRUpdates] ([Pax ID], [Reservation], [Surname], [First Name], [Pax Type], [Phone], [Mobile], [Email], [Passport], [Fare Class], [Flight Date], [Flight Number], [Board], [Off], [Original Booking Date], [Leg Status], [Last Mod Date], [Booking Origin], [Agency Name], [Province], [User Base], [lng_Res_Segments_Id_Nmbr], [Flight Status], [lng_Res_Pax_Group_Id_Nmbr], [RSDesc]) VALUES (2181141, 1270742, N'ALHATTAMI', N'FAISAL SARHAN ABDULLAH', N'A', N'966554384312', NULL, NULL, NULL, N'DYOW', CAST(0xA41E0000 AS SmallDateTime), N'FO831', N'DMM', N'TAI', CAST(0xA41D0000 AS SmallDateTime), N'C', CAST(0xA41D0541 AS SmallDateTime), N'Web', N'DMM STARS TRAVEL', N'NA', N'DMM', N'2644586C', N'Open', 1884411, N'CHECKED-IN')

    INSERT [dbo].[AmeliaPNRUpdates] ([Pax ID], [Reservation], [Surname], [First Name], [Pax Type], [Phone], [Mobile], [Email], [Passport], [Fare Class], [Flight Date], [Flight Number], [Board], [Off], [Original Booking Date], [Leg Status], [Last Mod Date], [Booking Origin], [Agency Name], [Province], [User Base], [lng_Res_Segments_Id_Nmbr], [Flight Status], [lng_Res_Pax_Group_Id_Nmbr], [RSDesc]) VALUES (2181142, 1270743, N'ALKHAWLANI', N'ABDULLAH', N'A', N'01 241420', N'772220080', N'info@jubaritravel.com', NULL, N'DYOW', CAST(0xA4210000 AS SmallDateTime), N'FO873.', N'AHB', N'ADE', CAST(0xA41D0000 AS SmallDateTime), N'C', CAST(0xA41D0541 AS SmallDateTime), N'Web', N'SAH Jubari for Tourism Travel', N'Sana''a Intl', N'SAH', N'2644587C', N'Open', 1884412, N'CHECKED-IN')

    INSERT [dbo].[AmeliaPNRUpdates] ([Pax ID], [Reservation], [Surname], [First Name], [Pax Type], [Phone], [Mobile], [Email], [Passport], [Fare Class], [Flight Date], [Flight Number], [Board], [Off], [Original Booking Date], [Leg Status], [Last Mod Date], [Booking Origin], [Agency Name], [Province], [User Base], [lng_Res_Segments_Id_Nmbr], [Flight Status], [lng_Res_Pax_Group_Id_Nmbr], [RSDesc]) VALUES (2181142, 1270743, N'ALKHAWLANI', N'ABDULLAH', N'A', N'01 241420', N'772220080', N'info@jubaritravel.com', NULL, N'DYOW', CAST(0xA4210000 AS SmallDateTime), N'FO873.', N'ADE', N'SAH', CAST(0xA41D0000 AS SmallDateTime), N'C', CAST(0xA41D0541 AS SmallDateTime), N'Web', N'SAH Jubari for Tourism Travel', N'Sana''a Intl', N'SAH', N'2644588C', N'Open', 1884412, N'CHECKED-IN')

    Retrieve the latest record for each combination of PAX/SegmentID

    USE tempdb;

    GO

    SET NOCOUNT ON;

    ;WITH BASE_DATA AS

    (

    SELECT

    PA.[Pax ID]

    ,PA.[Reservation]

    ,PA.[Surname]

    ,PA.[First Name]

    ,PA.[Pax Type]

    ,PA.[Phone]

    ,PA.[Mobile]

    ,PA.[Email]

    ,PA.[Passport]

    ,PA.[Fare Class]

    ,PA.[Flight Date]

    ,PA.[Flight Number]

    ,PA.[Board]

    ,PA.[Off]

    ,PA.[Original Booking Date]

    ,PA.[Leg Status]

    ,PA.[Last Mod Date]

    ,PA.[Booking Origin]

    ,PA.[Agency Name]

    ,PA.[Province]

    ,PA.[User Base]

    ,PA.[lng_Res_Segments_Id_Nmbr]

    ,PA.[Flight Status]

    ,PA.[lng_Res_Pax_Group_Id_Nmbr]

    ,PA.[RSDesc]

    FROM dbo.AmeliaPNRAdds PA

    UNION ALL

    SELECT

    PU.[Pax ID]

    ,PU.[Reservation]

    ,PU.[Surname]

    ,PU.[First Name]

    ,PU.[Pax Type]

    ,PU.[Phone]

    ,PU.[Mobile]

    ,PU.[Email]

    ,PU.[Passport]

    ,PU.[Fare Class]

    ,PU.[Flight Date]

    ,PU.[Flight Number]

    ,PU.[Board]

    ,PU.[Off]

    ,PU.[Original Booking Date]

    ,PU.[Leg Status]

    ,PU.[Last Mod Date]

    ,PU.[Booking Origin]

    ,PU.[Agency Name]

    ,PU.[Province]

    ,PU.[User Base]

    ,PU.[lng_Res_Segments_Id_Nmbr]

    ,PU.[Flight Status]

    ,PU.[lng_Res_Pax_Group_Id_Nmbr]

    ,PU.[RSDesc]

    FROM dbo.AmeliaPNRUpdates PU

    )

    ,SEGMENT_ORDERED_SET AS

    (

    SELECT

    ROW_NUMBER() OVER

    (

    PARTITION BY BD.[Pax ID], [lng_Res_Segments_Id_Nmbr]

    ORDER BY BD.[Last Mod Date] DESC

    ) AS PAXSEG_RID

    ,BD.[Pax ID]

    ,BD.[Reservation]

    ,BD.[Surname]

    ,BD.[First Name]

    ,BD.[Pax Type]

    ,BD.[Phone]

    ,BD.[Mobile]

    ,BD.[Email]

    ,BD.[Passport]

    ,BD.[Fare Class]

    ,BD.[Flight Date]

    ,BD.[Flight Number]

    ,BD.[Board]

    ,BD.[Off]

    ,BD.[Original Booking Date]

    ,BD.[Leg Status]

    ,BD.[Last Mod Date]

    ,BD.[Booking Origin]

    ,BD.[Agency Name]

    ,BD.[Province]

    ,BD.[User Base]

    ,BD.[lng_Res_Segments_Id_Nmbr]

    ,BD.[Flight Status]

    ,BD.[lng_Res_Pax_Group_Id_Nmbr]

    ,BD.[RSDesc]

    FROM BASE_DATA BD

    )

    SELECT

    SOS.[Pax ID]

    ,SOS.[Reservation]

    ,SOS.[Surname]

    ,SOS.[First Name]

    ,SOS.[Pax Type]

    ,SOS.[Phone]

    ,SOS.[Mobile]

    ,SOS.[Email]

    ,SOS.[Passport]

    ,SOS.[Fare Class]

    ,SOS.[Flight Date]

    ,SOS.[Flight Number]

    ,SOS.[Board]

    ,SOS.[Off]

    ,SOS.[Original Booking Date]

    ,SOS.[Leg Status]

    ,SOS.[Last Mod Date]

    ,SOS.[Booking Origin]

    ,SOS.[Agency Name]

    ,SOS.[Province]

    ,SOS.[User Base]

    ,SOS.[lng_Res_Segments_Id_Nmbr]

    ,SOS.[Flight Status]

    ,SOS.[lng_Res_Pax_Group_Id_Nmbr]

    ,SOS.RSDesc

    FROM SEGMENT_ORDERED_SET SOS

    WHERE SOS.PAXSEG_RID = 1

    ;

    Results

    Pax ID Reservation Surname First Name Pax Type Phone Mobile Email Passport Fare Class Flight Date Flight Number Board Off Original Booking Date Leg Status Last Mod Date Booking Origin Agency Name Province User Base lng_Res_Segments_Id_Nmbr Flight Status lng_Res_Pax_Group_Id_Nmbr RSDesc

    ----------- ----------- ---------------------------------------- ---------------------------------------- -------- ---------------- ---------------- -------------------------- -------------------- ---------- ----------------------- ------------- ----- ---- ----------------------- ---------- ----------------------- -------------- ---------------------------------------- ---------------------------------------- --------- ------------------------ ------------- ------------------------- ------------------------------

    2180259 1270364 ALI ABDULLAH ABDO A 700067263 NULL NULL NULL A-YMN 2015-01-11 00:00:00 FO500 SAH TAI 2015-01-10 00:00:00 C 2015-01-11 09:57:00 Web TAI Yemen Alalya Travel Taiz Al Janad TAI 2643572C Closed 1883648 BORD

    2180261 1270367 ALMAMARI EBRAHIM AHMED A 736631231 NULL NULL NULL A-YMN 2015-01-11 00:00:00 FO501 TAI SAH 2015-01-10 00:00:00 C 2015-01-11 15:22:00 Web TAI ZAIN TRAVEL Taiz Al Janad TAI 2643574C Open 1883650 BORD

    2180262 1270367 ALMASHWALI AHMED ALi A 736631231 NULL NULL NULL A-YMN 2015-01-11 00:00:00 FO501 TAI SAH 2015-01-10 00:00:00 C 2015-01-11 15:22:00 Web TAI ZAIN TRAVEL Taiz Al Janad TAI 2643575C Open 1883651 BORD

    2180274 1270374 ABDOO IBRAHEEM A 735644442 498710 THABIT20141@GMAIL.COM NULL A-YMN 2015-01-11 00:00:00 FO501 TAI SAH 2015-01-10 00:00:00 C 2015-01-11 15:22:00 Web SAH ALSHAEER Travel Sana'a Intl SAH 2643592C Open 1883661 BORD

    2180275 1270374 ALMUSEE ADEL A 498710 NULL NULL NULL A-YMN 2015-01-11 00:00:00 FO501 TAI SAH 2015-01-10 00:00:00 C 2015-01-11 15:22:00 Web SAH ALSHAEER Travel Sana'a Intl SAH 2643593C Open 1883662 BORD

    2180276 1270374 ALRADAEI ALI A 498710 NULL NULL NULL A-YMN 2015-01-11 00:00:00 FO501 TAI SAH 2015-01-10 00:00:00 C 2015-01-11 15:22:00 Web SAH ALSHAEER Travel Sana'a Intl SAH 2643594C Open 1883663 BORD

    2180277 1270374 GAMEL GAMAL A 498710 NULL NULL NULL A-YMN 2015-01-11 00:00:00 FO501 TAI SAH 2015-01-10 00:00:00 C 2015-01-11 15:22:00 Web SAH ALSHAEER Travel Sana'a Intl SAH 2643595C Open 1883664 BORD

    2180278 1270374 HUSSEN ABDULARAHMAN A 498710 NULL NULL NULL A-YMN 2015-01-11 00:00:00 FO501 TAI SAH 2015-01-10 00:00:00 C 2015-01-11 15:22:00 Web SAH ALSHAEER Travel Sana'a Intl SAH 2643596C Open 1883665 BORD

    2180279 1270375 ABDULSALAM ESMAIL ABDULFATAH A 770107569 NULL alezzy.jamal@gmail.com NULL A-YMN 2015-01-11 00:00:00 FO500 SAH TAI 2015-01-10 00:00:00 C 2015-01-11 08:37:00 Web SAH UNIVEERSAL APT Sana'a Intl SAH 2643597C Closed 1883666 BORD

    2180280 1270376 FARUOQ AMEEN A 735644442 498710 THABIT20141@GMAIL.COM NULL A-YMN 2015-01-11 00:00:00 FO501 TAI SAH 2015-01-10 00:00:00 C 2015-01-11 15:22:00 Web SAH ALSHAEER Travel Sana'a Intl SAH 2643598C Open 1883667 BORD

    2180281 1270376 OBAID MOHAMMED A 498710 NULL NULL NULL A-YMN 2015-01-11 00:00:00 FO501 TAI SAH 2015-01-10 00:00:00 C 2015-01-11 15:22:00 Web SAH ALSHAEER Travel Sana'a Intl SAH 2643599C Open 1883668 BORD

    2180282 1270376 ALKHRAZ AHMED A 498710 NULL NULL NULL A-YMN 2015-01-11 00:00:00 FO501 TAI SAH 2015-01-10 00:00:00 C 2015-01-11 15:22:00 Web SAH ALSHAEER Travel Sana'a Intl SAH 2643600C Open 1883669 BORD

    2180283 1270376 KHALED ALHGRH A 498710 NULL NULL NULL A-YMN 2015-01-11 00:00:00 FO501 TAI SAH 2015-01-10 00:00:00 C 2015-01-11 15:22:00 Web SAH ALSHAEER Travel Sana'a Intl SAH 2643601C Open 1883670 BORD

    2180292 1270380 ALMGHAS TAMER ABDULAZIZ A 241420 733499596 myholidays.ye@gmail.com NULL A-YMN 2015-01-11 00:00:00 FO501 TAI SAH 2015-01-10 00:00:00 C 2015-01-11 15:22:00 Web SAH Jubari for Tourism Travel Sana'a Intl SAH 2643612C Open 1883676 BORD

    2180312 1270393 ALHARAZI TALAL A 770409470 NULL ALAQEEQTRAVEL@GMAIL.COM NULL A-YMN 2015-01-11 00:00:00 FO500 SAH TAI 2015-01-10 00:00:00 C 2015-01-11 09:47:00 Web SAH ALAQEEQ TRAVEL Sana'a Intl SAH 2643637C Closed 1883691 BORD

    2180315 1270395 T.B.A. Adult A NULL 01-206908 NULL -1 NULL 2015-01-19 00:00:00 FO820 ADE JED 2015-01-10 00:00:00 X 2015-01-10 23:33:00 Amelia FELIX AIRWAYS Sana'a Intl AAP 2643640C Open 1883694 NOT-CHECKED-IN

    2180316 1270395 T.B.A. Adult A NULL 01-206908 NULL -1 NULL 2015-02-13 00:00:00 FO825 JED ADE 2015-01-10 00:00:00 X 2015-01-10 23:33:00 Amelia FELIX AIRWAYS Sana'a Intl AAP 2643641C Open 1883694 NOT-CHECKED-IN

    2180320 1270399 SAIF GAWHARAH MOHAMED A 772858687 NULL NULL NULL A-YMN 2015-01-11 00:00:00 FO501 TAI SAH 2015-01-10 00:00:00 C 2015-01-11 15:22:00 Web TAI ALHARBI TRAVEL Taiz Al Janad TAI 2643645C Open 1883698 BORD

    2181129 1270735 ALAHMDI SABAH MOSLAH A 773756780 NULL NULL NULL A-YMN 2015-01-15 00:00:00 FO196 SAH AAY 2015-01-11 00:00:00 C 2015-01-11 21:03:00 Web AAY ALMUHANDIS TRAVEL Al Ghaydah AAY 2644572C Open 1884400 NOT-CHECKED-IN

    2181130 1270736 ABDULRAB MOHAMMED ABDULLAH A 711259123 NULL makkah2013@live.com NULL N 2015-02-02 00:00:00 FO820 ADE JED 2015-01-11 00:00:00 C 2015-01-11 21:03:00 Web ADE Makkah Travel Aden Intl ADE 2644574C Open 1884401 NOT-CHECKED-IN

    2181131 1270736 ABDULRAB MOHAMMED ABDULLAH A 711259123 NULL makkah2013@live.com NULL Z 2015-02-20 00:00:00 FO825 JED ADE 2015-01-11 00:00:00 C 2015-01-11 21:03:00 Web ADE Makkah Travel Aden Intl ADE 2644573C Open 1884401 NOT-CHECKED-IN

    2181132 1270737 HONEAFAN NAJI SAAD A 774077873 774077873 NULL NULL A-YMN 2015-01-15 00:00:00 FO196 SAH AAY 2015-01-11 00:00:00 C 2015-01-11 21:06:00 Web AAY ALMUHANDIS TRAVEL Al Ghaydah AAY 2644575C Open 1884402 NOT-CHECKED-IN

    2181133 1270738 ALJAIFI YAHYA HEZAM A 771240040 770700222 NULL NULL A-YMN 2015-01-13 00:00:00 FO109 ADE SAH 2015-01-11 00:00:00 C 2015-01-11 21:16:00 Web SAH ALMOKARRAM TOURS Sana'a Intl SAH 2644576C Open 1884403 NOT-CHECKED-IN

    2181134 1270739 ALHABRI AHMED ABDULALEM AHMED A 96613833000 966551355958 JAMALSANAN@YAHOO.COM NULL DYOW 2015-01-12 00:00:00 FO831 DMM TAI 2015-01-11 00:00:00 C 2015-01-11 21:17:00 Web DMM STARS TRAVEL NA DMM 2644577C Open 1884404 NOT-CHECKED-IN

    2181135 1270740 baazab dokhanh mohammed salem A 770570561 NULL NULL NULL A-YMN 2015-01-12 00:00:00 FO108 SAH ADE 2015-01-11 00:00:00 C 2015-01-11 21:17:00 Web SAH ALSHAEER Travel Sana'a Intl SAH 2644578C Open 1884405 NOT-CHECKED-IN

    2181136 1270740 baazab maryam ali mohammed A 770570561 NULL NULL NULL A-YMN 2015-01-12 00:00:00 FO108 SAH ADE 2015-01-11 00:00:00 C 2015-01-11 21:17:00 Web SAH ALSHAEER Travel Sana'a Intl SAH 2644579C Open 1884406 NOT-CHECKED-IN

    2181137 1270740 awdh tahani qahtan C 770570561 NULL NULL NULL A-YMN 2015-01-12 00:00:00 FO108 SAH ADE 2015-01-11 00:00:00 C 2015-01-11 21:17:00 Web SAH ALSHAEER Travel Sana'a Intl SAH 2644580C Open 1884407 NOT-CHECKED-IN

    2181138 1270741 ALHUBAISHI IFTIKAR MAHFOUDH A 966530048200 966530048200 MOHD.GABER@YAHOO.COM NULL DYOW 2015-01-15 00:00:00 FO873. AHB ADE 2015-01-11 00:00:00 C 2015-01-11 21:18:00 Web AHB ALTAYYAR SP GIZ NA AHB 2644581C Open 1884408 NOT-CHECKED-IN

    2181138 1270741 ALHUBAISHI IFTIKAR MAHFOUDH A 966530048200 966530048200 MOHD.GABER@YAHOO.COM NULL DYOW 2015-01-15 00:00:00 FO873. ADE SAH 2015-01-11 00:00:00 C 2015-01-11 21:18:00 Web AHB ALTAYYAR SP GIZ NA AHB 2644583C Open 1884408 NOT-CHECKED-IN

    2181139 1270741 ALSALAHI RAHF ALMUDAFAR AHMED C 966530048200 NULL NULL NULL DYOW 2015-01-15 00:00:00 FO873. AHB ADE 2015-01-11 00:00:00 C 2015-01-11 21:18:00 Web AHB ALTAYYAR SP GIZ NA AHB 2644582C Open 1884409 NOT-CHECKED-IN

    2181139 1270741 ALSALAHI RAHF ALMUDAFAR AHMED C 966530048200 NULL NULL NULL DYOW 2015-01-15 00:00:00 FO873. ADE SAH 2015-01-11 00:00:00 C 2015-01-11 21:18:00 Web AHB ALTAYYAR SP GIZ NA AHB 2644584C Open 1884409 NOT-CHECKED-IN

    2181140 1270742 ALRAHMI GEHAD MURSHED SAEED MOHAMMED A 96613833000 966554384312 JAMALSANAN@YAHOO.COM NULL DYOW 2015-01-12 00:00:00 FO831 DMM TAI 2015-01-11 00:00:00 C 2015-01-11 22:25:00 Web DMM STARS TRAVEL NA DMM 2644585C Open 1884410 CHECKED-IN

    2181141 1270742 ALHATTAMI FAISAL SARHAN ABDULLAH A 966554384312 NULL NULL NULL DYOW 2015-01-12 00:00:00 FO831 DMM TAI 2015-01-11 00:00:00 C 2015-01-11 22:25:00 Web DMM STARS TRAVEL NA DMM 2644586C Open 1884411 CHECKED-IN

    2181142 1270743 ALKHAWLANI ABDULLAH A 01 241420 772220080 info@jubaritravel.com NULL DYOW 2015-01-15 00:00:00 FO873. AHB ADE 2015-01-11 00:00:00 C 2015-01-11 22:25:00 Web SAH Jubari for Tourism Travel Sana'a Intl SAH 2644587C Open 1884412 CHECKED-IN

    2181142 1270743 ALKHAWLANI ABDULLAH A 01 241420 772220080 info@jubaritravel.com NULL DYOW 2015-01-15 00:00:00 FO873. ADE SAH 2015-01-11 00:00:00 C 2015-01-11 22:25:00 Web SAH Jubari for Tourism Travel Sana'a Intl SAH 2644588C Open 1884412 CHECKED-IN

    2181143 1270744 FARIE NASEF A 24444444 777757667 yousuf.mohageb@gmail.com NULL CD YMN 2015-01-12 00:00:00 FO830 SAH TAI 2015-01-11 00:00:00 C 2015-01-11 21:53:00 Web SAH ALEMAD1 TRAVEL Sana'a Intl SAH 2644589C Open 1884413 NOT-CHECKED-IN

    2181144 1270745 ABDULHAK ABDULNASER MOHAMMED A 733766666 NULL NULL NULL CD YMN 2015-01-12 00:00:00 FO830 SAH TAI 2015-01-11 00:00:00 C 2015-01-11 21:55:00 Web TAI ALHARBI TRAVEL Taiz Al Janad TAI 2644590C Open 1884414 NOT-CHECKED-IN

    2181145 1270746 ALAWLAQI AHMED MOHAMMED A 735044000 NULL NULL NULL BYOW 2015-01-12 00:00:00 FO820 ADE JED 2015-01-11 00:00:00 C 2015-01-11 22:05:00 Web ADE ALTAMAYOZ Aden Intl ADE 2644591C Open 1884415 NOT-CHECKED-IN

    2181146 1270747 THABIT KHALED A 777287096 04289274 UTTBASIM@GMAIL.COM NULL CD YMN 2015-01-12 00:00:00 FO830 SAH TAI 2015-01-11 00:00:00 C 2015-01-11 22:13:00 Web TAI UNIVERSAL Taiz Al Janad TAI 2644592C Open 1884416 NOT-CHECKED-IN

    2181147 1270747 THABIT MOHAMMED A 777287096 NULL NULL NULL CD YMN 2015-01-12 00:00:00 FO830 SAH TAI 2015-01-11 00:00:00 C 2015-01-11 22:13:00 Web TAI UNIVERSAL Taiz Al Janad TAI 2644593C Open 1884417 NOT-CHECKED-IN

    2181148 1270747 THABIT WALEED A 777287096 NULL NULL NULL CD YMN 2015-01-12 00:00:00 FO830 SAH TAI 2015-01-11 00:00:00 C 2015-01-11 22:13:00 Web TAI UNIVERSAL Taiz Al Janad TAI 2644594C Open 1884418 NOT-CHECKED-IN

    2181149 1270748 ABDULADHEM ASHWAQ ABDULJALIL A 01469792 736778830 altakaritt@yahoo.com NULL CD YMN 2015-01-12 00:00:00 FO831 TAI SAH 2015-01-11 00:00:00 C 2015-01-11 22:21:00 Web SAH ALTAKARI Travel Sana'a Intl SAH 2644595C Open 1884419 NOT-CHECKED-IN

    2181150 1270749 YASLAM MOHAHMMED MEHDI A 02255351 738109209 SALAHMADAN@YAHOO.COM NULL A-YMN 2015-01-13 00:00:00 FO109 ADE SAH 2015-01-11 00:00:00 C 2015-01-11 22:25:00 Web ADE Binmadan Travel Aden Intl SAH 2644596C Open 1884420 NOT-CHECKED-IN

    2181151 1270750 SALEH MOHAMMED A 777429171 NULL NULL NULL CD YMN 2015-01-13 00:00:00 FO187 GXF ADE 2015-01-11 00:00:00 C 2015-01-11 22:50:00 Web GXF ALWAFA TRAVEL Seiyun GXF 2644597C Open 1884421 NOT-CHECKED-IN

    2181151 1270750 SALEH MOHAMMED A 777429171 NULL NULL NULL CD YMN 2015-01-13 00:00:00 FO187 ADE SAH 2015-01-11 00:00:00 C 2015-01-11 22:50:00 Web GXF ALWAFA TRAVEL Seiyun GXF 2644598C Open 1884421 NOT-CHECKED-IN

    2181152 1269942 SHARAFALDEEN AHMED ABDULSALAM A 701355066 777793389 NULL NULL DYOW 2015-02-09 00:00:00 FO831 DMM TAI 2015-01-11 00:00:00 C 2015-01-11 22:54:00 Web TAI ALHARBI TRAVEL Taiz Al Janad TAI 2644599C Open 1883092 NOT-CHECKED-IN

    2181153 1270751 ALHABSHI DORRAH A 7772211 NULL NULL NULL A-YMN 2015-01-20 00:00:00 FO186 ADE GXF 2015-01-11 00:00:00 C 2015-01-11 22:55:00 Web GXF ALWAFA TRAVEL Seiyun GXF 2644600C Open 1884422 NOT-CHECKED-IN

  • Thank you my friend , i need to add small where condation to show bookings that has flight date greater than today and the leg status = 'c' , flight status = 'open' , rsdesc='NOT-CHECKED-IN'

    And reference to your question this is comming from small CRS.

    Thank you again.

    Rashed

  • CELKO (2/24/2015)


    ...

    You should follow ISO-8601 rules for displaying temporal data. This is the second most used ISO standard on Earth (the metric system is #1) and you do not know it! It is the only format allowed in ANSI/ISO Standard SQL. But you picked the worst, most ambiguous local dialect display format on Earth.

    ...

    Avoid dialect in favor of ANSI/ISO Standard SQL. Is โ€œ1/2/xxxxโ€ in February or January? DUH!

    I think we have been through this few times before, this is an MS SQL Server forum which makes the T-SQL dialect perfectly acceptable.

    ๐Ÿ˜Ž

    Your posting is full of fundamental errors. Not little errors, but fundamental errors. I spent decades of my life developing SQL standards, so when I tell you to stop programming you know I have some authority. You are doing everything wrong.

    Again, while you are blaming the victim of an industry wide bad practice you are also exposing your ignorance on that subject.

    >> I have two Tables, one has New records [sic] and another table has the same columns but with updates transactions, I need to generate report that shows the last transaction occurs to each passenger, I need only single record [sic] with the last update or status.<<

    ...

    But even worse than all of this, is that you have two tables with the same structure. Chris Date and Dave McGoveran have a lot to say about this design disaster. I called it โ€œattribute splittingโ€ in my articles. In Mathematics, are there two or more sets of integers? NO! That is insane. This is the mathematical principle why we cannot have your two sets. An SQL programmer would have a temporal attribute in the table (set).

    Most if not all GDSs and and in fact many CRSs modelled after the former, leave no other option but importing a full or partial set which then has to be compared to the existing set. This is normally done by using a staging table with the same structure as the existing one, nothing wrong with that.

  • Hello all,

    thank you for your comments and valuable advises , unfortunately i wasn't the one who build this structure ,i am getting those data in row format exported to my ftp and i had to create tables with the same data structure provided to start playing with it and get my required data.

    so my case was to get those data in my required format.

    Regards

    Rashed

  • Maybe this is an over-simplification, but you could test it versus the other methods provided.

    SELECT

    a.[Pax ID], a.[Flight Date], a.[Flight Number],

    ISNULL(b.[Last Mod Date], a.[Last Mod Date]) AS [Last Mod Date],

    ISNULL(b.[Status], a.[Status]) AS [Status]

    FROM pnradds a left outer join pnrupdates b

    on a.[pax id] = b.[pax id]

    AND a.[flight date] = b.[flight date]

    AND a.[flight number] = b.[flight number]

    WHERE b.[last mod date] =

    (SELECT MAX([last mod date])

    FROM pnrupdates c

    WHERE b.[pax id] = c.[pax id]

    AND b.[flight date] = c.[flight date]

    AND b.[flight number] = c.[flight number])

    OR b.[pax id] IS NULL

    Greg
    _________________________________________________________________________________________________
    The glass is at one half capacity: nothing more, nothing less.

  • CELKO (2/24/2015)


    You should follow ISO-11179 rules for naming data elements. You failed!

    But even worse than all of this, is that you have two tables with the same structure.

    Oh? You're sure the OP actually wrote the sproc and designed the DB? That he's not some guy coming in after the fact, just trying to do his best?

    Your posting is full of fundamental errors. Not little errors, but fundamental errors. I spent decades of my life developing SQL standards, so when I tell you to stop programming you know I have some authority.

    I only respect authority that respects other people. You fail that test. All the SQL knowledge in the world can't replace basic social skills.

    WOW! Do you know how few ISO standards ate the long??

    Come on, CELKO!! Can't you even type correctly?? Stop typing and go home until you know how to do it perfectly! ๐Ÿ˜›

    Can you take a year off to get a proper education before you program again?

    This is just you being mean for no reason. If you can't say your piece nicely, please don't bother to post. We'll all feel better.

    PS: Your contribution about one possible way to keep a transaction history was actually clear and helpful. For a change.

    Sigerson

    "No pressure, no diamonds." - Thomas Carlyle

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply