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
 apply same

Author  Topic 

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-03-05 : 23:14:36
how can i apply the same for location?

go back location
US UK USUK
UK US USUK

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-05 : 23:26:40
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

420 Posts

Posted - 2013-03-05 : 23:39:51
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-05 : 23:46:59
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

420 Posts

Posted - 2013-03-05 : 23:51:03
there will be another column which is NUMBER.

GO = 1
BACK = 2
Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-03-06 : 04:34:13
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

420 Posts

Posted - 2013-03-06 : 04:50:15
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
Master Smack Fu Yak Hacker

1054 Posts

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

Cheers
MIK
Go to Top of Page

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-03-06 : 09:21:23
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
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-03-06 : 09:42:30
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

420 Posts

Posted - 2013-03-06 : 23:00:27
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
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-03-07 : 05:02:42
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

420 Posts

Posted - 2013-03-07 : 09:24:12
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
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-03-07 : 12:24:36
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

358 Posts

Posted - 2013-03-07 : 15:34:51
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
   

- Advertisement -