case when logic - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

case when logic

Author

Topic

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-09-18 : 13:11:07

JourneyNumber SegmentNumber PassengerID FlightNUmber DepartureStation ArrivalStation IDNO CountryCode
1 1 157117557 213 MEL KUL ABCDE MY
1 2 157117557 1926 KUL HKT ABCDE TH
2 1 157117557 1929 HKT KUL ABCDE MY
2 2 157117557 212 KUL MEL ABCDE AU
1 1 172429009 6123 PEN KUL DEFGH MY
1 2 172429009 202 KUL OOL DEFGH AU

How can i only pull out those on segmentNumber=2 and arrivalstation='AU' to tag along with the journeynumber.

For this case, output should be like this:

JourneyNumber SegmentNumber PassengerID FlightNUmber DepartureStation ArrivalStation IDNO CountryCode
2 1 157117557 1929 HKT KUL ABCDE MY
2 2 157117557 212 KUL MEL ABCDE AU
1 1 172429009 6123 PEN KUL DEFGH MY
1 2 172429009 202 KUL OOL DEFGH AU

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-18 : 14:00:39

[code]select * from yourTable y where JourneyNumber in
(select JourneyNumber from YourTable x where x.JourneyNumber=y.JourneyNumber
and x.arrivalstation = 'AU' and x.segementnumber = 2);[/code]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-09-18 : 14:04:29

My guess:

select 
	* 
from 
	yourTable y 
inner join
	(
		select 
			JourneyNumber 
			,PassengerID 
		from 
			YourTable 
		where 
			x.arrivalstation = 'AU' 
			and x.segementnumber = 2	
	) as a
	on y.JourneyNumber  = a.JourneyNumber
	and y.PassengerID = a.PassengerID

I'm assuming that you want to "group" by passenger...?

EDIT: forgot the ON clause.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-18 : 14:05:43

I can't imagine that he wouldn't want to do that. Thanks for catching that Lamprey.

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-09-18 : 21:47:51

i get nothing in return.

the countryCode refers to another table join with departure/arrival station to get the countrycode.

any help

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-09-18 : 21:55:45

I got what you meant now.

Thanks for the help :D

cheers

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-09-18 : 22:11:18

how can i apply Lamprey method if only would like to select the 1st journeynumber which depart from PEN?

i tried: where y.departurestation='PEN' but it only appear as below:

JourneyNumber SegmentNumber PassengerID FlightNUmber DepartureStation ArrivalStation IDNO CountryCode
1 1 172429009 6123 PEN KUL DEFGH MY

would like to appear both route with segmentnumber 1 and 2 on same passengerid

JourneyNumber SegmentNumber PassengerID FlightNUmber DepartureStation ArrivalStation IDNO CountryCode
1 1 172429009 6123 PEN KUL DEFGH MY
1 2 172429009 202 KUL OOL DEFGH AU

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-09-19 : 02:20:47

[code]select *
from YourTable a
where exists (
select 1
from YourTable b
where a.PassengerID = b.PassengerID
and a.JourneyNumber = b.JourneyNumber
and ~~b.CountryCode~~b.DepartureStation = 'PEN'
~~and b.SegmentNumber = 2~~)
[/code]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-09-19 : 11:10:46

quote:
Originally posted by peace

how can i apply Lamprey method if only would like to select the 1st journeynumber which depart from PEN?

i tried: where y.departurestation='PEN' but it only appear as below:

JourneyNumber SegmentNumber PassengerID FlightNUmber DepartureStation ArrivalStation IDNO CountryCode
1 1 172429009 6123 PEN KUL DEFGH MY

would like to appear both route with segmentnumber 1 and 2 on same passengerid

JourneyNumber SegmentNumber PassengerID FlightNUmber DepartureStation ArrivalStation IDNO CountryCode
1 1 172429009 6123 PEN KUL DEFGH MY
1 2 172429009 202 KUL OOL DEFGH AU

How do you define first: Lowest number, by date, other?

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-09-19 : 15:09:18

I am sorry to butt in. It seems you already have answers to your scenario, but when I read the thread, I came to think of a few things.

Is one journey always 2 segments? If no, don't read any further.
What if a journey is 3 segments, would you want to see if a person has departed, been thru or arrived at for instance AU?

Fiddling around, I came up with these suggestions.

Your first question - arrived in AU (I added some segments, to make more than 2 segments on a single journey for a given person):


