August 16, 2006 at 9:49 am
I am having trouble figuring out how to properly sort the results of a query that takes daily entries and summarizes them by month over a period. I used the DATENAME function to group the records by month/year, but that creates a text result that I cannot sort chronologically (oldest to newest). The query I am using looks something like this (some table joins and WHERE clause omitted):
SELECT
DATENAME(month, referral.referral_date) + ' ' + DATENAME(year, referral.referral_date) AS Period,
AVG(DATEDIFF(d, A.[First Contact], referral.referral_date)) AS Ref_time
FROM
referral INNER JOIN ih_referral ON referral.referral_ID = ih_referral.referral_ID
GROUP BY
ih_referral.referral_ID) A ON referral.referral_ID = A.referral_ID
GROUP BY CONVERT(char(4), DATEPART(yyyy, referral.referral_date)) + CONVERT(char(2), DATEPART(mm, referral.referral_date)), DATENAME(month, referral.referral_date) + ' ' + DATENAME(year, referral.referral_date)
The result set needed looks like this:
Period Ref_Time Ref_Count
August 2006 25 345
August 2005 18 421
July 2006 7 456
Etc....
I need to sort this result set chronologically by period. Obviously a field not used in the SELECT can't be used in the ORDER BY clause.
Any suggestions would be appreciated.
Thanks
Tim
August 16, 2006 at 9:55 am
>>Obviously a field not used in the SELECT can't be used in the ORDER BY clause.
Put your SELECT into a Derived Table and add on 1 extra column
as a sort key. Order by the SortKey in the outer SELECT:
SELECT Period, Ref_Time
FROM
(
SELECT
DATENAME(month, referral.referral_date) + ' ' + DATENAME(year, referral.referral_date) AS Period,
AVG(DATEDIFF(d, A.[First Contact], referral.referral_date)) AS Ref_time,
MIN(referral.referral_date) As SortKey
FROM
referral INNER JOIN ih_referral ON referral.referral_ID = ih_referral.referral_ID
GROUP BY
DATENAME(month, referral.referral_date) + ' ' + DATENAME(year, referral.referral_date)
) dt
ORDER BY SortKey
August 16, 2006 at 11:14 am
Perfect! Thanks so much.
Tim
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply