SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 apply same
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

peace
Constraint Violating Yak Guru

389 Posts

Posted - 03/05/2013 :  23:14:36  Show Profile  Reply with Quote
how can i apply the same for location?

go back location
US UK USUK
UK US USUK

James K
Flowing Fount of Yak Knowledge

3557 Posts

Posted - 03/05/2013 :  23:26:40  Show Profile  Reply with Quote
One way would be to always order in alphabetic order - for example:
SELECT
   go,
   back,
   CASE WHEN go > back THEN go+back ELSE back+go END AS location
FROM
   YourTable;
But, how did you decide that it is USUK and not UKUS?
Go to Top of Page

peace
Constraint Violating Yak Guru

389 Posts

Posted - 03/05/2013 :  23:39:51  Show Profile  Reply with Quote
i have a list of go and back country.
Those with return journey i only need to apply the go.
Is it possible to do that?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3557 Posts

Posted - 03/05/2013 :  23:46:59  Show Profile  Reply with Quote
In the example you posted, how do we determine which one is go and which one is back? Is it US -> UK -> US, or is it UK -> US -> UK?
Go to Top of Page

peace
Constraint Violating Yak Guru

389 Posts

Posted - 03/05/2013 :  23:51:03  Show Profile  Reply with Quote
there will be another column which is NUMBER.

GO = 1
BACK = 2
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 03/06/2013 :  04:34:13  Show Profile  Reply with Quote
at least I am not clear with yours requirements. Provide business logic along with same data in form of insert statments and the desired output you want to have it. And that how the output would be manipulated in terms of inforamtion. Thanks!

Cheers
MIK
Go to Top of Page

peace
Constraint Violating Yak Guru

389 Posts

Posted - 03/06/2013 :  04:50:15  Show Profile  Reply with Quote
go back location number
US UK USUK 1
UK US USUK 2
AUS UK AUSUK 1
UK AUS AUSUK 2
NZ US NZUS 1
US NZ NZUS 2

i would like the location column to appear the first segment.
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 03/06/2013 :  04:54:50  Show Profile  Reply with Quote
SO all this is stored information? And that you want to have location column appear at start? e.g. Locatioin, Go, Back ..

Cheers
MIK

Edited by - MIK_2008 on 03/06/2013 04:55:35
Go to Top of Page

peace
Constraint Violating Yak Guru

389 Posts

Posted - 03/06/2013 :  09:21:23  Show Profile  Reply with Quote
What i wanted is, the location for number 2 to be same as number 1.

go back location number
US UK USUK 1
UK US USUK 2

i tried this:

select ....
case when location = 1, then go+back else go+back end

but it appear as below:

go back location number
US UK USUK 1
UK US NULL 2
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 03/06/2013 :  09:42:30  Show Profile  Reply with Quote
Is this what you're wanting?


SELECT
go,
back,
CASE WHEN Number=1 THEN go+back WHEN Number=2 THEN back+go END AS location
FROM
YourTable;


Cheers
MIK
Go to Top of Page

peace
Constraint Violating Yak Guru

389 Posts

Posted - 03/06/2013 :  23:00:27  Show Profile  Reply with Quote
the query below i get it right but when theres only number=1, it will take the go+back instead of back+go

example on the UKAUS:
go back location number
AUS UK UKAUS 1

go back location number
US UK USUK 1
UK US USUK 2
AUS UK UKAUS 1

SELECT
go,
back,
CASE WHEN Number=1 THEN go+back WHEN Number=2 THEN back+go END AS location
FROM
YourTable;
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 03/07/2013 :  05:02:42  Show Profile  Reply with Quote
you yourself said that when it number=1 then go+back else it should be back+go. Now you'are saying you want to have back+go when there's only number=1

For what purpose you want to extract such information? What this inforamtion will show you? ..dont just simply say you want this .. explain it in DETAIL with the help of example(s) even if it take PAGES.

Cheers
MIK
Go to Top of Page

peace
Constraint Violating Yak Guru

389 Posts

Posted - 03/07/2013 :  09:24:12  Show Profile  Reply with Quote
Here what I actually trying to get.

