Forgive me for the long title, but that is succinctly as I could put this problem. I have a table that contains sales data by sale as well as the sales date, territory, customer info etc for each sale. I want to return the top 10 customers in each territory for each of the last 4 years by sales value. If the customer shows up on multiple years top tens, it should sum the Total value of those years together and order by the total value. Therefore, if all top 10 customers were the same for all 4 years, I would only have 10 results. If if all top 10 customers were different for all 4 years, I would have 40 results. I want the query to read in today's date, so I don't have to update the years I am searching for each New Year. I am having trouble with even where to begin, and yes I am new to SQL.
The table is "Bookings". I have put the field types and some sample data below:
[BKS_BookDate] (datetime), [BKS_TerritoryCodes] (nvarchar(255)), [BKS_CUS_RecordID] (uniqueidentifier), [BKS_BookAmt] (money).
BKS_BookDate BKS_TerritoryCodes BKS_CUS_RecordID BKS_BookAmt
'2006-09-07 17:00:00.000' 'MF - USA' 'EF928A2E-1A71-4231-BFA9-0B1D2E903469' '1190.00' '2006-09-15 12:45:00.000' 'TT - South' '68BAFD37-59F5-4985-8F3E-E440E3A0C3EE' '1716.00' '2006-09-15 12:45:00.000' 'TT - South' '68BAFD37-59F5-4985-8F3E-E440E3A0C3EE' '864.00' '2006-09-15 12:45:00.000' 'TT - South' '68BAFD37-59F5-4985-8F3E-E440E3A0C3EE' '822.00' '2006-09-15 12:45:00.000' 'TT - South' '68BAFD37-59F5-4985-8F3E-E440E3A0C3EE' '760.00' '2006-09-15 12:45:00.000' 'TT - South' '68BAFD37-59F5-4985-8F3E-E440E3A0C3EE' '0.00'
Update #3 With Mihir's help I have made the following changes:
Set Nocount On; Declare @Today Date ,@LastSales Int Select @Today = Getdate() ---- Last 4 year including current year, if excluding current year then use -4 ,@LastSales = Datepart(Year, Dateadd(Year, -3, @Today)) ;With Sales_CTE As ( Select s.* ,Row_Number() Over(Partition By s.BKS_TerritoryCodes Order By s.BKS_TerritoryCodes,s.SaleYear,s.Amount Desc) As TopSales From ( Select b.BKS_TerritoryCodes ,Datepart(Year, b.BKS_BookDate) As SaleYear ,b.BKS_CUS_RecordID ,SUM(b.BKS_BookAmt) As Amount From Bookings As b With (Nolock) Where Datepart(Year, b.BKS_BookDate) >= @LastSales Group By b.BKS_TerritoryCodes ,Datepart(Year, b.BKS_BookDate) ,b.BKS_CUS_RecordID ) As s ) Select sc.BKS_TerritoryCodes ,sc.SaleYear ,CUS_CorpName ,MAX(sc.Amount) as Total_Sales From Sales_CTE As sc With (Nolock) join Customer on CUS_RecordID = BKS_CUS_RecordID Where sc.TopSales <= 10 Group By sc.BKS_TerritoryCodes ,SaleYear ,CUS_CorpName Order By sc.SaleYear Desc
The issues I still have are: 1. The bookings amount appears to be only the largest sale to each of the customers, when it need to be the sum of all of the bookings. That way when the top 10 in each region are returned, it is the top ten in sales for the whole year. 2. Also, for some reason, I only get a full data set for 2012 and then 1 region for 2014. I know there is a full data set for each of the regions.