September 15, 2003 at 12:24 pm
can some help me in tunning following t-sql statement?
create view test
as
set isolation level read READ UNCOMMITTED
SELECT A4.RLOC, A4.CreateDate, A4.Segment, A4.AirRloc, A4.Carrier, A4.FlightNum, A4.Class, A4.Org,
A4.Dest, A4.Miles, A4.[Action], A4.NumSeats, A4.Seats, A4.DeptDateTime, A4.ArriveDateTime,
A4.Duration, A4.StopCity1, A4.StopCity2, A4.DepTerminal, A4.ArrTerminal, A4.Equipment, A4.Meal,
A4.Entertainment, A4.Codeshare, T1.[NAME], T1.[Remarks]
FROM
( SELECT DISTINCT A3.RLOC, A3.CreateDate FROM TEMP_AIRITIN A3
INNER JOIN
( SELECT A2.DeptDateTime,A2.Carrier,A2.FlightNum, count(T.Name) as TRNumber FROM
TEMP_AIRITIN A2
INNER JOIN
( select DISTINCT A.RLOC, A.CreateDate from TEMP_AIRITIN A
INNER JOIN TEMP_MASTER M
ON A.RLOC = M.RLOC AND A.CreateDate = M.CreateDate
WHERE
(M.BookingPCC = 'ZZZ') AND (A.DeptDateTime Between '9/10/2003' and '12/11/2010')
and (A.Segment = (Select MAX(Segment) From TEMP_AIRITIN A1
WHERE (A.RLOC = A1.RLOC) and (A.CreateDate = A1.CreateDate))
)
) SP
ON A2.RLOC = SP.RLOC AND A2.CreateDate = SP.CreateDate
INNER JOIN
TEMP_TRAVELER T
ON SP.RLOC = T.RLOC AND SP.CreateDate = T.CreateDate
WHERE A2.DEPTDATETIME IS NOT null
GROUP BY A2.DeptDateTime,A2.Carrier,A2.FlightNum
HAVING count(T.[Name]) >=3
) ATN
ON A3.DeptDateTime = ATN.DeptDateTime AND A3.Carrier = ATN.Carrier AND A3.FlightNum = ATN.FlightNum
) RTN
INNER JOIN
TEMP_AIRITIN A4
ON A4.RLOC = RTN.RLOC AND A4.CreateDate = RTN.CreateDate
INNER JOIN
TEMP_TRAVELER T1
ON A4.RLOC = T1.RLOC AND A4.CreateDate = T1.CreateDate
thanks
September 15, 2003 at 5:03 pm
I'm still trying to decipher all this but where is the join specification for your first derived table A2.something = A3.something.
Sorry if I missed it but I just don't see it. Looks like a cartesian product....
Interested to see....
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
September 15, 2003 at 8:57 pm
How about a description of what you are trying to achieve. Also some sample data may help. I'm sure there is a simpler way than the many subselects, but it is difficult to get my head around what it is supposed to do.
David, the joins are correct. A3 joins to the subselect aliased as ATN. A2 is contained within this subselect.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply