February 6, 2010 at 4:39 am
/*My tables and rows as follow*/
declare @tAllBus table
(
BusTypeCode varchar(30) not null,
BusN varchar(30) not null
)
/*BusN is a unique*/
/*Same BusTypeCode but different BusN*/
insert into @tAllBus values('NOCODE','JHW 439')
insert into @tAllBus values('NOCODE','JKP 6354')
insert into @tAllBus values('STD26','JHS7846')
insert into @tAllBus values('STD26','JJN4906')
insert into @tAllBus values('STD29','JJG6507')
insert into @tAllBus values('STD40 (A)','JJS3258')
insert into @tAllBus values('STD44 (A)','JKP 7277')
insert into @tAllBus values('STD44 (A)','JLD 8587')
insert into @tAllBus values('STD48','JKM7921')
insert into @tAllBus values('STD44 (A)','JGT 3112')
insert into @tAllBus values('STD44 (A)','JKT 7277')
insert into @tAllBus values('STD29','JJG6516')
declare @tTrip table
(
RID varchar(50) not null,
Posi tinyint not null,
BusTypeCode varchar(30) not null,
TripNme varchar(50) not null
)
/*Combination of RID,Posi is a unique*/
/*BusTypeCode refer to @tAllBus(BusTypeCode)*/
insert into @tTrip values('_r00000006',1,'STD29','KL-JB-1')
insert into @tTrip values('_r00000006',2,'STD26','KL-JB-2')
insert into @tTrip values('_r00000006',3,'STD29','KL-JB-3')
insert into @tTrip values('_r00000006',4,'STD29','KL-JB-4')
insert into @tTrip values('_r00000006',5,'STD29','KL-JB-8')
insert into @tTrip values('_r00000010',10,'STD26','JB-KL-12')
insert into @tTrip values('_r00000010',11,'STD29','JB-KL-13')
insert into @tTrip values('_r00000010',12,'STD26','JB-KL-6')
insert into @tTrip values('_r00000016',1,'STD44 (A)','JB-BP-1')
insert into @tTrip values('_r00000016',2,'STD44 (A)','JB-BP-2')
insert into @tTrip values('_r00000019',2,'STD40 (A)','MS-JB-2')
insert into @tTrip values('_r00000019',4,'STD40 (A)','MS-JB-4')
insert into @tTrip values('_r00000024',1,'STD26','JBKLG01')
insert into @tTrip values('_r00000024',2,'STD26','JBKLG02')
insert into @tTrip values('_r00000024',3,'STD26','JBKLG03')
insert into @tTrip values('_r00000036',3,'STD40 (A)','BP - KLG 3')
insert into @tTrip values('_r00000044',1,'STD26','KLG - JB 1')
insert into @tTrip values('_r00000044',2,'STD26','KLG - JB 2')
insert into @tTrip values('_r00000044',3,'STD26','KLG - JB 3')
insert into @tTrip values('_r00000046',4,'STD44 (A)','CL-SN-4')
insert into @tTrip values('_r00000046',5,'STD44 (A)','CL-SN-5')
/*
1. How to generate SQL to built below resultset?
2. As you can see, RandomBusN is random pickup from @tAllBus based on BusTypeCode
RID |Posi |BusTypeCode|TripNme |RandomBusN
------------------------------------------------------------
_r000000061STD29 KL-JB-1 JJG6507
_r000000062STD26 KL-JB-2 JJN4906
_r000000063STD29 KL-JB-3 JHS7846
_r000000064STD29 KL-JB-4 JJG6507
_r000000065STD29 KL-JB-8 JJG6507
_r0000001010STD26 JB-KL-12 JHS7846
_r0000001011STD29 JB-KL-13 JJG6507
_r0000001012STD26 JB-KL-6 JJN4906
_r000000161STD44 (A) JB-BP-1 JKP 7277
_r000000162STD44 (A) JB-BP-2 JLD 8587
_r000000192STD40 (A) MS-JB-2 JKP 7277
_r000000194STD40 (A) MS-JB-4 JJS3258
_r000000241STD26 JBKLG01 JJN4906
_r000000242STD26 JBKLG02 JJN4906
_r000000243STD26 JBKLG03 JHS7846
....
....
*/
I'm stuck
February 6, 2010 at 5:29 am
There are many ways to do it, but I prefer the CROSS APPLY way as it seems to be faster than other methods.
SELECTT.RID, T.Posi, T.BusTypeCode, T.TripName, AB.BusN AS RandomBusN
FROM@tTrip T
CROSS APPLY
(
SELECTTOP 1 AB.BusN
FROM@tAllBus AB
WHERET.BusTypeCode = AB.BusTypeCode
ORDER BY NEWID()
) AB
--Ramesh
February 6, 2010 at 7:23 am
tq sir
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply