disregard succeeding - 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

SQL Server 2000 Forums

SQL Server Development (2000)

disregard succeeding

Author

Topic

marileng
Starting Member

28 Posts

Posted - 2002-06-19 : 01:05:52

The trucking charge is 200, if the vin have the same tripticket then
the truckingcharge will be charge only to the first vin .

my query is this:

Select vin,tripticket, truckingcharge=200 ,storagecharges=200
from vehicleInfo

VIN TRIPTICKET TRUCKINGCHARGE STORAGECHARGE
1 1 200 200.00
2 1 200 200.00
3 2 200 200.00
4 2 200 200.00
5 2 200 200.00

Results should be :

VIN TRIPTICKET TRUCKINGCHARGE STORAGECHARGE
1 1 200 200.00
2 1 0 Dis 200.00
3 2 200 200.00
4 2 0 200.00
5 2 0 200.00

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2002-06-19 : 01:47:11

create table #tripinfo (
vin int,
tripticket int,
truckcharge int default (200),
storagecharge int default (200)
)

insert into #tripinfo (vin, tripticket)
values (1,1)
insert into #tripinfo (vin, tripticket)
values (2,1)
insert into #tripinfo (vin, tripticket)
values (3,2)
insert into #tripinfo (vin, tripticket)
values (4,2)
insert into #tripinfo (vin, tripticket)
values (5,2)

Tested this, it should work ok...

select vin, tripticket,
(select truckcharge =
case
when vin = (select min(vin) from #tripinfo t2 where t2.tripticket = t1.tripticket) then truckcharge
else '0'
end) truckcharge,
storagecharge
from #tripinfo t1

hth,
Justin
<

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-06-19 : 01:53:46

Here is another way to skin a cat...


Select min(VIN) as Vin, TripTicket, truckingcharge=200 ,storagecharges=200 
from vehicleInfo 
group by Trip
union all
Select Vin, TripTicket, truckingcharge=0 ,storagecharges=200
from vehicleInfo 
where Vin not in (Select min(VIN) as Vin from v group by Trip)

I am pretty sure Justin's will be more efficent....

DavidM

"SQL-3 is an abomination.."

Edited by - byrmol on 06/19/2002 01:54:29

marileng
Starting Member

28 Posts

Posted - 2002-06-19 : 01:55:06

thanks justin just what I needed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources