February 25, 2006 at 2:40 am
Hi
Creating another report - still in the early stages. I have a list of bookings which is referenced to a list of customers. For each booking there are multiple booking transactions which will be totalled using a series of JOINs however I figured the best base point was a list of distinct bookings.
The query below pulls all of the bookings however if I add a DISTINCT wrapper around the BookingID this causes my ORDER BY statements not to work.
Does anybody have any suggestions how to only retrieve a list of distinct booking ids for the base of this query?
Thanks in advance for any assistance offered:
SELECT
B.BookingID,
ISNULL(UPPER(C.CustomerSurname),'
FROM
tbl_hp_bookings B WITH (NOLOCK),
tbl_hp_bookings_transactions BT WITH (NOLOCK),
tbl_hp_customers C WITH (NOLOCK)
WHERE
B.BookingID = BT.BookingID
AND
BT.TransactionTypeID != 1001
AND
B.CustomerID = C.CustomerID
ORDER BY
CASE
WHEN @SortDir = 'a' AND @Field = 'client' THEN C.CustomerSurname
END ASC,
CASE
WHEN @SortDir = 'd' AND @Field = 'client' THEN C.CustomerSurname
END DESC
February 25, 2006 at 9:10 am
If you place the select distinct in a derived table you should be able to order by the column you desire and have select distincts.
select *
FROM (SELECT distinct B.BookingID, ISNULL(UPPER(C.CustomerSurname),'') AS CustomerSurname
FROM tbl_hp_bookings B WITH (NOLOCK),
tbl_hp_bookings_transactions BT WITH (NOLOCK),
tbl_hp_customers C WITH (NOLOCK)
WHERE B.BookingID = BT.BookingID
AND BT.TransactionTypeID != 1001
AND B.CustomerID = C.CustomerID
) mydata
ORDER BY
CASE WHEN @SortDir = 'a' AND @Field = 'client' THEN CustomerSurname END ASC,
CASE WHEN @SortDir = 'd' AND @Field = 'client' THEN CustomerSurname END DESC
February 25, 2006 at 11:44 am
*rewritten with joins
*group by takes care of distinct
SELECT B.BookingID,
ISNULL(UPPER(C.CustomerSurname),'') AS CustomerSurname
FROM tbl_hp_bookings B WITH (NOLOCK)
INNER JOIN tbl_hp_bookings_transactions BT WITH (NOLOCK)
ON B.BookingID = BT.BookingID
AND BT.TransactionTypeID <> 1001
LEFT JOIN tbl_hp_customers C WITH (NOLOCK)
ON B.CustomerID = C.CustomerID
group by B.BookingID,C.CustomerSurname /*distinct bookings*/
ORDER BY
CASE
WHEN @SortDir = 'a' AND @Field = 'client' THEN C.CustomerSurname
END ASC,
CASE
WHEN @SortDir = 'd' AND @Field = 'client' THEN C.CustomerSurname
END DESC
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply