declare @t1 table
(idx int identity(1,1), tripid varchar(10), tickno varchar(20), scout varchar(10),
ecout varchar(10), departdate datetime);
insert into @t1 values('1090','1234','hpt','kntn','3/21/2010');
insert into @t1 values('1090','1235','ast','jb','3/21/2010');
insert into @t1 values('1090','1236','hpt','trg','3/21/2010');
insert into @t1 values('1090','1237','trg','ah','3/21/2010');

declare @t2 table
(idx int identity(1,1), tripid varchar(10), cout varchar(10),
ttime varchar(6), ndf char(1));
insert into @t2 values('1090','ast','103000','n');
insert into @t2 values('1090','hpt','143000','n');
insert into @t2 values('1090','kntn','173000','n');
insert into @t2 values('1090','jb','213000','n');
insert into @t2 values('1090','trg','003000','y');
insert into @t2 values('1090','ah','063000','y');

select t1.tripid,t1.tickno,
t1.scout,t2.ndf as 'sNDF',t1.ecout,t3.ndf as 'eNDF',
t1.departdate
from @t1 t1 inner join @t2 t2 on t1.tripid=t2.tripid and t1.scout=t2.cout
            inner join @t2 t3 on t1.tripid=t3.tripid and t1.ecout=t3.cout

tripid | tickno  | scout  | sNDF  | ecout | eNDF  | departdate  | NDF
-------------------------------------------------------------------------------
1090	1234	hpt	n	kntn	n	2010-03-21 00:00:00.000   n
1090	1235	ast	n	jb	n	2010-03-21 00:00:00.000   n
1090	1236	hpt	n	trg	y	2010-03-21 00:00:00.000   y
1090	1237	trg	y	ah	y	2010-03-21 00:00:00.000   y

Case when  sNDF='n' AND eNDF='n' then 'n'
     when  sNDF='n' AND eNDF='y'then 'y'
and so on
End as NDF

select t1.tripid,t1.tickno,
t1.scout,t2.ndf as 'sNDF',t1.ecout,t3.ndf as 'eNDF',
t1.departdate,
CASE WHEN t2.ndf='n' AND t3.ndf = 'n' THEN 'n'
     WHEN t2.ndf='n' AND t3.ndf = 'y' THEN 'y'
     WHEN t2.ndf='y' AND t3.ndf = 'y' THEN 'y'
END
...