Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Complex Query Expand / Collapse
Author
Message
Posted Monday, December 30, 2013 12:30 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 8, 2014 6:43 PM
Points: 57, Visits: 97
I am somewhat troubled. You're being paid to work for an airline, but you're really lost as to how to do the job you're being paid to do ?

Everything you're saying seems to me to point towards the database not really being suited to the task at hand. This questions has become a confusing set of edge cases. I'd suggest you need to either fix the database design, or if it can't be changed, work out how to differentiate what you want. It looks to me like a starting point might be to group by departure date, in order to not end up grouping the return flights with the flights going out. Sometimes people do have a connection the next day, but your data base does not have arrival times, or even departure times, these are ways in which it is fundamentally broken.

Post #1526400
Posted Monday, December 30, 2013 12:32 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 8, 2014 6:43 PM
Points: 57, Visits: 97
Just to add, I don't think anyone is going to give you the SQL to solve this, because the issues are deeper. If you need help, we'll need to work through it one thing at a time. First things first. Do you have ANY room to improve this database format ? If you had arrival times and departure times, you could work out what's a group of connected flights by how close they are together. As it is, you could try, but with some commercial flights taking 15 hours and landing before they left, or missing a day, that is hard.
Post #1526401
Posted Monday, December 30, 2013 12:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 30, 2014 3:59 PM
Points: 17, Visits: 73
Its true that the database is not designed well, i don't want to go in details why its not designed well but this is the only fields and data we are getting from reservation host that limits the option of having the data easily filtered.
I wasn't the one who designed the database structure to be blamed.. i am only using the data i have.
Post #1526403
Posted Monday, December 30, 2013 12:51 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 8, 2014 6:43 PM
Points: 57, Visits: 97
Sure - I understand how that can happen. But, what do you propose should be the business rule to work out what is a connecting flight and what is not ? Because that is the first step, not for you to pick out instances and say 'this is two flights', but for you to define business rules by which this can be satisfactorily worked out. Once you have business rules ( which probably the client should define ), then you can work on SQL. Until then, until the client has signed off on what you're going to do, you're just asking for code that works today and not tomorrow. With this DB, I think that can't be avoided, which is all the more reason the client needs to sign off on the rules you are implementing.

If you know the rules, by all means, post them, and we can work on defining them in SQL.
Post #1526409
Posted Monday, December 30, 2013 1:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 30, 2014 3:59 PM
Points: 17, Visits: 73
Thanks , let me do some home work and try to find out how i can make the business rule with the client. and let you know shortly. thank you for your efforts and appologize for being so troubled..


Post #1526413
Posted Monday, December 30, 2013 1:13 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 8, 2014 6:43 PM
Points: 57, Visits: 97
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.
Post #1526415
Posted Friday, January 3, 2014 12:29 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 30, 2014 3:59 PM
Points: 17, Visits: 73
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
Post #1527692
Posted Friday, January 3, 2014 1:35 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 8, 2014 6:43 PM
Points: 57, Visits: 97
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 ?
Post #1527710
Posted Saturday, January 4, 2014 10:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 30, 2014 3:59 PM
Points: 17, Visits: 73
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
Post #1527831
Posted Saturday, January 4, 2014 2:50 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 8, 2014 6:43 PM
Points: 57, Visits: 97
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.
Post #1527866
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse