Pivot SQL 2008, 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 in pic

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;

Select allOpen in new window

EE-pivot.png