Recently, I created a report using SSRS and i found ROW_NUMBER() can be very useful in rank-related tasks.
Here is a sample query using AdventureWorks database.
Select ProductID, ReorderPoint , SafetyStockLevel,
ROW_NUMBER() OVER (order by ProductID) as Row
FROM Production.Product
where ProductID < 320
The results would be:
ProductID ReorderPoint SafetyStockLevel Row
----------- ------------ ---------------- --------------------
1 750 1000 1
2 750 1000 2
3 600 800 3
4 600 800 4
316 600 800 5
317 375 500 6
318 375 500 7
319 375 500 8
If i want to see above results in ReorderPoint per SafetyStockLevel or multiple versions. We can accomplish this with the PARTITION BY clause in the ROW_NUMBER function like so:
Select ProductID, ReorderPoint , SafetyStockLevel,
ROW_NUMBER() OVER (PARTITION BY SafetyStockLevel order by ProductID) as Row
FROM Production.Product
where ProductID < 320
This will return the following results:
ProductID ReorderPoint SafetyStockLevel Row
----------- ------------ ---------------- --------------------
317 375 500 1
318 375 500 2
319 375 500 3
3 600 800 1
4 600 800 2
316 600 800 3
1 750 1000 1
2 750 1000 2
Subscribe
