Link to home
Start Free TrialLog in
Avatar of Leo Torres
Leo TorresFlag for United States of America

asked on

Tsql Rank, SQL 2008

Hello,

I am still a bit green when it comes to using Rank

I Need to have a query pull the top 5 weeks in a year 2007

The query below works with AdventureWorks2008R2 Database
All my ranks are 1 I need it to rank the top 5


SELECT convert(date,dateadd( d,   7 - datepart(dw,ph.OrderDate), ph.OrderDate))  as WeekEndDate--Convert(Date,DateAdd(day, -1 * datepart(dw, ph.OrderDate), ph.OrderDate)) as WeekEnding
	   ,SUM(ph.TotalDue) As GrandTotal
	   ,RANK() Over (Partition BY dateadd( d,7 - datepart(dw,ph.OrderDate), ph.OrderDate) Order by SUM(ph.TotalDue) desc) rn
FROM Purchasing.PurchaseOrderHeader ph
Where YEAR(OrderDate) = 2007
GROUP BY dateadd( d,   7 - datepart(dw,ph.OrderDate), ph.OrderDate)
Order by SUM(ph.TotalDue) desc

Open in new window

Avatar of Sean Stuber
Sean Stuber

I'd suggest not using RANK for this


SELECT top 5 dateadd( d,   7 - datepart(dw,ph.OrderDate), ph.OrderDate)  as WeekEndDate
         ,SUM(ph.TotalDue) As GrandTotal
FROM Purchasing.PurchaseOrderHeader ph
Where YEAR(OrderDate) = 2004
GROUP BY dateadd( d,   7 - datepart(dw,ph.OrderDate), ph.OrderDate)
Order by SUM(ph.TotalDue) desc;
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Leo Torres

ASKER

Ok, so How can I display rank by grand Total
use the rank query,  but include the "r" column


select WeekEndDate, GrandTotal, r from ....
Guess I should Have mentioned that Displaying the rank is part of the requirement
not a problem,  now you have 2 ways to do it without displaying rank, and one with displaying the rank.

need anything else?
select      Convert(date,WeekEndDate) as WeekEnding,
            GrandTotal,
            r as WeekRank
from    (
            select WeekEndDate, GrandTotal, rank() over(order by GrandTotal desc) r
            from (
                        SELECT  dateadd( d,   7 - datepart(dw,ph.OrderDate), ph.OrderDate)  as WeekEndDate
                        ,SUM(ph.TotalDue) As GrandTotal
                        FROM Purchasing.PurchaseOrderHeader ph
                        Where YEAR(OrderDate) = 2007
                        GROUP BY dateadd( d,   7 - datepart(dw,ph.OrderDate), ph.OrderDate)
                        ) as x
            ) as x
where r  <=5
Order by GrandTotal desc;
why are you converting a date into a date?
Actually yes, Now I need a pivot..

With The ShipMethod table

Date to date I wanted to get rid of time just wanted date see picture

This is the result I am looking for
EE-pivot.png
sorry, when I asked "anything else" I meant with relation to the original question.


since that appears to be a new question.  please close this one and open a new question
Very Excellent and fast