June 27, 2009 at 10:23 am
I think this should get you started. I did nothing to eliminate ties. Since this is a 2005 forum I provided a 2005 answer.
DECLARE @tasks TABLE (dte SmallDATETIME, quantity SmallINT, task VARCHAR(25))
INSERT INTO
@tasks (dte, quantity, task)
SELECT
'6/1/2009',
1,
'church'
UNION ALL
SELECT
'6/1/2009',
1,
'store'
UNION ALL
SELECT
'6/1/2009',
1,
'mall'
UNION ALL
SELECT
'6/1/2009',
1,
'dogwalking'
UNION ALL
SELECT
'6/1/2009',
1,
'mowing'
UNION ALL
SELECT
'6/1/2009',
1,
'cleaning'
UNION ALL
SELECT
'6/1/2009',
1,
'church'
UNION ALL
SELECT
'6/1/2009',
1,
'church'
UNION ALL
SELECT
'6/1/2009',
1,
'cleaning'
UNION ALL
SELECT
'6/2/2009',
1,
'church'
UNION ALL
SELECT
'6/2/2009',
1,
'store'
UNION ALL
SELECT
'6/2/2009',
1,
'fixing'
UNION ALL
SELECT
'6/2/2009',
1,
'dogwalking'
UNION ALL
SELECT
'6/2/2009',
1,
'mowing'
UNION ALL
SELECT
'6/2/2009',
1,
'washing'
UNION ALL
SELECT
'6/2/2009',
1,
'church'
UNION ALL
SELECT
'6/2/2009',
1,
'laundry'
UNION ALL
SELECT
'6/2/2009',
1,
'cleaning'
;WITH cteRank AS
(
SELECT
ROW_NUMBER() OVER(PARTITION BY dte ORDER BY SUM(quantity) desc) AS row,
RANK() OVER(PARTITION BY dte ORDER BY SUM(quantity) desc) AS rnk,
DENSE_RANK() OVER(PARTITION BY dte ORDER BY SUM(quantity) desc) AS dense_rnk,
dte,
SUM(quantity) AS qty,
task
FROM
@tasks
GROUP BY
dte,
task
)
SELECT
dense_rnk,
row,
rnk,
dte,
task,
qty
FROM
cteRank
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 27, 2009 at 11:15 am
Your solution works perfectly. Thank you so much !!!:-)
Viewing 2 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply