February 22, 2006 at 3:30 pm
Evening Gents...
I have a stored procedure which is used to report a summary of activity for users in a booking system. The information is dragged from various tables which are predefined.
I need to be able to order the results by varying different columns many of which are populated by a subquery however it seems I cannot reference an alias in the ORDER BY statement. Can somebody suggest how best I can order the following query by any one of the fields within?
At present I can only order by the surname field below. The aim is to pass in a FIELD and DIRECTION in the stored procedure...
Procedure as follows:
SELECT
au.userid,
(au.firstname),
(au.surname),
(
SELECT
COUNT(DISTINCT(b.bookingid))
FROM
tbl_hp_bookings_transactions bt WITH (NOLOCK),
tbl_hp_bookings b WITH (NOLOCK)
WHERE
bt.bookingid = b.bookingid
AND
bt.transactiondate BETWEEN '20060201' AND '20060228'
AND
Month(bt.transactiondate)=Month(getdate())
AND
Year(bt.transactiondate)=Year(getdate())
AND
bt.transactiontypeid = 1000
AND
b.homeworkerid = au.userid
) monthlybookings,
ISNULL((
SELECT
SUM(transactionamount)
FROM
tbl_hp_bookings_transactions bt WITH (NOLOCK),
tbl_hp_bookings b WITH (NOLOCK)
WHERE
bt.bookingid = b.bookingid
AND
Month(bt.transactiondate)=Month(getdate())
AND
Year(bt.transactiondate)=Year(getdate())
AND
bt.transactiontypeid = 1000
AND
b.homeworkerid = au.userid
),0) as monthlygross,
ISNULL((
SELECT
SUM(transactionamount)
FROM
tbl_hp_bookings_transactions bt WITH (NOLOCK),
tbl_hp_bookings b WITH (NOLOCK)
WHERE
bt.bookingid = b.bookingid
AND
Month(bt.transactiondate)=Month(getdate())
AND
Year(bt.transactiondate)=Year(getdate())
AND
bt.transactiontypeid = 1002
AND
b.homeworkerid = au.userid
),0) AS monthlydiscounts,
ISNULL((
SELECT
SUM(commissionamount)
FROM
tbl_hp_bookings_transactions bt WITH (NOLOCK),
tbl_hp_bookings b WITH (NOLOCK)
WHERE
bt.bookingid = b.bookingid
AND
Month(bt.transactiondate)=Month(getdate())
AND
Year(bt.transactiondate)=Year(getdate())
AND
bt.transactiontypeid = 1000
AND
b.homeworkerid = au.userid
),0) AS commissiongross,
ISNULL((
SELECT
SUM(commissionamount)
FROM
tbl_hp_bookings_transactions bt WITH (NOLOCK),
tbl_hp_bookings b WITH (NOLOCK)
WHERE
bt.bookingid = b.bookingid
AND
Month(bt.transactiondate)=Month(getdate())
AND
Year(bt.transactiondate)=Year(getdate())
AND
bt.transactiontypeid = 1002
AND
b.homeworkerid = au.userid
),0) AS commissiondeductions,
(
SELECT
COUNT(enq_id)
FROM
tbl_hp_enquiry e WITH (NOLOCK)
WHERE
e.Enq_AcceptDate IS NOT NULL
AND
e.Enq_HWID = AU.UserID
) AS enquiriestaken
FROM
tbl_hp_adminusers au WITH (NOLOCK)
WHERE
au.ShowStats = 'Y'
Any advice would be greatly appreciated...
Thanks
Simon
February 22, 2006 at 3:41 pm
Setting aside the ORDER BY issue for a moment, this query is a performance nightmare wiating to happen.
Sub-SELECTs within a SELECT create a cursor-like slug of an execution plan.
You need to ge these into the FROM as derived tables then not only will the performance issue disappear, but the column names coming out of the derived tables will be available for use in ORDER BY.
I don't have time for all of them, but here's 1 of your sub-SELECTs implemented as a derived table and 1 of its columns used in an ORDER BY:
SELECT
au.userid,
(au.firstname),
(au.surname),
dtEnquiriesTaken.CountEnqByUser
FROM
tbl_hp_adminusers au WITH (NOLOCK)
INNER JOIN
-- Join to a derived table of count enquiries per user
(
SELECT Enq_HWID, COUNT(enq_id) As CountEnqByUser
FROM tbl_hp_enquiry e WITH (NOLOCK)
WHERE e.Enq_AcceptDate IS NOT NULL
GROUP BY Enq_HWID
) dtEnquiriestaken -- asign name to the derived table
ON (dtEnquiriestaken.Enq_HWID = au.userid)
WHERE au.ShowStats = 'Y'
ORDER BY dtEnquiriesTaken.CountEnqByUser
February 22, 2006 at 4:01 pm
Hi
Thanks for the advice - point taken. So the 2nd derived table would go like this:
INNER JOIN
(
SELECT
b.homeworkerid, COUNT(DISTINCT(b.bookingid)) AS MonthlyBookings
FROM
tbl_hp_bookings_transactions bt WITH (NOLOCK),
tbl_hp_bookings b WITH (NOLOCK)
WHERE
bt.bookingid = b.bookingid
AND
bt.transactiondate BETWEEN '20060201' AND '20060228'
AND
Month(bt.transactiondate)=Month(getdate())
AND
Year(bt.transactiondate)=Year(getdate())
AND
bt.transactiontypeid = 1000
GROUP BY b.homeworkerid
) dtMonthlyBookings
ON (dtMonthlyBookings.homeworkerid = au.userid)
I can see that this will be much quicker.
This raises a further point:
The report needs to retrieve all of the records from adminusers - now I am using these derived tables the query ignores the records in the adminusers table where the enquiries/bookings are 0.
How can I ensure all records are retrieved even where the results are a row of zeroes as this is needed for the report?
February 22, 2006 at 4:09 pm
>>How can I ensure all records are retrieved even where the results are a row of zeroes
Use LEFT JOIN instead of INNER JOIN. Since LEFT JOIN will return a NULL where there's no match, use IsNull to convert to zero for your report:
SELECT
au.userid,
(au.firstname),
(au.surname),
IsNull( dtEnquiriesTaken.CountEnqByUser, 0 ) As CountEnqByUser
FROM
tbl_hp_adminusers au WITH (NOLOCK)
LEFT JOIN
-- Join to a derived table of count enquiries per user
(
SELECT Enq_HWID, COUNT(enq_id) As CountEnqByUser
FROM tbl_hp_enquiry e WITH (NOLOCK)
WHERE e.Enq_AcceptDate IS NOT NULL
GROUP BY Enq_HWID
) dtEnquiriestaken -- asign name to the derived table
ON (dtEnquiriestaken.Enq_HWID = au.userid)
WHERE au.ShowStats = 'Y'
ORDER BY dtEnquiriesTaken.CountEnqByUser
February 22, 2006 at 4:09 pm
You would use LEFT OUTER JOIN instead of INNER JOIN for each of the derived tables if it is possible that no results may be returned
February 22, 2006 at 4:21 pm
Thanks guys - as you can see I'm a bit out of my depth here - covering for a colleague on annual leave.
I was previously retrieving a figure for gross bookings and a figure for deductions and then handling the calculation for net in the web page. It occurs to me that I can probably do this in the SQL too.
I tried something like this:
LEFT JOIN
(
(
SELECT
b.homeworkerid, SUM(transactionamount) AS TransactionAmount
FROM
tbl_hp_bookings_transactions bt WITH (NOLOCK),
tbl_hp_bookings b WITH (NOLOCK)
WHERE
bt.bookingid = b.bookingid
AND
Month(bt.transactiondate)=Month(getdate())
AND
Year(bt.transactiondate)=Year(getdate())
AND
bt.transactiontypeid = 1000
GROUP BY b.homeworkerid
)
MINUS
(
SELECT
b.homeworkerid, SUM(transactionamount) AS TransactionAmount
FROM
tbl_hp_bookings_transactions bt WITH (NOLOCK),
tbl_hp_bookings b WITH (NOLOCK)
WHERE
bt.bookingid = b.bookingid
AND
Month(bt.transactiondate)=Month(getdate())
AND
Year(bt.transactiondate)=Year(getdate())
AND
bt.transactiontypeid = 1000
GROUP BY b.homeworkerid
)
) dtMonthlyRevenue
ON (dtMonthlyRevenue.homeworkerid = au.userid)
but that doesn't quite seem to do the trick. Am I working along the right lines?
I really appreciate the assistance here - it's certainly motivated me to sit down and learn about this properly.
SImon
February 22, 2006 at 7:28 pm
MINUS is not valid SQL syntax
What you should do is something like:
SELECT IsNull(b.Bookings, 0) - IsNull(d.Deductions, 0) AS Revenue
LEFT OUTER JOIN (select id, sum(bookings) Bookings...) B ON ...
LEFT OUTER JOIN (select id, sum(deductions) Deductions...) D ON ...
February 23, 2006 at 12:45 am
Yep sorry - had actually been using - but using it in the outer joins. I have got the procedure running a treat now which just leaves me with the original Order By question.
I want to be able to order ASC or DESC on any of the columns within the query.
I can use a CASE statement to pass in the field to sort by dynamically as follows:
ORDER BY
CASE
WHEN @Field = 'surname' THEN surname
WHEN @Field = 'bookings' THEN bookings
ELSE surname
END
..however you don't seem to be able to add in a direction after the field - nor it seems can you use a further case statement to display the direction.
How can this be achieved without reverting to making the whole statement dynamic - which would presumably make it more complicated to understand?
Thanks in advance for any help people are able to offer...
Simon
February 23, 2006 at 8:12 am
Try something like this:
Order By
case when @Field = 'surname' and @SortAscending = 1 then surname else null end asc
,case when @Field = 'bookings' and @SortAscending = 1 then bookings else null end asc
,case when @Field = 'surname' and @SortAscending = 0 then surname else null end desc
,case when @Field = 'bookings' and @SortAscending = 0 then bookings else null end desc
February 23, 2006 at 9:01 am
Hi,
you can use the Top 100 percent in the subqueries by using this you can order by the internal subquery.
like :
---------------------------------------------
select
(select top 100 percent col1 from TableA where
col2=a.col2 order by 1)
from TableA a
order by 1
-------------------------------------------
REGARDS
AMIT
February 23, 2006 at 9:01 am
Hi,
you can use the Top 100 percent in the subqueries by using this you can order by the internal subquery.
like :
---------------------------------------------
select
(select top 100 percent col1 from TableA where
col2=a.col2 order by 1)
from TableA a
order by 1
-------------------------------------------
REGARDS
AMIT
February 23, 2006 at 9:04 am
Hi,
you can use the Top 100 percent in the subqueries by using this you can order by the internal subquery.
like :
---------------------------------------------
select
(select top 100 percent col1 from TableA where
col2=a.col2 order by 1)
from TableA a
order by 1
-------------------------------------------
REGARDS
AMIT
February 23, 2006 at 9:04 am
Hi,
you can use the Top 100 percent in the subqueries by using this you can order by the internal subquery.
like :
---------------------------------------------
select
(select top 100 percent col1 from TableA where
col2=a.col2 order by 1)
from TableA a
order by 1
-------------------------------------------
REGARDS
AMIT
February 23, 2006 at 9:34 am
Use of TOP 100 PERCENT in conjunction with ORDER BY is and always was a hack to try and implement ordering in a view. A view is a virtual table, with no inherent order, and an ORDER BY in a view (or sub-select) is meaningless in the relational world. Just because SQL 7 and SQL2K let you get away with the syntax is no reason to use it in production code.
In SQL 2005, functionality that depends on TOP 100 PERCENT with ORDER BY is now broken, which is always the risk of using barely supported hacks.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply