Help in query

Question

Post reply

Help in query

alhakimi

Say Hey Kid

Points: 687
More actions
February 18, 2015 at 12:29 am

#314686

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

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

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

Eirikur Eiriksson SSC Guru Points: 182879 More actions · Answer 1

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

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 2

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

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

Eirikur Eiriksson SSC Guru Points: 182879 More actions · Answer 4

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

😎

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 5

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]

kapil_kk SSC-Insane Points: 21316 More actions · Answer 6

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/

alhakimi Say Hey Kid Points: 687 More actions · Answer 7

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

Eirikur Eiriksson SSC Guru Points: 182879 More actions · Answer 8

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

alhakimi Say Hey Kid Points: 687 More actions · Answer 9

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

Eirikur Eiriksson SSC Guru Points: 182879 More actions · Answer 10

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.

alhakimi Say Hey Kid Points: 687 More actions · Answer 11

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

Greg Snidow SSCoach Points: 16106 More actions · Answer 12

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.

Sigerson Hall of Fame Points: 3697 More actions · Answer 13

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