SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Complex Query


Complex Query

Author
Message
Christian Graus
Christian Graus
Old Hand
Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)

Group: General Forum Members
Points: 353 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.
Christian Graus
Christian Graus
Old Hand
Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)

Group: General Forum Members
Points: 353 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.
alhakimi
alhakimi
SSC-Enthusiastic
SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)

Group: General Forum Members
Points: 177 Visits: 108
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.
Christian Graus
Christian Graus
Old Hand
Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)

Group: General Forum Members
Points: 353 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.
alhakimi
alhakimi
SSC-Enthusiastic
SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)

Group: General Forum Members
Points: 177 Visits: 108
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..
Christian Graus
Christian Graus
Old Hand
Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)

Group: General Forum Members
Points: 353 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.
alhakimi
alhakimi
SSC-Enthusiastic
SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)

Group: General Forum Members
Points: 177 Visits: 108
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
Christian Graus
Christian Graus
Old Hand
Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)

Group: General Forum Members
Points: 353 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 ?
alhakimi
alhakimi
SSC-Enthusiastic
SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)

Group: General Forum Members
Points: 177 Visits: 108
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
Christian Graus
Christian Graus
Old Hand
Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)

Group: General Forum Members
Points: 353 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search