customerID journeyID journey go back location
171852357 117267752 1 KUL TPE KULTPE
171852357 117267752 2 TPE KUL KULTPE
171852358 117267752 1 KUL TPE KULTPE
171852358 117267752 2 TPE KUL KULTPE
171861489 117272910 2 HKT KUL KULHKT

The last data which this customer only have journey 2, i would like the location to be HKTKUL instead of KULHKT.

How can i apply that?
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 03/07/2013 :  12:24:36  Show Profile  Reply with Quote
SELECT
X.*
,CASE
WHEN X.Journey=1 AND Y.IsReturn=2 THEN go+back
WHEN X.Journey=2 AND Y.IsReturn=2 THEN back+go
WHEN X.Journey=2 AND Y.IsReturn=1 THEN back+go
END AS location
FROM (
SELECT 171852357 as customerID,117267752 as journeyID,1 Journey, 'KUL' as [Go],'TPE' Back UNION ALL
SELECT 171852357,117267752,2,'TPE','KUL'UNION ALL
SELECT 171852358,117267752,1,'KUL','TPE'UNION ALL
SELECT 171852358,117267752,2,'TPE','KUL'UNION ALL
SELECT 171861489,117272910,2,'HKT','KUL')X
INNER JOIN (
--customers who have travelled two sided return ticket
SELECT customerID,journeyID,COUNT(1) as IsReturn
FROM (
SELECT 171852357 as customerID,117267752 as journeyID,1 Journey, 'KUL' as [Go],'TPE' Back UNION ALL
SELECT 171852357,117267752,2,'TPE','KUL'UNION ALL
SELECT 171852358,117267752,1,'KUL','TPE'UNION ALL
SELECT 171852358,117267752,2,'TPE','KUL'UNION ALL
SELECT 171861489,117272910,2,'HKT','KUL'
)A
GROUP BY customerID,journeyID Having COUNT(1)=2

UNION ALL
--customers who have travelled one sided
SELECT customerID,journeyID,COUNT(1)
FROM (
SELECT 171852357 as customerID,117267752 as journeyID,1 Journey, 'KUL' as [Go],'TPE' Back UNION ALL
SELECT 171852357,117267752,2,'TPE','KUL'UNION ALL
SELECT 171852358,117267752,1,'KUL','TPE'UNION ALL
SELECT 171852358,117267752,2,'TPE','KUL'UNION ALL
SELECT 171861489,117272910,2,'HKT','KUL'
)A
GROUP BY customerID,journeyID Having COUNT(1)=1

) Y ON X.CustomerID=Y.CustomerID and X.JourneyID=Y.JourneyID

Cheers
MIK
Go to Top of Page

basicconfiguration
Constraint Violating Yak Guru

356 Posts

Posted - 03/07/2013 :  15:34:51  Show Profile  Reply with Quote
i recommend creating two tables:

drop table trip
go

create table trip (
customerID int, journeyID int, journey int, go varchar(50) , back varchar(50))


insert trip

select 171852357, 117267752, 1, 'KUL' ,'TPE' union all
select 171852357, 117267752, 2 ,'TPE' ,'KUL' union all
select 171852358, 117267752, 1 ,'KUL' ,'TPE' union all
select 171852358, 117267752, 2 ,'TPE' ,'KUL' union all
select 171861489, 117272910, 2 ,'HKT' ,'KUL' union all
select 100000000, 117272910, 1 , 'UK' ,'AUS'

DROP TABLE ONETRIP
DROP TABLE TWOTRIP

select customerid into onetrip from trip
group by customerid
having count(customerid) =1

select customerid into twotrip from trip
group by customerid
having count(customerid) =2



select trip.customerID, trip.journeyid, trip.journey, trip.go, trip.back,
case when trip.journey = 2 then go + back
when trip.journey = 1 then back + go
end location
from onetrip a
join trip trip
on a.customerID = trip.customerID
union

select trip.customerID, trip.journeyid, trip.journey, trip.go, trip.back,
case when trip.journey = 2 then back + go
when trip.journey = 1 then go + back
end location
from twotrip a
join trip trip
on a.customerID = trip.customerID
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000