CODEWITHSUNDEEP
sql-server-2008row-number
ariel
ariel

Reputation: 77

row_number Over Partition

I'm using SQL Server 2008 R2. I have table called EmployeePurcheses with the following structure and sample data:

EmployeeID Date Usd
1     2014-11-12 5
1     2014-11-18 9
1     2014-11-18 7

What I am trying to do is to list of work week of this employee's employment in each Date ordered by the Date field. So, the output will look like this:

rn EmployeeID Date WW
1  1     2014-11-12 46
2  1     2014-11-18 47
2  1     2014-11-18 47

I intended to use partitioning the data using the following query but it failed. Thank you, in advance.

select 
        rn = ROW_NUMBER() over (partition by DATEPART(ww, [Date]) order by    
        DATEPART(ww, [Date]))
       ,[EmployeeID ]
       ,Cast([Date] as date) as [Date]
       ,DATEPART(ww, [Date]) as WW
FROM EmployeePurcheses 
Order by [Date] asc

Upvotes: 0

Views: 286

Answers (1)

Paweł Dyl
Paweł Dyl

Reputation: 9143

According to your result you need DENSE_RANK() with no partitioning:

WITH EmployeePurchases AS
(
    SELECT * FROM (VALUES
    (1, '2014-11-12', 5),
    (1, '2014-11-18', 9),
    (1, '2014-11-18', 7)) T(EmployeeID, Date, Usd)
)
select 
        rn = DENSE_RANK() over (order by DATEPART(WW, [Date]))
       ,[EmployeeID]
       ,Cast([Date] as date) as [Date]
       ,DATEPART(ww, [Date]) as WW
FROM EmployeePurchases 
Order by [Date] asc

Upvotes: 1

Related Questions