Leo Torres
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 ....
select WeekEndDate, GrandTotal, r from ....
ASKER
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?
need anything else?
ASKER
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.PurchaseOrderHe ader 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;
GrandTotal,
r as WeekRank
from (
select WeekEndDate, GrandTotal, rank() over(order by GrandTotal desc) r
from (
SELECT dateadd( d, 7 - datepart(dw,ph.OrderDate),
,SUM(ph.TotalDue) As GrandTotal
FROM Purchasing.PurchaseOrderHe
Where YEAR(OrderDate) = 2007
GROUP BY dateadd( d, 7 - datepart(dw,ph.OrderDate),
) as x
) as x
where r <=5
Order by GrandTotal desc;
why are you converting a date into a date?
ASKER
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
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
since that appears to be a new question. please close this one and open a new question
ASKER
Very Excellent and fast
SELECT top 5 dateadd( d, 7 - datepart(dw,ph.OrderDate),
,SUM(ph.TotalDue) As GrandTotal
FROM Purchasing.PurchaseOrderHe
Where YEAR(OrderDate) = 2004
GROUP BY dateadd( d, 7 - datepart(dw,ph.OrderDate),
Order by SUM(ph.TotalDue) desc;