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.
Author |
Topic |
vl18
Starting Member
2 Posts |
Posted - 2008-04-17 : 09:13:27
|
Hi!Here's my function. The trouble - I can not make ORDER BY the "visits_count", "properties_count", "enquiries_count" fields.May be some one could help me with this?CREATE FUNCTION [dbo].[GetPagedStatistics] ( @start_index int, @count int, @condition nvarchar(255), @order_field nvarchar(255), @date_from datetime, @date_to datetime ) RETURNS @total_stat TABLE ( username nvarchar(255), first_name nvarchar(255), last_name nvarchar(255), properties_count int, enquiries_count int, visits_count int, id_user int) BEGIN INSERT @total_stat SELECT top (@count) dbo.users.username, dbo.users.first_name, dbo.users.last_name, ISNULL(COUNT(DISTINCT dbo.advertisement.id_advertisement), 0) AS properties_count, ISNULL(COUNT(DISTINCT dbo.enquiry_emails.id_enquiry_email), 0) AS enquiries_count, ISNULL(COUNT(DISTINCT dbo.property_statistics.id_statistics), 0) AS visits_count, dbo.users.id_userFROM dbo.property_statistics RIGHT OUTER JOIN dbo.advertisement RIGHT OUTER JOIN dbo.users ON dbo.advertisement.id_user = dbo.users.id_user LEFT JOIN dbo.enquiry_emails ON dbo.enquiry_emails.id_advertisement = dbo.advertisement.id_advertisement ON dbo.property_statistics.id_advertisement = dbo.advertisement.id_advertisementWHERE 1=@condition and (dbo.advertisement.creation_date <= @date_to and dbo.advertisement.creation_date >= @date_from ) and ( (dbo.enquiry_emails.creation_date <= @date_to and dbo.enquiry_emails.creation_date >= @date_from and dbo.property_statistics.view_date <= @date_to and dbo.property_statistics.view_date >= @date_from ) or (dbo.property_statistics.view_date is null) or (dbo.enquiry_emails.creation_date is null) ) and (ISNULL(dbo.advertisement.id_parent, 0) = 0) GROUP BY dbo.users.username, dbo.users.first_name, dbo.users.last_name, dbo.users.id_userorder by case when @order_field='username' then dbo.users.username end, case when @order_field='first_name' then dbo.users.first_name end, case when @order_field='last_name' then dbo.users.last_name end, case when @order_field='properties_count' then 1 end, case when @order_field='enquiries_count' then 1 end, case when @order_field='visits_count' then 1 end RETURN END |
|
|
|
|
|
|