You don't want to count the destinations, you just want to know which was the first and which was the last. You can do this by using GROUP BY on the booking code and getting the MIN and MAX of the sequence number. E.g.
select booking, max(sequence) as final, min(sequence) as [first]
from booking_destinations bd
group by booking
You can then incorporate that sub-query into an overall result by either using a sub-query OR (my preference) using a Common Table Expression...
Sub-query example:
select fl.booking, b.Pickup_location, bd.sequence, bd.destination,
case when bd.sequence = fl.final then 'Final'
when bd.sequence = fl.[first] then 'First'
else 'Interim'
end
from
( select booking, max(sequence) as final, min(sequence) as [first]
from booking_destinations bd
group by booking
) fl
join booking b ON fl.booking = b.id
join booking_destinations bd on b.id = bd.booking
join location l on bd.destination = l.zip_code
CTE example:
;with CTE as
(
select booking, max(sequence) as final, min(sequence) as [first]
from booking_destinations bd
group by booking
)
select CTE.booking, b.Pickup_location, bd.sequence, bd.destination,
case when bd.sequence = CTE.final then 'Final'
when bd.sequence = CTE.first then 'First'
else 'Interim'
end
from CTE
join booking b ON CTE.booking = b.id
join booking_destinations bd on b.id = bd.booking
join location l on bd.destination = l.zip_code
I made some assumptions about your table structure ... this is the code I used to create some test data (only the columns I needed have been included)
create table booking
(
id int,
Pickup_location bigint
)
insert into booking values (1001, 44456),(2005, 44457)
create table booking_destinations
(
booking int,
[sequence] int,
destination bigint
)
insert into booking_destinations values
(1001,1,43367),
(1001,2,43876),(2005,1,56721),(2005,2,53242),(2005,3,45234)
create table location
(
id int identity(1,1),
zip_code bigint
)
insert into location values(44456),(44457),(43367),(43876),(56721),(53242),(45234)
Submit an article or tip