Ranking - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Ranking

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-12-11 : 09:33:05

Is there a way I can rank each month (column)? In between each month would be the rank for that month. Here is my Pivot table right now it is set up to rank the row.

DECLARE @pivot_cols NVARCHAR(1000);
SELECT @pivot_cols =
STUFF((SELECT '],[' + year_month
FROM (SELECT DISTINCT RIGHT(CONVERT(VARCHAR(9),
CAST(dowrdt AS DATETIME),
6), 6),
CONVERT(CHAR(7), CAST(dowrdt AS
DATETIME), 126)
FROM #tempCnt) AS T(year_month, sort)
ORDER BY sort
FOR XML PATH('')
), 1, 2, '') + ']';

DECLARE @pivot_query NVARCHAR(2000);
SET @pivot_query =
N'SELECT sort, reg, ' + @pivot_cols +
N',ROW_NUMBER() OVER(ORDER BY Sort) AS rn ' +
N',RANK() OVER(ORDER BY Reg) AS rk ' +
N'FROM (SELECT sort, reg, ' +
N'RIGHT(CONVERT(VARCHAR(9), CAST(dowrdt AS DATETIME),
6),
6) AS year_month, ' +
N'CAST((1.0 * icnt / NULLIF(tcnt, 0) * 100) AS DECIMAL
(5,
2)) AS perc ' +
N'FROM #tempCnt) AS F ' +
N'PIVOT ' +
N'(MAX(perc) FOR year_month IN (' + @pivot_cols + ')) AS P ' +
N'ORDER BY Sort;';

EXEC(@pivot_query);

Here's the data with the word rk where I want to rank each row. Is that possible?


Sort     Reg  Nov08 rk Dec08 rk Jan09 rk Feb09 Mar09 

1       NAT   11.04   12.05   14.66   14.10   13.98   
A       BOS   8.94    10.37   11.88   11.68   11.83   
B       NYC   8.28    11.58   15.38   15.51   14.81   
C       PHI   11.96   13.66   15.46   13.91   14.05   
D       ATL   10.93   11.13   14.40   13.72   14.44   
E       CHI   10.57   12.10   14.34   13.65   13.56   
G       KCM   8.03    8.56    10.09   10.91   10.63   
H       DAL   10.03   10.32   12.49   12.22   11.81   
I       DEN   12.70   13.17   16.96   15.38   15.24   
J       SFO   14.38   13.86   17.44   17.24   15.84   
K       SEA   14.93   16.52   19.80   17.18   18.01