SQL Rank...

There was a situation that Suppliers needs to be ranked by the lowest Rate given for the Items in a particular Year. It can be easily done by using SQL Rank function.
Sample query is as below.

   1: SELECT YearKey,SupplierKey,ItemKey,
   2: RANK() OVER (PARTITION BY ItemKey,YearKey ORDER BY AvgRate ASC) AS RankByAverageRate
   3:   FROM FactPurchases
   4: GROUP BY YearKey,SupplierKey,ItemKey

This will rank the Suppliers using the AvgRate of the Item available for the Year.

No comments:

Post a Comment

Getting Error Description and Error Column Name in SSIS 2016...

If you check the Error Output Path of a SSIS Data Flow component, it only contains the ErrorCode and the ErrorColumn (actually this the I...