Returning Top Customers by Territory For Each of the Last 4 Years and Summing Them SQL Server 2008 -
forgive me long title, succinctly put problem. have table contains sales data sale sales date, territory, customer info etc each sale. want return top 10 customers in each territory each of last 4 years sales value. if customer shows on multiple years top tens, should sum total value of years , order total value. therefore, if top 10 customers same 4 years, have 10 results. if if top 10 customers different 4 years, have 40 results. want query read in today's date, don't have update years searching each new year. having trouble begin, , yes new sql.
the table "bookings". have put field types , sample data below:
[bks_bookdate] (datetime), [bks_territorycodes] (nvarchar(255)), [bks_cus_recordid] (uniqueidentifier), [bks_bookamt] (money). sample data:
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 mihir's have made following changes:
set nocount on; declare @today date ,@lastsales int select @today = getdate() ---- last 4 year including current year, if excluding current year use -4 ,@lastsales = datepart(year, dateadd(year, -3, @today)) ;with sales_cte ( select s.* ,row_number() over(partition s.bks_territorycodes order s.bks_territorycodes,s.saleyear,s.amount desc) topsales ( select b.bks_territorycodes ,datepart(year, b.bks_bookdate) saleyear ,b.bks_cus_recordid ,sum(b.bks_bookamt) amount bookings b (nolock) datepart(year, b.bks_bookdate) >= @lastsales group b.bks_territorycodes ,datepart(year, b.bks_bookdate) ,b.bks_cus_recordid ) s ) select sc.bks_territorycodes ,sc.saleyear ,cus_corpname ,max(sc.amount) total_sales sales_cte sc (nolock) join customer on cus_recordid = bks_cus_recordid sc.topsales <= 10 group sc.bks_territorycodes ,saleyear ,cus_corpname order sc.saleyear desc the issues still have are: 1. bookings amount appears largest sale each of customers, when need sum of of bookings. way when top 10 in each region returned, top ten in sales whole year. 2. also, reason, full data set 2012 , 1 region 2014. know there full data set each of regions.
try this
set nocount on; declare @today date ,@lastsales int select @today = getdate() ---- last 4 year including current year, if excluding current year use -4 ,@lastsales = datepart(year, dateadd(year, -3, @today)) if object_id('tempdb.dbo.#sales') not null begin drop table #sales end create table #sales ( saleid int identity(1,1) primary key ,amount numeric(18,2) ,saledate date ,territory varchar(200) ,customerid int ) insert #sales(amount,saledate,territory,customerid) values (18542,'2015-02-15','andaman , nicobar islands',3) ,(1542,'2015-03-05','andaman , nicobar islands',1) ,(11542,'2014-03-05','chandigarh',1) ,(15542,'2011-11-05','chandigarh',5) ,(6542,'2013-12-15','andaman , nicobar islands',3) ,(4542,'2014-06-05','andaman , nicobar islands',1) ,(8542,'2012-08-05','chandigarh',1) ,(2642,'2015-10-05','chandigarh',5) ;with sales_cte ( select s.* ,row_number() over(partition s.territory order s.territory,s.saleyear,s.amount desc) topsales ( select s.territory ,datepart(year, s.saledate) saleyear ,s.customerid ,max(s.amount) amount #sales s (nolock) datepart(year, s.saledate) >= @lastsales group s.territory ,datepart(year, s.saledate) ,s.customerid ) s ) select sc.territory ,sc.saleyear ,sc.customerid ,sc.amount sales_cte sc (nolock) sc.topsales <= 10 order sc.saleyear desc
Comments
Post a Comment