with yourtable (journeynumber,segmentnumber,passengerid,flightnumber,departurestation,arrivalstation,idno,countrycode)
  as (          select 1,1,157117557, 213,'MEL','KUL','ABCDE','MY'
      union all select 1,2,157117557,1926,'KUL','HKT','ABCDE','TH'
      union all select 2,1,157117557,1929,'HKT','KUL','ABCDE','MY'
      union all select 2,2,157117557, 202,'KUL','OOL','ABCDE','AU'
      union all select 2,3,157117557, 203,'OOL','KUL','ABCDE','AU'
      union all select 2,4,157117557, 212,'KUL','MEL','ABCDE','AU'
      union all select 1,1,172429009,6123,'PEN','KUL','DEFGH','MY'
      union all select 1,2,172429009, 202,'KUL','OOL','DEFGH','AU'
     )
select c.*
  from (select journeynumber
              ,passengerid
              ,min(segmentnumber) as minsegmentnumber
              ,max(segmentnumber) as maxsegmentnumber
          from yourtable
         group by journeynumber
                 ,passengerid
       ) as a
       inner join (select journeynumber
                         ,passengerid
                         ,segmentnumber
                     from yourtable
                    where countrycode='AU'
                  ) as b
               on b.journeynumber=a.journeynumber
              and b.passengerid=a.passengerid
              and b.segmentnumber=a.maxsegmentnumber
       inner join yourtable as c
               on c.journeynumber=a.journeynumber
              and c.passengerid=a.passengerid
              and c.segmentnumber between a.minsegmentnumber and a.maxsegmentnumber

Your second question - departure from PEN (Again I added some segments, to make more than 2 segments on a single journey for a given person):


with yourtable (journeynumber,segmentnumber,passengerid,flightnumber,departurestation,arrivalstation,idno,countrycode)
  as (          select 1,1,157117557, 213,'MEL','KUL','ABCDE','MY'
      union all select 1,2,157117557,1926,'KUL','HKT','ABCDE','TH'
      union all select 2,1,157117557,1929,'HKT','KUL','ABCDE','MY'
      union all select 2,2,157117557, 212,'KUL','MEL','ABCDE','AU'
      union all select 1,1,172429009,6123,'PEN','KUL','DEFGH','MY'
      union all select 1,2,172429009, 202,'KUL','OOL','DEFGH','AU'
      union all select 1,3,172429009, 203,'OOL','KUL','ABCDE','AU'
     )
select c.*
  from (select journeynumber
              ,passengerid
              ,min(segmentnumber) as minsegmentnumber
              ,max(segmentnumber) as maxsegmentnumber
          from yourtable
         group by journeynumber
                 ,passengerid
       ) as a
       inner join (select journeynumber
                         ,passengerid
                         ,segmentnumber
                     from yourtable
                    where departurestation='PEN'
                  ) as b
               on b.journeynumber=a.journeynumber
              and b.passengerid=a.passengerid
              and b.segmentnumber=a.minsegmentnumber
       inner join yourtable as c
               on c.journeynumber=a.journeynumber
              and c.passengerid=a.passengerid
              and c.segmentnumber between a.minsegmentnumber and a.maxsegmentnumber

Lastly, if a person departed, been thru or arrived at for instance AU:


with yourtable (journeynumber,segmentnumber,passengerid,flightnumber,departurestation,arrivalstation,idno,countrycode)
  as (          select 1,1,157117557, 213,'MEL','KUL','ABCDE','MY'
      union all select 1,2,157117557,1926,'KUL','HKT','ABCDE','TH'
      union all select 2,1,157117557,1929,'HKT','KUL','ABCDE','MY'
      union all select 2,2,157117557, 212,'KUL','MEL','ABCDE','AU'
      union all select 1,1,172429009,6123,'PEN','KUL','DEFGH','MY'
      union all select 1,2,172429009, 202,'KUL','OOL','DEFGH','AU'
      union all select 1,3,172429009, 203,'OOL','KUL','ABCDE','AU'
     )
select b.*
  from (select a.journeynumber
              ,a.passengerid
              ,a.minsegmentnumber
              ,a.maxsegmentnumber
          from (select journeynumber
                      ,passengerid
                      ,min(segmentnumber) as minsegmentnumber
                      ,max(segmentnumber) as maxsegmentnumber
                  from yourtable
                 group by journeynumber
                         ,passengerid
               ) as a
               inner join yourtable as b
                       on b.journeynumber=a.journeynumber
                      and b.passengerid=a.passengerid
                      and b.segmentnumber between a.minsegmentnumber and a.maxsegmentnumber
                      and b.countrycode='AU'
         group by a.journeynumber
                 ,a.passengerid
                 ,a.minsegmentnumber
                 ,a.maxsegmentnumber
       ) as a
       inner join yourtable as b
               on b.journeynumber=a.journeynumber
              and b.passengerid=a.passengerid
              and b.segmentnumber between a.minsegmentnumber and a.maxsegmentnumber

Sorry for this long post.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources