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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy