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.
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 locationUS UK USUKUK 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 locationFROM YourTable; But, how did you decide that it is USUK and not UKUS? |
|
|
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? |
|
|
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? |
|
|
peace
Constraint Violating Yak Guru
420 Posts |
Posted - 2013-03-05 : 23:51:03
|
there will be another column which is NUMBER.GO = 1BACK = 2 |
|
|
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!CheersMIK |
|
|
peace
Constraint Violating Yak Guru
420 Posts |
Posted - 2013-03-06 : 04:50:15
|
go back location numberUS UK USUK 1 UK US USUK 2AUS UK AUSUK 1UK AUS AUSUK 2NZ US NZUS 1US NZ NZUS 2i would like the location column to appear the first segment. |
|
|
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 ..CheersMIK |
|
|
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 numberUS UK USUK 1UK US USUK 2i tried this:select ....case when location = 1, then go+back else go+back endbut it appear as below:go back location numberUS UK USUK 1UK US NULL 2 |
|
|
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 locationFROM YourTable;CheersMIK |
|
|
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+goexample on the UKAUS:go back location numberAUS UK UKAUS 1go back location numberUS UK USUK 1 UK US USUK 2AUS UK UKAUS 1SELECTgo,back,CASE WHEN Number=1 THEN go+back WHEN Number=2 THEN back+go END AS locationFROMYourTable; |
|
|
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.CheersMIK |
|
|
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 location171852357 117267752 1 KUL TPE KULTPE171852357 117267752 2 TPE KUL KULTPE171852358 117267752 1 KUL TPE KULTPE171852358 117267752 2 TPE KUL KULTPE171861489 117272910 2 HKT KUL KULHKTThe 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? |
|
|
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+goEND AS locationFROM ( 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')XINNER 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.JourneyIDCheersMIK |
|
|
basicconfiguration
Constraint Violating Yak Guru
358 Posts |
Posted - 2013-03-07 : 15:34:51
|
i recommend creating two tables:drop table tripgocreate table trip (customerID int, journeyID int, journey int, go varchar(50) , back varchar(50))insert tripselect 171852357, 117267752, 1, 'KUL' ,'TPE' union allselect 171852357, 117267752, 2 ,'TPE' ,'KUL' union all select 171852358, 117267752, 1 ,'KUL' ,'TPE' union allselect 171852358, 117267752, 2 ,'TPE' ,'KUL' union allselect 171861489, 117272910, 2 ,'HKT' ,'KUL' union allselect 100000000, 117272910, 1 , 'UK' ,'AUS' DROP TABLE ONETRIPDROP TABLE TWOTRIPselect customerid into onetrip from tripgroup by customerid having count(customerid) =1select customerid into twotrip from tripgroup by customeridhaving count(customerid) =2select 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 tripon a.customerID = trip.customerIDunion 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 tripon a.customerID = trip.customerID |
|
|
|
|
|
|
|