December 30, 2013 at 1:13 am
No worries. Obviously, when your client is chasing you, it's a problem to you, and if you've not been given enough info to do your job, and if you're also struggling with the task, then I can understand why you'd be frustrated.
January 3, 2014 at 12:29 pm
Dear Christian , i have edited my query and the data i posted earlier hope the query more clear now please let me know if still you have any query
January 3, 2014 at 1:35 pm
You modified the original post ? Reading it, you're asking for an 'arrival date' column. But I don't see any data you can use to work that out, that's precisely the point I made to you in the first place. Can you just post SQL to create the table, insert data, and an example of the output you want from the query ?
January 4, 2014 at 10:43 am
1- create query
CREATE TABLE table1(
[Pax ID] [int] NULL,
[Reservation] [int] NULL,
[Surname] [varchar](40) NULL,
[First Name] [varchar](40) NULL,
[Pax Type] [char](1) NULL,
[Flight Date] [smalldatetime] NULL,
[Flight Number] [varchar](10) NULL,
[Board] [varchar](3) NULL,
[Off] [varchar](3) NULL,
[Original Booking Date] [smalldatetime] NULL,
[Last Mod Date] [smalldatetime] NULL
)
2- insert query
INSERT INTO table1([Pax ID],[Reservation],[Surname],[First Name],[Flight Date],[Flight Number],[Board],[Off],[Original Booking Date],[Last Mod Date])
SELECT '1558611','899842','SULIMAN','ALI','2/1/2013','FO151','RIY','SAH','1/1/2013','1/1/2013' UNION ALL
SELECT '1558612','899842','ALGANADI','HASAN','2/1/2013','FO151','RIY','SAH','1/1/2013','1/1/2013' UNION ALL
SELECT '1558613','899844','ALYAFEE','MOHMMED','1/1/2013','FO160','SAH','TAI','1/1/2013','1/1/2013' UNION ALL
SELECT '1558616','899847','ASSIRI','ahmed','4/1/2013','FO873','AHB','ADE','1/1/2013','1/1/2013' UNION ALL
SELECT '1558616','899847','ASSIRI','ahmed','4/2/2013','FO874','ADE','JED','1/1/2013','1/1/2013' UNION ALL
SELECT '1558628','899847','asiri','alin','4/1/2013','FO873','AHB','ADE','1/1/2013','1/1/2013' UNION ALL
SELECT '1558628','899847','asiri','alin','4/2/2013','FO874','ADE','JED','1/1/2013','1/1/2013' UNION ALL
SELECT '1558616','899847','ASSIRI','ahmed','6/10/2013','FO875','JED','ADE','1/1/2013','1/1/2013' UNION ALL
SELECT '1558616','899847','ASSIRI','ahmed','6/11/2013','FO876','ADE','AHB','1/1/2013','1/1/2013' UNION ALL
SELECT '1558628','899847','asiri','alin','6/10/2013','FO875','JED','ADE','1/1/2013','1/1/2013' UNION ALL
SELECT '1558628','899847','asiri','alin','6/11/2013','FO876','ADE','AHB','1/1/2013','1/1/2013' UNION ALL
SELECT '1558618','899848','ALAQWAA','EBRAHEEM','1/1/2013','FO173','HOD','SAH','1/1/2013','1/1/2013' UNION ALL
SELECT '1558621','899850','ALGELHM','HAMID','3/1/2013','FO196','SAH','AAY','1/1/2013','1/1/2013' UNION ALL
SELECT '1558622','899861','ASGHAR','AMER','1/1/2013','FO205','ADE','SAH','1/1/2013','1/1/2013' UNION ALL
SELECT '1558623','899852','ALHALILI','HAMZAH','2/1/2013','FO174','SAH','HOD','1/1/2013','1/1/2013' UNION ALL
SELECT '1558624','899852','ALJAHDARI','GHALIAH','2/1/2013','FO174','SAH','HOD','1/1/2013','1/1/2013' UNION ALL
SELECT '1558625','899853','ABDULLAH','ADEL','1/1/2013','FO173','HOD','SAH','1/1/2013','1/1/2013' UNION ALL
SELECT '1558626','899854','alasmari','mohammed','2/1/2013','FO873','AHB','ADE','1/1/2013','1/1/2013' UNION ALL
SELECT '1558626','899854','alasmari','mohammed','4/1/2013','FO874','ADE','AHB','1/1/2013','1/1/2013' UNION ALL
SELECT '1558631','899856','ALI','FAWAZ','2/2/2013','FO196','SAH','AAY','1/1/2013','1/1/2013' UNION ALL
SELECT '1558631','899856','ALI','FAWAZ','2/2/2013','FO197','AAY','TAI','1/1/2013','1/1/2013' UNION ALL
SELECT '1558631','899856','ALI','FAWAZ','2/3/2013','FO198','TAI','CAI','1/1/2013','1/1/2013' UNION ALL
SELECT '1558634','899851','KAMAL','YASSER','1/4/2013','FO120','TAI','ADE','1/3/2013','1/3/2013' UNION ALL
SELECT '1558634','899851','KAMAL','YASSER','4/5/2013','FO121','ADE','TAI','1/3/2013','1/3/2013'
3- The output i want:
Pax ID|Reservation|First name|Surname|pax in same res#|# of pax|Diff flight date purchase date|TransitCity|Flight date|FLightNo|DepartCity|ArrivalCity|Last Mod Date|Purchase Date|Arrival Date
1558611|899842|ALI|SULIMAN|HASAN|2|31|NULL|2/1/2013 0:00|FO151|RIY|SAH|1/1/2013 0:00|1/1/2013 0:00|2/1/2013 0:00
1558612|899842|HASAN|ALGANADI|ALI|2|31|NULL|2/1/2013 0:00|FO151|RIY|SAH|1/1/2013 0:00|1/1/2013 0:00|2/1/2013 0:00
1558613|899844|MOHMMED|ALYAFEE|NULL|1|0|NULL|1/1/2013 0:00|FO160|SAH|TAI|1/1/2013 0:00|1/1/2013 0:00|1/1/2013 0:00
1558616|899847|ahmed|ASSIRI|Alin Asiri|2|90|ADE|4/1/2013 0:00|FO873,FO874|AHB|JED|1/1/2013 0:00|1/1/2013 0:00|4/2/2013 0:00
1558628|899847|alin|asiri|ahmed Assiri|2|90|ADE|4/1/2013 0:00|FO873,FO874|AHB|JED|1/1/2013 0:00|1/1/2013 0:00|4/2/2013 0:00
1558616|899847|ahmed|ASSIRI|Alin Asiri|2|160|ADE|6/10/2013 0:00|FO875,FO876|JED|AHB|1/1/2013 0:00|1/1/2013 0:00|6/11/2013 0:00
1558628|899847|alin|asiri|ahmed Assiri|2|160|ADE|6/10/2013 0:00|FO875,FO876|JED|AHB|1/1/2013 0:00|1/1/2013 0:00|6/11/2013 0:00
1558618|899848|EBRAHEEM|ALAQWAA|NULL|1|0|NULL|1/1/2013 0:00|FO173|HOD|SAH|1/1/2013 0:00|1/1/2013 0:00|1/1/2013 0:00
1558621|899850|HAMID|ALGELHM|NULL|1|0|NULL|1/1/2013 0:00|FO196|SAH|AAY|1/1/2013 0:00|1/1/2013 0:00|1/1/2013 0:00
1558622|899861|AMER|ASGHAR|Null|1|0|NULL|1/1/2013 0:00|FO205|ADE|SAH|1/1/2013 0:00|1/1/2013 0:00|1/1/2013 0:00
1558634|899851|YASSER|KAMAL|NULL|1|92|null|4/5/2013 0:00|FO120|TAI|ADE|1/3/2013 0:00|1/3/2013 0:00|4/5/2013 0:00
1558634|899851|YASSER|KAMAL|NULL|1|92|null|4/5/2013 0:00|FO120|ADE|TAI|1/3/2013 0:00|1/3/2013 0:00|4/5/2013 0:00
1558631|899856|FAWAZ|ALI|NULL|1|32|AAY,TAI|2/2/2013 0:00|FO196,FO197,FO198|SAH|CAI|1/1/2013 0:00|1/1/2013 0:00|2/3/2013 0:00
1558623|899852|HAMZAH|ALHALILI|GHALIAH|2|31|NULL|2/1/2013 0:00|FO174|SAH|HOD|1/1/2013 0:00|1/1/2013 0:00|2/1/2013 0:00
1558624|899852|GHALIAH|ALJAHDARI|HAMZAH|2|31|NULL|2/1/2013 0:00|FO174|SAH|HOD|1/1/2013 0:00|1/1/2013 0:00|2/1/2013 0:00
1558625|899853|ADEL|ABDULLAH|NULL|1|0|NULL|1/1/2013 0:00|FO173|HOD|SAH|1/1/2013 0:00|1/1/2013 0:00|1/1/2013 0:00
1558626|899854|mohammed|alasmari|NULL|1|90|NULL|4/1/2013 0:00|FO873|AHB|ADE|1/1/2013 0:00|1/1/2013 0:00|4/1/2013 0:00
1558626|899854|mohammed|alasmari|NULL|1|120|NULL|5/1/2013 0:00|FO874|ADE|AHB|1/1/2013 0:00|1/1/2013 0:00|5/1/2013 0:00
3- What Mr. Lowell provided is :
SELECT [Pax ID],[Reservation],[First name],[Surname],
STUFF((SELECT ',' + [First name] FROM [table1] WHERE [Reservation] = t.[Reservation] AND [First name] <> t.[First name] FOR XML PATH('')),1,1,'') AS [pax in same res#],
COUNT(1) OVER (PARTITION BY [Reservation]) AS [# of pax],
DATEDIFF(dd,[Purchase date],[Flight date]) AS [Diff flight date purchase date],
STUFF((SELECT DISTINCT ',' + t1.[Off]
FROM table1 t1
JOIN table1 t2
ON t1.[Pax ID] = t2.[Pax ID]
AND t1.[Reservation] = t2.[Reservation]
AND t1.[Off] = t2.[Board]
WHERE t1.[Pax ID] = t.[Pax ID]
AND t1.[Reservation] = t.[Reservation]
FOR XML PATH('')
),1,1,'') AS TransitCity,
[Flight date],
STUFF((SELECT ',' + [Flight Number]
FROM table1
WHERE [Pax ID] = t.[Pax ID]
AND [Reservation] = t.[Reservation]
FOR XML PATH('')
),1,1,'') AS FLightNo,
(SELECT TOP 1 [Board]
FROM table1
WHERE [Pax ID] = t.[Pax ID]
AND [Reservation] = t.[Reservation]
ORDER BY [Flight date] ASC) AS DepartCity,
(SELECT TOP 1 [Off]
FROM table1
WHERE [Pax ID] = t.[Pax ID]
AND [Reservation] = t.[Reservation]
ORDER BY [Flight date] DESC) AS ArrivalCity,
[Last Mod Date],
[Purchase Date]
FROM (SELECT [Pax ID],[Reservation],[First Name],[Surname],MAX([Flight Date]) AS [Flight date],MAX([Original Booking Date]) AS [Purchase Date],MAX([Last Mod Date] ) AS [Last Mod Date]
FROM [table1]
GROUP BY [Pax ID],[Reservation],[First Name] ,[Surname]) t
in regards to the arrival date , sorry i missed to mention it in my previous post ..
i know its not possible to get it as there is no flight time, so it will be the last row flight date of each passenger flight .. as mentioned in the needed output above..
for more clarification please let me know.
thank you in advance
Alhakimi
January 4, 2014 at 2:50 pm
I've spent an hour playing with this. This would work, if there was a maximum of one connection:
select t1.[pax id], t1.board, case when t2.[off] is null then '' else t1.[off] end as connecting, coalesce(t2.[off], t1.[off]) as [off], coalesce(t2.[flight date], t1.[flight date]) as [fake arrival date], abs(datediff(dd, t1.[flight date], t2.[flight date]))
from
table1 t1
left join table1 t2 on t1.[off] = t2.board and t1.[pax id] = t2.[pax id]
As there's many, you have several issues. The main one is simply that this cannot work, all the time. The database is fundamentally broken, and needs to be fixed for a workable solution to exist. The SQL you were given has a number of it's own issues, not least that it will ignore fellow passengers who have the same first name, which is far from impossible.
You can probably do this, by writing a cursor based solution. That's always slow and hideous, but the fact is, you need a ton of procedural code to try to work out what you're trying to work out, based on the non existent relevant data that you have. I asked you for the logic by which you hope to work out what constitutes a single trip, and you've not replied. I cannot think of anything, based on this data, that would reliably work, all the time. What if I am doing a round the world trip, and staying in each destination for a day ? There won't be any logical way to work out, without arrival times, how long I am staying, and if I am connecting. With arrival times, and I mean times, not just dates, for arrival and departure, then you could say 'anything greater than 8 hours is an end of trip', or 'any place I stay overnight, is the end of a trip'. That you need to fudge this basic information is another clue that this database design is utterly broken.
You need arrival dates as a minimum. That an airline would store departure dates without times, is unbelievable to me. The database needs to be normalised. There should be a table that stores a single trip, with connecting tables that store each step of the trip, and that store each passenger that's taking the trip.
If I was working for someone who gave me this, I'd quote on rewriting the entire system or tell them to find someone else. Life is too short to spend fixing other people's idiocy.
January 4, 2014 at 6:40 pm
alhakimi (1/4/2014)
1- create query
CREATE TABLE table1(
[Pax ID] [int] NULL,
[Reservation] [int] NULL,
[Surname] [varchar](40) NULL,
[First Name] [varchar](40) NULL,
[Pax Type] [char](1) NULL,
[Flight Date] [smalldatetime] NULL,
[Flight Number] [varchar](10) NULL,
[Board] [varchar](3) NULL,
[Off] [varchar](3) NULL,
[Original Booking Date] [smalldatetime] NULL,
[Last Mod Date] [smalldatetime] NULL
)
2- insert query
INSERT INTO table1([Pax ID],[Reservation],[Surname],[First Name],[Flight Date],[Flight Number],[Board],[Off],[Original Booking Date],[Last Mod Date])
SELECT '1558611','899842','SULIMAN','ALI','2/1/2013','FO151','RIY','SAH','1/1/2013','1/1/2013' UNION ALL
SELECT '1558612','899842','ALGANADI','HASAN','2/1/2013','FO151','RIY','SAH','1/1/2013','1/1/2013' UNION ALL
SELECT '1558613','899844','ALYAFEE','MOHMMED','1/1/2013','FO160','SAH','TAI','1/1/2013','1/1/2013' UNION ALL
SELECT '1558616','899847','ASSIRI','ahmed','4/1/2013','FO873','AHB','ADE','1/1/2013','1/1/2013' UNION ALL
SELECT '1558616','899847','ASSIRI','ahmed','4/2/2013','FO874','ADE','JED','1/1/2013','1/1/2013' UNION ALL
SELECT '1558628','899847','asiri','alin','4/1/2013','FO873','AHB','ADE','1/1/2013','1/1/2013' UNION ALL
SELECT '1558628','899847','asiri','alin','4/2/2013','FO874','ADE','JED','1/1/2013','1/1/2013' UNION ALL
SELECT '1558616','899847','ASSIRI','ahmed','6/10/2013','FO875','JED','ADE','1/1/2013','1/1/2013' UNION ALL
SELECT '1558616','899847','ASSIRI','ahmed','6/11/2013','FO876','ADE','AHB','1/1/2013','1/1/2013' UNION ALL
SELECT '1558628','899847','asiri','alin','6/10/2013','FO875','JED','ADE','1/1/2013','1/1/2013' UNION ALL
SELECT '1558628','899847','asiri','alin','6/11/2013','FO876','ADE','AHB','1/1/2013','1/1/2013' UNION ALL
SELECT '1558618','899848','ALAQWAA','EBRAHEEM','1/1/2013','FO173','HOD','SAH','1/1/2013','1/1/2013' UNION ALL
SELECT '1558621','899850','ALGELHM','HAMID','3/1/2013','FO196','SAH','AAY','1/1/2013','1/1/2013' UNION ALL
SELECT '1558622','899861','ASGHAR','AMER','1/1/2013','FO205','ADE','SAH','1/1/2013','1/1/2013' UNION ALL
SELECT '1558623','899852','ALHALILI','HAMZAH','2/1/2013','FO174','SAH','HOD','1/1/2013','1/1/2013' UNION ALL
SELECT '1558624','899852','ALJAHDARI','GHALIAH','2/1/2013','FO174','SAH','HOD','1/1/2013','1/1/2013' UNION ALL
SELECT '1558625','899853','ABDULLAH','ADEL','1/1/2013','FO173','HOD','SAH','1/1/2013','1/1/2013' UNION ALL
SELECT '1558626','899854','alasmari','mohammed','2/1/2013','FO873','AHB','ADE','1/1/2013','1/1/2013' UNION ALL
SELECT '1558626','899854','alasmari','mohammed','4/1/2013','FO874','ADE','AHB','1/1/2013','1/1/2013' UNION ALL
SELECT '1558631','899856','ALI','FAWAZ','2/2/2013','FO196','SAH','AAY','1/1/2013','1/1/2013' UNION ALL
SELECT '1558631','899856','ALI','FAWAZ','2/2/2013','FO197','AAY','TAI','1/1/2013','1/1/2013' UNION ALL
SELECT '1558631','899856','ALI','FAWAZ','2/3/2013','FO198','TAI','CAI','1/1/2013','1/1/2013' UNION ALL
SELECT '1558634','899851','KAMAL','YASSER','1/4/2013','FO120','TAI','ADE','1/3/2013','1/3/2013' UNION ALL
SELECT '1558634','899851','KAMAL','YASSER','4/5/2013','FO121','ADE','TAI','1/3/2013','1/3/2013'
3- The output i want:
Pax ID|Reservation|First name|Surname|pax in same res#|# of pax|Diff flight date purchase date|TransitCity|Flight date|FLightNo|DepartCity|ArrivalCity|Last Mod Date|Purchase Date|Arrival Date
1558611|899842|ALI|SULIMAN|HASAN|2|31|NULL|2/1/2013 0:00|FO151|RIY|SAH|1/1/2013 0:00|1/1/2013 0:00|2/1/2013 0:00
1558612|899842|HASAN|ALGANADI|ALI|2|31|NULL|2/1/2013 0:00|FO151|RIY|SAH|1/1/2013 0:00|1/1/2013 0:00|2/1/2013 0:00
1558613|899844|MOHMMED|ALYAFEE|NULL|1|0|NULL|1/1/2013 0:00|FO160|SAH|TAI|1/1/2013 0:00|1/1/2013 0:00|1/1/2013 0:00
1558616|899847|ahmed|ASSIRI|Alin Asiri|2|90|ADE|4/1/2013 0:00|FO873,FO874|AHB|JED|1/1/2013 0:00|1/1/2013 0:00|4/2/2013 0:00
1558628|899847|alin|asiri|ahmed Assiri|2|90|ADE|4/1/2013 0:00|FO873,FO874|AHB|JED|1/1/2013 0:00|1/1/2013 0:00|4/2/2013 0:00
1558616|899847|ahmed|ASSIRI|Alin Asiri|2|160|ADE|6/10/2013 0:00|FO875,FO876|JED|AHB|1/1/2013 0:00|1/1/2013 0:00|6/11/2013 0:00
1558628|899847|alin|asiri|ahmed Assiri|2|160|ADE|6/10/2013 0:00|FO875,FO876|JED|AHB|1/1/2013 0:00|1/1/2013 0:00|6/11/2013 0:00
1558618|899848|EBRAHEEM|ALAQWAA|NULL|1|0|NULL|1/1/2013 0:00|FO173|HOD|SAH|1/1/2013 0:00|1/1/2013 0:00|1/1/2013 0:00
1558621|899850|HAMID|ALGELHM|NULL|1|0|NULL|1/1/2013 0:00|FO196|SAH|AAY|1/1/2013 0:00|1/1/2013 0:00|1/1/2013 0:00
1558622|899861|AMER|ASGHAR|Null|1|0|NULL|1/1/2013 0:00|FO205|ADE|SAH|1/1/2013 0:00|1/1/2013 0:00|1/1/2013 0:00
1558634|899851|YASSER|KAMAL|NULL|1|92|null|4/5/2013 0:00|FO120|TAI|ADE|1/3/2013 0:00|1/3/2013 0:00|4/5/2013 0:00
1558634|899851|YASSER|KAMAL|NULL|1|92|null|4/5/2013 0:00|FO120|ADE|TAI|1/3/2013 0:00|1/3/2013 0:00|4/5/2013 0:00
1558631|899856|FAWAZ|ALI|NULL|1|32|AAY,TAI|2/2/2013 0:00|FO196,FO197,FO198|SAH|CAI|1/1/2013 0:00|1/1/2013 0:00|2/3/2013 0:00
1558623|899852|HAMZAH|ALHALILI|GHALIAH|2|31|NULL|2/1/2013 0:00|FO174|SAH|HOD|1/1/2013 0:00|1/1/2013 0:00|2/1/2013 0:00
1558624|899852|GHALIAH|ALJAHDARI|HAMZAH|2|31|NULL|2/1/2013 0:00|FO174|SAH|HOD|1/1/2013 0:00|1/1/2013 0:00|2/1/2013 0:00
1558625|899853|ADEL|ABDULLAH|NULL|1|0|NULL|1/1/2013 0:00|FO173|HOD|SAH|1/1/2013 0:00|1/1/2013 0:00|1/1/2013 0:00
1558626|899854|mohammed|alasmari|NULL|1|90|NULL|4/1/2013 0:00|FO873|AHB|ADE|1/1/2013 0:00|1/1/2013 0:00|4/1/2013 0:00
1558626|899854|mohammed|alasmari|NULL|1|120|NULL|5/1/2013 0:00|FO874|ADE|AHB|1/1/2013 0:00|1/1/2013 0:00|5/1/2013 0:00
3- What Mr. Lowell provided is :
SELECT [Pax ID],[Reservation],[First name],[Surname],
STUFF((SELECT ',' + [First name] FROM [table1] WHERE [Reservation] = t.[Reservation] AND [First name] <> t.[First name] FOR XML PATH('')),1,1,'') AS [pax in same res#],
COUNT(1) OVER (PARTITION BY [Reservation]) AS [# of pax],
DATEDIFF(dd,[Purchase date],[Flight date]) AS [Diff flight date purchase date],
STUFF((SELECT DISTINCT ',' + t1.[Off]
FROM table1 t1
JOIN table1 t2
ON t1.[Pax ID] = t2.[Pax ID]
AND t1.[Reservation] = t2.[Reservation]
AND t1.[Off] = t2.[Board]
WHERE t1.[Pax ID] = t.[Pax ID]
AND t1.[Reservation] = t.[Reservation]
FOR XML PATH('')
),1,1,'') AS TransitCity,
[Flight date],
STUFF((SELECT ',' + [Flight Number]
FROM table1
WHERE [Pax ID] = t.[Pax ID]
AND [Reservation] = t.[Reservation]
FOR XML PATH('')
),1,1,'') AS FLightNo,
(SELECT TOP 1 [Board]
FROM table1
WHERE [Pax ID] = t.[Pax ID]
AND [Reservation] = t.[Reservation]
ORDER BY [Flight date] ASC) AS DepartCity,
(SELECT TOP 1 [Off]
FROM table1
WHERE [Pax ID] = t.[Pax ID]
AND [Reservation] = t.[Reservation]
ORDER BY [Flight date] DESC) AS ArrivalCity,
[Last Mod Date],
[Purchase Date]
FROM (SELECT [Pax ID],[Reservation],[First Name],[Surname],MAX([Flight Date]) AS [Flight date],MAX([Original Booking Date]) AS [Purchase Date],MAX([Last Mod Date] ) AS [Last Mod Date]
FROM [table1]
GROUP BY [Pax ID],[Reservation],[First Name] ,[Surname]) t
in regards to the arrival date , sorry i missed to mention it in my previous post ..
i know its not possible to get it as there is no flight time, so it will be the last row flight date of each passenger flight .. as mentioned in the needed output above..
for more clarification please let me know.
thank you in advance
Alhakimi
I believe that all of this would be a lot easier if you exposed the Flight table, which should contain the departure and arrival times of each and every flight. Can you post that in a readily consumable form for the given dates and flights?
--Jeff Moden
Change is inevitable... Change for the better is not.
January 4, 2014 at 6:43 pm
If there is a flight table, then this database is significantly not normalized, but he's told me in the past that they don't store the arrival dates at all, just like they don't store the departure times.
I really think that the DB needs fixing, or if that can't be done, nothing will be efficient, and a cursor based solution might be best in terms of maintainability. Certainly I think whatever solution we give, he won't be able to understand sufficiently to be able to change it, so there's only more trouble down the track, if the core issues are not fixed.
January 4, 2014 at 8:15 pm
It's difficult for me to believe that there's no flight table. I don't believe it would be possible to make the given test data without it.
That, not withstanding, the data looks to be a "simple" Adjacency List where each reservation is a small tree within a forest. I haven't tried it yet because I don't really know what's expected (don't have the time to go back through the whole post, unfortunately) but it seems like using a recursive CTE (wouldn't be RBAR, in this case, because it would process entire levels all at once) could be used to build the hierarchical path for each reservation which would be, essential, the list of flights in order.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 4, 2014 at 8:33 pm
I agree. The hard part is working out where the tree stops, and where the return journey begins. That's really what I was trying to do with it, to define a gap where the same flight number means a new flight. But given time zones and different flight lengths, I don't think that can be reliably done, and the next best thing, would be business rules, so that when it doesn't work for every case, the OP has covered themselves.
January 4, 2014 at 9:02 pm
Hi friends thanks for your input and comments and imagine thats the case.. actually the host system who provides the service gives this limited data export to the client and pulling his leg to pay huge amount for rest of information .. it doesnt mean that they are not storing it at thier end but they are not willing to provide it to the client eeasily and thats the reason my client s looking for an alternative system to switch. But there was the need of having that report and was wishing to get at least better resault out of it ..
Thanks
January 4, 2014 at 9:06 pm
If the arrival date is an issue , I can tell my client to escape it but at least the transit cities should be correct .. the new query gives an extra duplicare rows..
January 4, 2014 at 9:13 pm
alhakimi (1/4/2014)
Hi friends thanks for your input and comments and imagine thats the case.. actually the host system who provides the service gives this limited data export to the client and pulling his leg to pay huge amount for rest of information .. it doesnt mean that they are not storing it at thier end but they are not willing to provide it to the client eeasily and thats the reason my client s looking for an alternative system to switch. But there was the need of having that report and was wishing to get at least better resault out of it ..Thanks
Ahhhhh.... I knew there had to be something else to it all. It would not have been possible to actually have a flight system with the data given. I get it now.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 4, 2014 at 9:15 pm
OK, here is a recursive solution that gives a list of flights, in order, with a sequence of numbers next to them. It's assumed any stopover greater than a day, is the end of a journey. The sequence thus restarts for the return trip.
with flights as
(
select 1 as depth, t.[pax id], t.reservation, t.surname, t.[first name], t.[pax type], t.[flight date], t.[flight number], t.board, t.[off], t.[original booking date], t.[last mod date] from table1 t
left join table1 f on f.[off] = t.[board] and f.[pax id] = t.[pax id] and f.reservation = t.reservation and f.surname = t.surname and f.[first name] = t.[first name] and abs(datediff(dd, f.[flight date], t.[flight date])) < 2
where f.[off] is null
union all
select f.depth + 1 as depth, t.[pax id], t.reservation, t.surname, t.[first name], t.[pax type], t.[flight date], t.[flight number], t.board, t.[off], t.[original booking date], t.[last mod date] from table1 t
inner join flights f on f.[off] = t.[board] and f.[pax id] = t.[pax id] and f.reservation = t.reservation and f.surname = t.surname and f.[first name] = t.[first name]
where abs(datediff(dd, f.[flight date], t.[flight date])) < 2
)
select * from flights order by [pax id], [flight date], depth
I know this does not solve the overall problem, but I think it's a step in the right direction.
And here is SQL to get a list of all the destinations in a journey. Hopefully between these two you can start to see a full solution...
with flights as
(
select 1 as depth, t.[pax id], t.reservation, t.surname, t.[first name], t.[pax type], t.[flight date], t.[flight number], t.board, t.[off], t.[original booking date], t.[last mod date] from table1 t
left join table1 f on f.[off] = t.[board] and f.[pax id] = t.[pax id] and f.reservation = t.reservation and f.surname = t.surname and f.[first name] = t.[first name] and abs(datediff(dd, f.[flight date], t.[flight date])) < 2
where f.[off] is null
union all
select f.depth + 1 as depth, t.[pax id], t.reservation, t.surname, t.[first name], t.[pax type], t.[flight date], t.[flight number], t.board, t.[off], t.[original booking date], t.[last mod date] from table1 t
inner join flights f on f.[off] = t.[board] and f.[pax id] = t.[pax id] and f.reservation = t.reservation and f.surname = t.surname and f.[first name] = t.[first name]
where abs(datediff(dd, f.[flight date], t.[flight date])) < 2
)
SELECT
t1.[pax id], t1.depth, t1.[flight date],
DestList = substring((SELECT ( ', ' + [off] )
FROM flights t2
WHERE t1.[pax id] = t2.[pax id] and (t1.depth + 1 = t2.depth or t1.depth = 1) and abs(datediff(dd, t1.[flight date], t2.[flight date])) < 2
FOR XML PATH( '' )
), 3, 1000 )FROM flights t1
GROUP BY t1.[pax id], t1.depth, t1.[flight date]
January 4, 2014 at 9:25 pm
Christian Graus (1/4/2014)
I agree. The hard part is working out where the tree stops, and where the return journey begins. That's really what I was trying to do with it, to define a gap where the same flight number means a new flight. But given time zones and different flight lengths, I don't think that can be reliably done, and the next best thing, would be business rules, so that when it doesn't work for every case, the OP has covered themselves.
You've said a mouthful there. The other problem might be "city gaps", which would really throw a wrench into the works, where someone might fly to one destination, take a train to another, fly to another using a couple of layovers, take a train, and then fly back to the point of origin all as part of the same reservation. Without something to identify the sequence of departures, this is a tough nut to crack.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 4, 2014 at 9:45 pm
Exactly - that's why the OP needs to get business rules from the paying client, because this system will NEVER be reliable, and the most he can hope for, is to be able to say that it implements the exact rules the client asked for, and the client deserves to have been told that this is going to be a fragile solution, because they probably do not know.
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply